[Home] [Help]
PACKAGE BODY: APPS.AS_GAR_LEADS_PUB
Source
4 ---------------------------------------------------------------------------
1 PACKAGE BODY AS_GAR_LEADS_PUB AS
2 /* $Header: asxgrldb.pls 120.8.12010000.2 2010/03/01 10:38:13 sariff ship $ */
3
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
35 *------------------------------------------------------------------------*/
32 *-------------------------------------------------------------------------*/
33 /*------------------------------------------------------------------------*
34 | PUBLIC ROUTINES
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 l_assign_manual_flag VARCHAR2(1);
58 l_resource_id NUMBER;
59
60 BEGIN
61 AS_GAR.g_debug_flag := p_debug_mode;
62 IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
63 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
64
65 IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
66 l_target_type := 'TOTAL';
67 ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
68 l_target_type := 'INCREMENTAL';
69 END If;
70
71 -- Set the Global variables
72 AS_GAR.INIT(
73 p_run_mode,
74 p_worker_id,
75 l_terr_globals);
76
77 /* This inserts into Lead winners */
78 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
79 JTY_ASSIGN_BULK_PUB.GET_WINNERS
80 ( p_api_version_number => 1.0,
81 p_init_msg_list => FND_API.G_TRUE,
82 p_source_id => -1001,
83 p_trans_id => -1003,
84 P_PROGRAM_NAME => 'SALES/LEAD PROGRAM',
85 P_mode => l_target_type,
86 P_percent_analyzed => NVL(P_percent_analyzed,20),
87 p_worker_id => p_worker_id,
88 x_return_status => l_return_status,
89 x_msg_count => l_msg_count,
90 x_msg_data => l_msg_data,
91 ERRBUF => l_errbuf,
92 RETCODE => l_retcode);
93
94
95 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
96 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
97
98 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
99 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 End If;
102
103 COMMIT;
104
105 -- Explode GROUPS if any inside winners
106 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
107 AS_GAR_LEADS_PUB.EXPLODE_GROUPS_LEADS(
108 x_errbuf => l_errbuf,
109 x_retcode => l_retcode,
110 p_terr_globals => l_terr_globals,
111 x_return_status => l_return_status);
112
113 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
114 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
115
116 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
117 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 End If;
120
121 COMMIT;
122
123 -- Explode TEAMS if any inside winners
124 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
125 AS_GAR_LEADS_PUB.EXPLODE_TEAMS_LEADS(
126 x_errbuf => l_errbuf,
127 x_retcode => l_retcode,
128 p_terr_globals => l_terr_globals,
129 x_return_status => l_return_status);
130
131 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
132 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
133
134 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
135 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
137 End If;
138
139 COMMIT;
140
141 -- Set team leader for Leads
142 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
143 AS_GAR_LEADS_PUB.SET_TEAM_LEAD_LEADS(
144 x_errbuf => l_errbuf,
145 x_retcode => l_retcode,
146 p_terr_globals => l_terr_globals,
147 x_return_status => l_return_status);
148
149 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
150 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
151
152 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
153 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 End If;
156
157 -- Insert into Lead Accesses from Winners
158 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
159 AS_GAR_LEADS_PUB.INSERT_ACCESSES_LEADS(
163 x_return_status => l_return_status);
160 x_errbuf => l_errbuf,
161 x_retcode => l_retcode,
162 p_terr_globals => l_terr_globals,
164
165 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
166 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
167
168 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
169 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171 End If;
172
173 -- Insert into territory Accesses
174 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
175 AS_GAR_LEADS_PUB.INSERT_TERR_ACCESSES_LEADS(
176 x_errbuf => l_errbuf,
177 x_retcode => l_retcode,
178 p_terr_globals => l_terr_globals,
179 x_return_status => l_return_status);
180
181 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
182 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
183
184 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
185 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 End If;
188
189 -- Remove (soft delete) records in access table that are not qualified
190 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
191 AS_GAR_LEADS_PUB.PERFORM_LEAD_CLEANUP(
192 x_errbuf => l_errbuf,
193 x_retcode => l_retcode,
194 p_terr_globals => l_terr_globals,
195 x_return_status => l_return_status);
196
197 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
198 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
199
200 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
201 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
203 End If;
204
205 -- LEAD Owner assignment
206 -- Lead Owner logic is re-written to sync with real time TAP for bug 8615468
207 -- Logic is as:
208 -- If profile 'OS: Assign New Lead' set to 'No' then
209 -- owner will be any Resource assigned via TAP. If no resource assigned by TAP then
210 -- owner is based on profile OS: Default Resource ID used for Sales Lead Assignment.
211 -- If profile 'OS: Assign New Lead' set to 'Yes' then
212 -- owner is based on profile OS: Default Resource ID used for Sales Lead Assignment.
213
214 l_assign_manual_flag := nvl(FND_PROFILE.Value('AS_LEAD_ASSIGN_MANUAL'),'N');
215 l_resource_id := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
216
217 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_START);
218 IF l_assign_manual_flag = 'N' THEN
219 AS_GAR_LEADS_PUB.ASSIGN_LEAD_OWNER(
220 x_errbuf => l_errbuf,
221 x_retcode => l_retcode,
222 p_terr_globals => l_terr_globals,
223 x_return_status => l_return_status);
224 IF l_resource_id IS NOT NULL THEN
225 AS_GAR_LEADS_PUB.ASSIGN_DEFAULT_LEAD_OWNER(
226 x_errbuf => l_errbuf,
227 x_retcode => l_retcode,
228 p_terr_globals => l_terr_globals,
229 x_return_status => l_return_status);
230 END IF;
231 ELSE
232 IF l_resource_id IS NOT NULL THEN
233 AS_GAR_LEADS_PUB.UNCHECK_LEAD_OWNER(
234 x_errbuf => l_errbuf,
235 x_retcode => l_retcode,
236 p_terr_globals => l_terr_globals,
237 x_return_status => l_return_status);
238 AS_GAR_LEADS_PUB.ASSIGN_DEFAULT_LEAD_OWNER(
239 x_errbuf => l_errbuf,
240 x_retcode => l_retcode,
241 p_terr_globals => l_terr_globals,
242 x_return_status => l_return_status);
243 ELSE
244 AS_GAR_LEADS_PUB.UNCHECK_ASSIGN_SALESFORCE(
245 x_errbuf => l_errbuf,
246 x_retcode => l_retcode,
247 p_terr_globals => l_terr_globals,
248 x_return_status => l_return_status);
249 END IF;
250 END IF;
251 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
252 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
253
254 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
255 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO, l_errbuf, l_retcode);
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 End If;
258
259 -- BES enhancement
260
261 l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
262 IF l_sub_exist = 'Y' THEN
263 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
264 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
265 AS_GAR.Raise_BE(l_terr_globals);
266 END If;
267
268 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
269 EXCEPTION
270 WHEN OTHERS THEN
271 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
272 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
273 END GAR_WRAPPER;
274
275 /************************** End GAR Wrapper *****************************/
276
277 /************************** Start Explode Teams Leads ******************/
278 PROCEDURE EXPLODE_TEAMS_LEADS(
279 x_errbuf OUT NOCOPY VARCHAR2,
283 IS
280 x_retcode OUT NOCOPY VARCHAR2,
281 p_terr_globals IN AS_GAR.TERR_GLOBALS,
282 x_return_status OUT NOCOPY VARCHAR2)
284
285 /*-------------------------------------------------------------------------+
286 | LOGIC
287 |
288 | A RESOURCE team can be comprised OF resources who belong TO one OR more
289 | GROUPS OF resources.
290 | So get a LIST OF team members (OF TYPE employee OR partner OR parter contact
291 | AND play a ROLE OF salesrep ) AND get atleast one GROUP id that they belong TO
292 | WHERE they play a similar ROLE.
293 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
294 | are a RESOURCE within a team.
295 | INSERT these members INTO winners IF they are NOT already IN winners.
296 +-------------------------------------------------------------------------*/
297
298 l_errbuf VARCHAR2(4000);
299 l_retcode VARCHAR2(255);
300 l_res_type_count NUMBER;
301 l_res_lead_count NUMBER;
302 l_resource_type VARCHAR2(10);
303 l_request_id NUMBER;
304 l_worker_id NUMBER;
305
306 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
307 IS
308 SELECT count(*)
309 FROM JTF_TAE_1001_LEAD_WINNERS
310 WHERE request_id = c_request_id
311 AND resource_type = c_resource_type
312 AND worker_id = c_worker_id
313 AND ROWNUM < 2;
314
315
316 CURSOR count_res_lead
317 IS
318 SELECT count(*)
319 FROM JTF_TERR_RSC_ALL rsc,
320 JTF_TERR_DENORM_RULES_ALL rules,
321 JTF_TERR_RSC_ACCESS_ALL acc
322 WHERE rules.terr_id = rsc.terr_id
323 AND rsc.resource_type = 'RS_TEAM'
324 AND acc.access_type = 'LEAD'
325 AND rules.source_id = -1001
326 AND rsc.terr_rsc_id = acc.terr_rsc_id;
327
328 BEGIN
329 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
330 x_return_status := FND_API.G_RET_STS_SUCCESS;
331 l_request_id := p_terr_globals.request_id;
332 l_worker_id := p_terr_globals.worker_id;
333 l_resource_type := 'RS_TEAM';
334
335
336 OPEN count_res_lead;
337 FETCH count_res_lead INTO l_res_lead_count;
338 CLOSE count_res_lead;
339
340 IF l_res_lead_count > 0 THEN
341 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
342 FETCH c_get_res_type_count INTO l_res_type_count;
343 CLOSE c_get_res_type_count;
344 END IF;
345
346 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
347 IF l_res_type_count > 0 THEN
348 /* Get resources within a resource team */
349 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
350 /** Note
351 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
352 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
353 **/
354 INSERT INTO JTF_TAE_1001_LEAD_WINNERS
355 (trans_object_id,
356 trans_detail_object_id,
357 terr_id,
358 resource_id,
359 resource_type,
360 group_id,
361 full_access_flag,
362 request_id,
363 program_application_id,
364 program_id,
365 program_update_date,
366 source_id,
367 trans_object_type_id,
368 last_update_date,
369 last_updated_by,
370 creation_date,
371 created_by,
372 last_update_login,
373 absolute_rank,
374 top_level_terr_id,
375 num_winners,
376 terr_rsc_id,
377 role,
378 primary_contact_flag,
379 person_id,
380 org_id,
381 worker_id)
382 SELECT T.trans_object_id,
383 T.trans_detail_object_id,
384 T.terr_id,
385 J.resource_id,
386 DECODE(J.resource_category,'PARTY','RS_PARTY',
387 'PARTNER','RS_PARTNER',
388 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
389 J.group_id,
390 T.full_access_flag,
391 T.request_id,
392 T.program_application_id,
393 T.program_id, T.program_update_date,
394 T.source_id,
395 T.trans_object_type_id,
396 SYSDATE,
397 T.last_updated_by,
398 SYSDATE,
399 T.created_by,
400 T.last_update_login,
401 T.absolute_rank,
402 T.top_level_terr_id,
403 T.num_winners,
404 T.terr_rsc_id,
405 T.role,
406 T.primary_contact_flag,
407 J.person_id,
408 T.org_id,
409 T.worker_id
410 FROM
411 JTF_TAE_1001_LEAD_WINNERS T,
412 (
413 SELECT TM.team_resource_id resource_id,
414 TM.person_id person_id2,
415 MIN(G.group_id)group_id,
416 MIN(T.team_id) team_id,
417 TRES.category resource_category,
418 MIN(TRES.source_id) person_id
419 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
420 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
421 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
422 (
426 jtf_rs_group_usages u,
423 SELECT m.group_id group_id, m.resource_id resource_id
424 FROM jtf_rs_group_members m,
425 jtf_rs_groups_b g,
427 jtf_rs_role_relations rr,
428 jtf_rs_roles_b r,
429 jtf_rs_resource_extns res
430 WHERE m.group_id = g.group_id
431 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
432 AND NVL(g.end_date_active,SYSDATE)
433 AND u.group_id = g.group_id
434 AND u.usage IN ('SALES','PRM')
435 AND m.group_member_id = rr.role_resource_id
436 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
437 AND rr.delete_flag <> 'Y'
438 AND SYSDATE BETWEEN rr.start_date_active
439 AND NVL(rr.end_date_active,SYSDATE)
440 AND rr.role_id = r.role_id
441 AND r.role_type_code
442 IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
443 AND r.active_flag = 'Y'
444 AND res.resource_id = m.resource_id
445 AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
446 ) G
447 WHERE tm.team_id = t.team_id
448 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
449 AND NVL(t.end_date_active,SYSDATE)
450 AND tu.team_id = t.team_id
451 AND tu.usage IN ('SALES','PRM')
452 AND tm.team_member_id = trr.role_resource_id
453 AND tm.delete_flag <> 'Y'
454 AND tm.resource_type = 'INDIVIDUAL'
455 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
456 AND trr.delete_flag <> 'Y'
457 AND SYSDATE BETWEEN trr.start_date_active
458 AND NVL(trr.end_date_active,SYSDATE)
459 AND trr.role_id = tr.role_id
460 AND tr.role_type_code IN
461 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
462 AND tr.active_flag = 'Y'
463 AND tres.resource_id = tm.team_resource_id
464 AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
465 AND tm.team_resource_id = g.resource_id
466 GROUP BY tm.team_resource_id,
467 tm.person_id,
468 tres.category,
469 tres.source_id
470 UNION ALL
471 SELECT MIN(m.resource_id) resource_id,
472 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
473 MIN(jtm.team_id) team_id, res.CATEGORY resource_category,
474 MIN(res.source_id) person_id
475 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
476 jtf_rs_group_usages u, jtf_rs_role_relations rr,
477 jtf_rs_roles_b r, jtf_rs_resource_extns res,
478 (
479 SELECT tm.team_resource_id group_id,
480 t.team_id team_id
481 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
482 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
483 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
484 WHERE tm.team_id = t.team_id
485 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
486 AND NVL(t.end_date_active,SYSDATE)
487 AND tu.team_id = t.team_id
488 AND tu.usage IN ('SALES','PRM')
489 AND tm.team_member_id = trr.role_resource_id
490 AND tm.delete_flag <> 'Y'
491 AND tm.resource_type = 'GROUP'
492 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
493 AND trr.delete_flag <> 'Y'
494 AND SYSDATE BETWEEN trr.start_date_active
495 AND NVL(trr.end_date_active,SYSDATE)
496 AND trr.role_id = tr.role_id
497 AND tr.role_type_code IN
498 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
499 AND tr.active_flag = 'Y'
500 AND tres.resource_id = tm.team_resource_id
501 AND tres.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
502 ) jtm
503 WHERE m.group_id = g.group_id
504 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
505 AND NVL(g.end_date_active,SYSDATE)
506 AND u.group_id = g.group_id
507 AND u.usage IN ('SALES','PRM')
508 AND m.group_member_id = rr.role_resource_id
509 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
510 AND rr.delete_flag <> 'Y'
511 AND SYSDATE BETWEEN rr.start_date_active
512 AND NVL(rr.end_date_active,SYSDATE)
513 AND rr.role_id = r.role_id
514 AND r.role_type_code IN
515 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
516 AND r.active_flag = 'Y'
517 AND res.resource_id = m.resource_id
518 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
519 AND jtm.group_id = g.group_id
520 GROUP BY m.resource_id, m.person_id, jtm.team_id, res.CATEGORY) J
521 WHERE j.team_id = t.resource_id
522 AND t.request_id = l_request_id
523 AND t.worker_id = l_worker_id
527 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
524 AND t.resource_type = 'RS_TEAM'
525 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_LEAD_WINNERS rt1
526 WHERE rt1.resource_id = j.resource_id
528 AND rt1.request_id = t.request_id
529 AND rt1.worker_id = t.worker_id
530 AND rt1.trans_object_id = t.trans_object_id
531 AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
532
533 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);
534 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
535
536
537 COMMIT;
538
539 END IF; /* if l_res_type_count > 0 */
540 EXCEPTION
541 WHEN others THEN
542 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
543 x_errbuf := SQLERRM;
544 x_retcode := SQLCODE;
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 RAISE;
547 END EXPLODE_TEAMS_LEADS;
548 /************************** End Explode Teams Leads ******************/
549
550 /************************** Start Explode Groups Leads ******************/
551 PROCEDURE EXPLODE_GROUPS_LEADS(
552 x_errbuf OUT NOCOPY VARCHAR2,
553 x_retcode OUT NOCOPY VARCHAR2,
554 p_terr_globals IN AS_GAR.TERR_GLOBALS,
555 x_return_status OUT NOCOPY VARCHAR2)
556 IS
557 -------------RS_GROUP---------
558 /*-------------------------------------------------------------------------+
559 | PROGRAM LOGIC
560 |
561 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
562 | a sales ROLE AND are either an employee OR partner AND INSERT back INTO
563 | winners IF they are NOT already IN winners.
564 +-------------------------------------------------------------------------*/
565 l_errbuf VARCHAR2(4000);
566 l_retcode VARCHAR2(255);
567 l_res_type_count NUMBER;
568 l_res_lead_count NUMBER;
569 l_resource_type VARCHAR2(10);
570 l_request_id NUMBER;
571 l_worker_id NUMBER;
572
573 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
574 IS
575 SELECT count(*)
576 FROM JTF_TAE_1001_LEAD_WINNERS
577 WHERE request_id = c_request_id
578 AND resource_type = c_resource_type
579 AND worker_id = c_worker_id
580 AND ROWNUM < 2;
581
582 CURSOR count_res_lead
583 IS
584 SELECT count(*)
585 FROM JTF_TERR_RSC_ALL rsc,
586 JTF_TERR_DENORM_RULES_ALL rules,
587 JTF_TERR_RSC_ACCESS_ALL acc
588 WHERE rules.terr_id = rsc.terr_id
589 AND rsc.resource_type = 'RS_GROUP'
590 AND acc.access_type = 'LEAD'
591 AND rules.source_id = -1001
592 AND rsc.terr_rsc_id = acc.terr_rsc_id ;
593
594 BEGIN
595 l_resource_type := 'RS_GROUP';
596 x_return_status := FND_API.G_RET_STS_SUCCESS;
597 l_request_id := p_terr_globals.request_id;
598 l_worker_id := p_terr_globals.worker_id;
599
600 OPEN count_res_lead;
601 FETCH count_res_lead INTO l_res_lead_count;
602 CLOSE count_res_lead;
603
604 IF l_res_lead_count > 0 THEN
605 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
606 FETCH c_get_res_type_count INTO l_res_type_count;
607 CLOSE c_get_res_type_count;
608 END IF;
609
610 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
611 IF l_res_type_count > 0 THEN
612 /* Get resources within a resource group */
613 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
614 /** Note
615 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
616 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
617 **/
618
619 INSERT INTO JTF_TAE_1001_LEAD_WINNERS
620 (trans_object_id,
621 trans_detail_object_id,
622 terr_id,
623 resource_id,
624 resource_type,
625 group_id,
626 full_access_flag,
627 request_id,
628 program_application_id,
629 program_id,
630 program_update_date,
631 source_id,
632 trans_object_type_id,
633 last_update_date,
634 last_updated_by,
635 creation_date,
636 created_by,
637 last_update_login,
638 absolute_rank,
639 top_level_terr_id,
640 num_winners,
641 terr_rsc_id,
642 role,
643 primary_contact_flag,
644 person_id,
645 org_id,
646 worker_id)
647 SELECT T.trans_object_id,
648 T.trans_detail_object_id,
649 T.terr_id,
650 J.resource_id,
651 DECODE(J.resource_category,'PARTY','RS_PARTY',
652 'PARTNER','RS_PARTNER',
653 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
654 J.group_id,
655 T.full_access_flag,
656 T.request_id,
657 T.program_application_id,
658 T.program_id,
659 T.program_update_date,
660 T.source_id,
661 T.trans_object_type_id,
662 SYSDATE,
663 T.last_updated_by,
667 T.absolute_rank,
664 SYSDATE,
665 T.created_by,
666 T.last_update_login,
668 T.top_level_terr_id,
669 T.num_winners,
670 T.terr_rsc_id,
671 T.role,
672 T.primary_contact_flag,
673 J.person_id,
674 T.org_id,
675 T.worker_id
676 FROM
677 JTF_TAE_1001_LEAD_WINNERS t,
678 (
679 SELECT MIN(m.resource_id) resource_id,
680 res.category resource_category,
681 m.group_id group_id, MIN(res.source_id) person_id
682 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
683 jtf_rs_group_usages u, jtf_rs_role_relations rr,
684 jtf_rs_roles_b r, jtf_rs_resource_extns res
685 WHERE m.group_id = g.group_id
686 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
687 AND NVL(g.end_date_active,SYSDATE)
688 AND u.group_id = g.group_id
689 AND u.usage IN ('SALES','PRM')
690 AND m.group_member_id = rr.role_resource_id
691 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
692 AND rr.role_id = r.role_id
693 AND rr.delete_flag <> 'Y'
694 AND SYSDATE BETWEEN rr.start_date_active
695 AND NVL(rr.end_date_active,SYSDATE)
696 AND r.role_type_code IN
697 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
698 AND r.active_flag = 'Y'
699 AND res.resource_id = m.resource_id
700 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
701 GROUP BY m.group_member_id, m.resource_id, m.person_id,
702 m.group_id, res.CATEGORY) j
703 WHERE j.group_id = t.resource_id
704 AND t.request_id = l_request_id
705 AND t.worker_id = l_worker_id
706 AND t.resource_type = 'RS_GROUP'
707 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_LEAD_WINNERS rt1
708 WHERE rt1.resource_id = j.resource_id
709 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
710 AND rt1.request_id = t.request_id
711 AND rt1.worker_id = t.worker_id
712 AND rt1.trans_object_id = t.trans_object_id
713 AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
714
715 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);
716 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
717
718 COMMIT;
719 END IF; /* if l_res_type_count > 0 */
720
721 EXCEPTION
722 WHEN OTHERS THEN
723 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
724 x_errbuf := SQLERRM;
725 x_retcode := SQLCODE;
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 RAISE;
728 END EXPLODE_GROUPS_LEADS;
729 /************************** End Explode Groups Leads ******************/
730
731 /************************** Start Set Leads Team Leader *****************/
732
733 /*-------------------------------------------------------------------------+
734 | PROGRAM LOGIC
735 |
736 | Winners table records are striped by worker id.
737 | All the logic pertains to what happens within a single worker.
738 | Get a list of resources who are marked as full access in winners but are
739 | not marked as full access in accesses (CURSOR team_leader).
740 | Loop within the worker for sets of records within winners ---?
741 | Bulk collect from team_leader cursor into array.
742 | Break up the array into batches based on global var bulk_size.
743 | For each batch:
744 | Try 3 times to bulk update acesses
745 | if all 3 attempts fail because of deadlock:
746 | Update on record at a time.
747 +-------------------------------------------------------------------------*/
748
749 PROCEDURE SET_TEAM_LEAD_LEADS(
750 x_errbuf OUT NOCOPY VARCHAR2,
751 x_retcode OUT NOCOPY VARCHAR2,
752 p_terr_globals IN AS_GAR.TERR_GLOBALS,
753 x_return_status OUT NOCOPY VARCHAR2)
754 IS
755
756 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
757 TYPE faf_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
758
759 l_sales_lead_id num_list;
760 l_salesforce_id num_list;
761 l_sales_group_id num_list;
762 l_faf faf_list;
763 l_var NUMBER;
764 l_worker_id NUMBER;
765 l_limit_flag BOOLEAN := FALSE;
766 l_max_fetches NUMBER := 10000;
767 l_loop_count NUMBER := 0;
768 l_flag BOOLEAN;
769 l_first NUMBER;
770 l_last NUMBER;
771 l_attempts NUMBER := 0;
772
773 CURSOR team_leader(c_worker_id number) IS
774 SELECT /*+ LEADING(WIN) USE_NL(A WIN) INDEX(A) */ A.sales_lead_id,
775 A.salesforce_id,
776 A.sales_group_id,
777 NVL(WIN.full_access_flag,'N')
778 FROM AS_ACCESSES_ALL_ALL A,
779 JTF_TAE_1001_LEAD_WINNERS WIN
780 WHERE A.lead_id is NULL
781 AND A.sales_lead_id is NOT NULL
782 AND A.delete_flag is NULL
783 AND NVL(A.team_leader_flag,'N') <> NVL(WIN.full_access_flag,'N')
784 AND WIN.source_id = -1001
785 AND WIN.worker_id = c_worker_id
789 AND WIN.group_id = A.sales_group_id
786 AND WIN.resource_type = 'RS_EMPLOYEE'
787 AND WIN.trans_object_id = A.sales_lead_id
788 AND WIN.resource_id = A.salesforce_id
790 GROUP BY A.sales_lead_id,
791 A.salesforce_id,
792 A.sales_group_id,
793 WIN.full_access_flag;
794
795
796 BEGIN
797 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
798 x_return_status := FND_API.G_RET_STS_SUCCESS;
799 l_worker_id:=p_terr_globals.worker_id;
800 l_var :=p_terr_globals.bulk_size;
801 l_max_fetches := p_terr_globals.cursor_limit;
802 LOOP -- For l_limit_flag
803 IF (l_limit_flag) THEN EXIT; END IF;
804
805 l_sales_lead_id.DELETE;
806 l_salesforce_id.DELETE;
807 l_sales_group_id.DELETE;
808 l_faf.DELETE;
809 l_loop_count := l_loop_count + 1;
810 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'LOOPCOUNT :- ' || l_loop_count);
811
812 --------------------------------
813 OPEN team_leader(l_worker_id);
814 FETCH team_leader BULK COLLECT INTO
815 l_sales_lead_id, l_salesforce_id, l_sales_group_id, l_faf
816 LIMIT l_max_fetches;
817 CLOSE team_leader;
818
819 -- Initialize variables
820 l_flag := TRUE;
821 l_first := 0;
822 l_last := 0;
823 l_attempts := 1;
824
825 IF l_sales_lead_id.COUNT < l_max_fetches THEN
826 l_limit_flag := TRUE;
827 END IF;
828 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
829
830 IF l_sales_lead_id.COUNT > 0 THEN
831 l_flag := TRUE;
832 l_first := l_sales_lead_id.FIRST;
833 l_last := l_first + l_var;
834 WHILE l_flag LOOP
835 IF l_last > l_sales_lead_id.LAST THEN
836 l_last := l_sales_lead_id.LAST;
837 END IF;
838 WHILE l_attempts < 3 LOOP
839 BEGIN
840 FORALL i IN l_first .. l_last
841 UPDATE AS_ACCESSES_ALL_ALL ACC
842 SET object_version_number = NVL(object_version_number,0) + 1,
843 ACC.last_update_date = SYSDATE,
844 ACC.last_updated_by = p_terr_globals.user_id,
845 ACC.last_update_login = p_terr_globals.last_update_login,
846 ACC.request_id = p_terr_globals.request_id,
847 ACC.program_application_id = p_terr_globals.prog_appl_id,
848 ACC.program_id = p_terr_globals.prog_id,
849 ACC.program_update_date = SYSDATE,
850 ACC.team_leader_flag = l_faf(i)
851 WHERE ACC.lead_id is NULL
852 AND ACC.sales_lead_id is NOT NULL
853 AND ACC.sales_lead_id = l_sales_lead_id(i)
854 AND ACC.salesforce_id = l_salesforce_id(i)
855 AND ACC.sales_group_id = l_sales_group_id(i);
856 COMMIT;
857 l_attempts := 3;
858 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);
859 EXCEPTION
860 WHEN DEADLOCK_DETECTED THEN
861 BEGIN
862 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
863 ROLLBACK;
864 l_attempts := l_attempts +1;
865 IF l_attempts = 3 THEN
866 FOR i IN l_first .. l_last
867 LOOP
868 BEGIN
869 UPDATE AS_ACCESSES_ALL_ALL ACC
870 SET object_version_number = NVL(object_version_number,0) + 1,
871 ACC.last_update_date = SYSDATE,
872 ACC.last_updated_by = p_terr_globals.user_id,
873 ACC.last_update_login = p_terr_globals.last_update_login,
874 ACC.request_id = p_terr_globals.request_id,
875 ACC.program_application_id = p_terr_globals.prog_appl_id,
876 ACC.program_id = p_terr_globals.prog_id,
877 ACC.program_update_date = SYSDATE,
878 ACC.team_leader_flag = l_faf(i)
879 WHERE ACC.lead_id is NULL
880 AND ACC.sales_lead_id is NOT NULL
881 AND ACC.sales_lead_id = l_sales_lead_id(i)
882 AND ACC.salesforce_id = l_salesforce_id(i)
883 AND ACC.sales_group_id = l_sales_group_id(i);
884 EXCEPTION
885 WHEN OTHERS THEN
886 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
887 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) );
888 END;
889 END LOOP; -- for each record individually
890 COMMIT;
891 END IF;
892 END; -- end of deadlock exception
893 WHEN OTHERS THEN
894 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
895 x_errbuf := SQLERRM;
896 x_retcode := SQLCODE;
897 x_return_status := FND_API.G_RET_STS_ERROR;
898 RAISE;
899 END;
900 END LOOP; -- loop for 3 attempts
901 /* For the next batch of records by bulk_size */
902 l_first := l_last + 1;
903 l_last := l_first + l_var;
904 IF l_first > l_sales_lead_id.LAST THEN
905 l_flag := FALSE;
906 END IF;
907 END LOOP; -- loop for more records within the bulk_size
908 END IF; --l_sales_lead_id.count > 0
909 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
910 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
911 END LOOP; -- loop for more bulk_size fetches
912 l_sales_lead_id.DELETE;
913 l_faf.DELETE;
914 l_salesforce_id.DELETE;
918 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
915 l_sales_group_id.DELETE;
916 EXCEPTION
917 WHEN OTHERS THEN
919 x_errbuf := SQLERRM;
920 x_retcode := SQLCODE;
921 x_return_status := FND_API.G_RET_STS_ERROR;
922 RAISE;
923 END SET_TEAM_LEAD_LEADS;
924
925 /************************** End Set Leads Team Leader *****************/
926
927 /************************** Start Insert Into Entity Accesses*************/
928
929 PROCEDURE INSERT_ACCESSES_LEADS(
930 x_errbuf OUT NOCOPY VARCHAR2,
931 x_retcode OUT NOCOPY VARCHAR2,
932 p_terr_globals IN AS_GAR.TERR_GLOBALS,
933 x_return_status OUT NOCOPY VARCHAR2)
934 IS
935 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
936 TYPE faf_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
937
938
939
940 l_sales_lead_id num_list;
941 l_org_id num_list;
942 l_salesforce_id num_list;
943 l_sales_group_id num_list;
944 l_person_id num_list;
945 l_faf faf_list;
946
947
948 l_var NUMBER;
949 l_worker_id NUMBER;
950 l_limit_flag BOOLEAN := FALSE;
951 l_max_fetches NUMBER := 10000;
952 l_loop_count NUMBER := 0;
953 l_flag BOOLEAN;
954 l_first NUMBER;
955 l_last NUMBER;
956 l_attempts NUMBER := 0;
957 l_src_exists VARCHAR2(1);
958
959 CURSOR ins_acc(c_worker_id number) IS
960 SELECT W.resource_id,
961 W.group_id,
962 MIN(W.person_id) person_id,
963 W.trans_object_id sales_lead_id,
964 MAX(W.full_access_flag) FAF,
965 W.org_id
966 FROM JTF_TAE_1001_LEAD_WINNERS W
967 WHERE W.source_id = -1001
968 AND W.worker_id = c_worker_id
969 AND W.resource_type = 'RS_EMPLOYEE'
970 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.
971 GROUP BY W.trans_object_id,
972 W.resource_id,
973 W.group_id,
974 W.org_id;
975
976 BEGIN
977 /*-------------------------------------------------------------------------+
978 | PROGRAM LOGIC
979 |
980 | Re-Initialize variables and null out if necessary.
981 | Try bulk inserting into accesses. If this fails, insert records one by one.
982 |
983 +-------------------------------------------------------------------------*/
984 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
985 x_return_status := FND_API.G_RET_STS_SUCCESS;
986 l_worker_id:=p_terr_globals.worker_id;
987 l_var :=p_terr_globals.bulk_size;
988 OPEN ins_acc(l_worker_id);
989 LOOP
990 IF (l_limit_flag) THEN EXIT; END IF;
991
992 l_sales_lead_id.DELETE;
993 l_org_id.DELETE;
994 l_salesforce_id.DELETE;
995 l_sales_group_id.DELETE;
996 l_person_id.DELETE;
997 l_faf.DELETE;
998
999 EXIT WHEN ins_acc%NOTFOUND;
1000
1001 l_loop_count := l_loop_count + 1;
1002 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'LOOPCOUNT :- ' || l_loop_count);
1003
1004 FETCH ins_acc BULK COLLECT INTO
1005 l_salesforce_id, l_sales_group_id, l_person_id,
1006 l_sales_lead_id,l_faf,l_org_id
1007 LIMIT l_max_fetches;
1008
1009 -- Initialize variables
1010 l_flag := TRUE;
1011 l_first := 0;
1012 l_last := 0;
1013
1014 IF l_sales_lead_id.COUNT < l_max_fetches THEN
1015 l_limit_flag := TRUE;
1016 END IF;
1017
1018 IF l_sales_lead_id.COUNT > 0 THEN
1019 l_flag := TRUE;
1020 l_first := l_sales_lead_id.FIRST;
1021 l_last := l_first + l_var;
1022 WHILE l_flag LOOP
1023 IF l_last > l_sales_lead_id.LAST THEN
1024 l_last := l_sales_lead_id.LAST;
1025 END IF;
1026 BEGIN
1027 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);
1028 FORALL i IN l_first .. l_last
1029 INSERT INTO AS_ACCESSES_ALL_ALL
1030 ( access_id
1031 ,access_type
1032 ,salesforce_id
1033 ,sales_group_id
1034 ,person_id
1035 ,salesforce_role_code
1036 ,customer_id
1037 ,address_id
1038 ,sales_lead_id
1039 ,freeze_flag
1040 ,reassign_flag
1041 ,team_leader_flag
1042 ,last_update_date
1043 ,last_updated_by
1044 ,creation_date
1045 ,created_by
1046 ,last_update_login
1047 ,request_id
1048 ,program_application_id
1049 ,program_id
1050 ,program_update_date
1051 ,created_by_tap_flag
1052 ,org_id
1053 ,open_flag
1054 ,lead_rank_score
1055 ,object_creation_date
1056 )
1057 (
1058 SELECT as_accesses_s.nextval
1059 ,'X'
1060 ,l_salesforce_id(i)
1061 ,l_sales_group_id(i)
1062 ,l_person_id(i)
1063 ,NULL
1064 ,L.customer_id
1065 ,L.address_id
1066 ,l_sales_lead_id(i)
1067 ,'N'
1068 ,'N'
1069 ,l_faf(i)
1070 ,SYSDATE
1071 ,p_terr_globals.user_id
1072 ,SYSDATE
1076 ,p_terr_globals.prog_appl_id
1073 ,p_terr_globals.user_id
1074 ,p_terr_globals.last_update_login
1075 ,p_terr_globals.request_id
1077 ,p_terr_globals.prog_id
1078 ,SYSDATE
1079 ,'Y'
1080 ,l_org_id(i)
1081 ,DECODE(STS.opp_open_status_flag, 'Y', 'Y', NULL)
1082 ,L.lead_rank_score
1083 ,L.creation_date
1084 FROM DUAL, AS_SALES_LEADS L, AS_STATUSES_B STS
1085 WHERE L.sales_lead_id = l_sales_lead_id(i)
1086 AND L.status_code = STS.status_code
1087 AND NOT EXISTS ( SELECT 'X'
1088 FROM AS_ACCESSES_ALL_ALL AA
1089 WHERE AA.sales_lead_id IS NOT NULL
1090 AND AA.lead_id IS NULL
1091 AND AA.delete_flag IS NULL
1092 AND AA.sales_lead_id = l_sales_lead_id(i)
1093 AND AA.salesforce_id = l_salesforce_id(i)
1094 AND AA.sales_group_id = l_sales_group_id(i)
1095 )
1096 );
1097 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);
1098 COMMIT;
1099 EXCEPTION
1100 WHEN DUP_VAL_ON_INDEX THEN
1101 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);
1102 FOR i IN l_first .. l_last LOOP
1103 BEGIN
1104 INSERT INTO AS_ACCESSES_ALL_ALL
1105 ( access_id
1106 ,access_type
1107 ,salesforce_id
1108 ,sales_group_id
1109 ,person_id
1110 ,salesforce_role_code
1111 ,customer_id
1112 ,address_id
1113 ,sales_lead_id
1114 ,freeze_flag
1115 ,reassign_flag
1116 ,team_leader_flag
1117 ,last_update_date
1118 ,last_updated_by
1119 ,creation_date
1120 ,created_by
1121 ,last_update_login
1122 ,request_id
1123 ,program_application_id
1124 ,program_id
1125 ,program_update_date
1126 ,created_by_tap_flag
1127 ,org_id
1128 ,open_flag
1129 ,lead_rank_score
1130 ,object_creation_date
1131 )
1132 (
1133 SELECT as_accesses_s.NEXTVAL
1134 ,'X'
1135 ,l_salesforce_id(i)
1136 ,l_sales_group_id(i)
1137 ,l_person_id(i)
1138 ,NULL
1139 ,L.customer_id
1140 ,L.address_id
1141 ,l_sales_lead_id(i)
1142 ,'N'
1143 ,'N'
1144 ,l_faf(i)
1145 ,SYSDATE
1146 ,p_terr_globals.user_id
1147 ,SYSDATE
1148 ,p_terr_globals.user_id
1149 ,p_terr_globals.last_update_login
1150 ,p_terr_globals.request_id
1151 ,p_terr_globals.prog_appl_id
1152 ,p_terr_globals.prog_id
1153 ,SYSDATE
1154 ,'Y'
1155 ,l_org_id(i)
1156 ,DECODE(STS.opp_open_status_flag, 'Y', 'Y', NULL)
1157 ,L.lead_rank_score
1158 ,L.creation_date
1159 FROM DUAL,AS_SALES_LEADS L, AS_STATUSES_B STS
1160 WHERE L.sales_lead_id = l_sales_lead_id(i)
1161 AND L.status_code = STS.status_code
1162 AND NOT EXISTS ( SELECT 'X'
1163 FROM AS_ACCESSES_ALL_ALL AA
1164 WHERE AA.sales_lead_id IS NOT NULL
1165 AND AA.lead_id IS NULL
1166 AND AA.delete_flag IS NULL
1167 AND AA.sales_lead_id = l_sales_lead_id(i)
1168 AND AA.salesforce_id = l_salesforce_id(i)
1169 AND AA.sales_group_id = l_sales_group_id(i)
1170 )
1171 );
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 NULL;
1175 END;
1176 END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
1177 COMMIT;
1178 WHEN OTHERS THEN
1179 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1180 x_errbuf := SQLERRM;
1181 x_retcode := SQLCODE;
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 RAISE;
1184 END;
1185 l_first := l_last + 1;
1186 l_last := l_first + l_var;
1187 IF l_first > l_sales_lead_id.last THEN
1188 l_flag := FALSE;
1189 END IF;
1190 END LOOP; /* l_flag loop */
1191 END IF; --l_sales_lead_id.count > 0
1192 END LOOP; -- loop for more bulk_size fetches
1193 l_sales_lead_id.DELETE;
1194 l_org_id.DELETE;
1195 l_salesforce_id.DELETE;
1196 l_sales_group_id.DELETE;
1197 l_person_id.DELETE;
1198 l_faf.DELETE;
1199 IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1200
1201 EXCEPTION
1202 WHEN others THEN
1203 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
1204 x_errbuf := SQLERRM;
1205 x_retcode := SQLCODE;
1206 x_return_status := FND_API.G_RET_STS_ERROR;
1207 IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1208 RAISE;
1209 END INSERT_ACCESSES_LEADS;
1210
1211 /************************** End Insert Into Entity Accesses*************/
1212
1213 /************************** Start Insert Into Terr Accesses*************/
1214
1215 PROCEDURE INSERT_TERR_ACCESSES_LEADS(
1216 x_errbuf OUT NOCOPY VARCHAR2,
1217 x_retcode OUT NOCOPY VARCHAR2,
1218 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1219 x_return_status OUT NOCOPY VARCHAR2)
1220 IS
1221 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1222
1226 l_terr_id num_list;
1223 l_sales_lead_id num_list;
1224 l_salesforce_id num_list;
1225 l_sales_group_id num_list;
1227
1228 l_var NUMBER;
1229 l_limit_flag BOOLEAN := FALSE;
1230 l_worker_id NUMBER;
1231 l_max_fetches NUMBER := 10000;
1232 l_loop_count NUMBER := 0;
1233 l_flag BOOLEAN;
1234 l_first NUMBER;
1235 l_last NUMBER;
1236
1237
1238 CURSOR ins_tacc(c_worker_id number) IS
1239 SELECT w.terr_id
1240 ,w.trans_object_id
1241 ,w.resource_id
1242 ,w.group_id
1243 FROM JTF_TAE_1001_LEAD_WINNERS W
1244 WHERE W.source_id = -1001
1245 AND W.worker_id = c_worker_id
1246 AND W.resource_type = 'RS_EMPLOYEE'
1247 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.
1248 GROUP BY W.terr_id,
1249 W.trans_object_id,
1250 W.resource_id,
1251 W.group_id;
1252
1253 BEGIN
1257 | Re-Initialize variables and null out if necessary.
1254 /*-------------------------------------------------------------------------+
1255 | PROGRAM LOGIC
1256 |
1258 | Almost the same as accesses, except the insertion is into as_territory_accesses
1259 | and there is no involvement of role.
1260 |
1261 +-------------------------------------------------------------------------*/
1262 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
1263 x_return_status := FND_API.G_RET_STS_SUCCESS;
1264 l_worker_id := p_terr_globals.worker_id;
1265 l_var := p_terr_globals.bulk_size;
1266 OPEN ins_tacc(l_worker_id);
1267 LOOP
1268 IF (l_limit_flag) THEN EXIT; END IF;
1269 EXIT WHEN ins_tacc%NOTFOUND;
1270 l_loop_count := l_loop_count + 1;
1271 l_sales_lead_id.DELETE;
1272 l_salesforce_id.DELETE;
1273 l_sales_group_id.DELETE;
1274 l_terr_id.DELETE;
1275 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'LOOPCOUNT :- ' || l_loop_count);
1276 BEGIN
1277
1278 FETCH ins_tacc BULK COLLECT INTO l_terr_id,
1279 l_sales_lead_id, l_salesforce_id, l_sales_group_id
1280 LIMIT l_max_fetches;
1281 -- Initialize variables
1282 l_flag := TRUE;
1283 l_first := 0;
1284 l_last := 0;
1285
1286 IF l_sales_lead_id.COUNT < l_max_fetches THEN l_limit_flag := TRUE; END IF;
1287 IF l_sales_lead_id.COUNT > 0 THEN
1288 l_flag := TRUE;
1289 l_first := l_sales_lead_id.first;
1290 l_last := l_first + l_var;
1291 WHILE l_flag LOOP
1292 IF l_last > l_sales_lead_id.last THEN
1293 l_last := l_sales_lead_id.last;
1294 END IF;
1295 BEGIN
1296 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);
1297 FORALL i IN l_first .. l_last
1298 INSERT INTO AS_TERRITORY_ACCESSES
1299 ( access_id,
1300 territory_id,
1301 user_territory_id,
1302 last_update_date,
1303 last_updated_by,
1304 creation_date,
1305 created_by,
1306 last_update_login,
1307 request_id,
1308 program_application_id,
1309 program_id,
1310 program_update_date
1311 )
1312 (
1313 SELECT
1314 V.acc_id,
1315 l_terr_id(i),
1316 l_terr_id(i),
1317 SYSDATE,
1318 p_terr_globals.user_id,
1319 SYSDATE,
1320 p_terr_globals.user_id,
1321 p_terr_globals.last_update_login,
1322 p_terr_globals.request_id,
1323 p_terr_globals.prog_appl_id,
1324 p_terr_globals.prog_id,
1325 SYSDATE
1326 FROM
1327 ( SELECT DISTINCT A.access_id acc_id
1328 FROM AS_ACCESSES_ALL_ALL A
1329 WHERE A.sales_lead_id=l_sales_lead_id(i)
1330 AND A.sales_group_id = l_sales_group_id(i)
1331 AND A.salesforce_id=l_salesforce_id(i)
1332 AND A.sales_lead_id is NOT NULL
1333 AND A.delete_flag IS NULL
1334 AND A.lead_id is NULL
1335 AND NOT EXISTS
1336 (SELECT 'X'
1337 FROM AS_TERRITORY_ACCESSES AST
1338 WHERE AST.access_id = A.access_id
1339 AND AST.territory_id = l_terr_id(i))
1340 ) V
1341 );
1342 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);
1343 COMMIT;
1344 EXCEPTION
1345 WHEN DUP_VAL_ON_INDEX THEN
1349 INSERT INTO AS_TERRITORY_ACCESSES
1346 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);
1347 FOR i IN l_first .. l_last LOOP
1348 BEGIN
1350 ( access_id,
1351 territory_id,
1352 user_territory_id,
1353 last_update_date,
1354 last_updated_by,
1355 creation_date,
1356 created_by,
1357 last_update_login,
1358 request_id,
1359 program_application_id,
1360 program_id,
1361 program_update_date
1362 )
1363 (
1364 SELECT
1365 v.acc_id,
1366 l_terr_id(i),
1367 l_terr_id(i),
1368 SYSDATE,
1369 p_terr_globals.user_id,
1370 SYSDATE,
1371 p_terr_globals.user_id,
1372 p_terr_globals.last_update_login,
1373 p_terr_globals.request_id,
1374 p_terr_globals.prog_appl_id,
1375 p_terr_globals.prog_id,
1376 SYSDATE
1377 FROM
1378 ( SELECT DISTINCT a.access_id acc_id
1379 FROM AS_ACCESSES_ALL_ALL A
1380 WHERE A.sales_lead_id=l_sales_lead_id(i)
1381 AND A.sales_group_id = l_sales_group_id(i)
1382 AND A.salesforce_id=l_salesforce_id(i)
1383 AND A.sales_lead_id is NOT NULL
1384 AND A.lead_id is NULL
1385 AND A.delete_flag IS NULL
1386 AND NOT EXISTS
1387 (SELECT 'X'
1388 FROM AS_TERRITORY_ACCESSES AST
1389 WHERE AST.access_id = A.access_id
1390 AND AST.territory_id = l_terr_id(i))
1391 ) V
1392 );
1393 EXCEPTION
1394 WHEN Others THEN
1395 NULL;
1396 END;
1397 END LOOP;
1398 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);
1399 COMMIT;
1400 WHEN Others THEN
1401 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1402 x_errbuf := SQLERRM;
1403 x_retcode := SQLCODE;
1404 x_return_status := FND_API.G_RET_STS_ERROR;
1405 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1406 RAISE;
1407 END;
1408 l_first := l_last + 1;
1409 l_last := l_first + l_var;
1410 IF l_first > l_sales_lead_id.last THEN
1411 l_flag := FALSE;
1412 END IF;
1413 END LOOP;
1414 END IF; --l_sales_lead_id.count > 0
1415 EXCEPTION
1416 WHEN Others THEN
1417 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC , SQLERRM, TO_CHAR(SQLCODE));
1418 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1419 x_errbuf := SQLERRM;
1420 x_retcode := SQLCODE;
1421 x_return_status := FND_API.G_RET_STS_ERROR;
1422 RAISE;
1423 END;
1424 END LOOP; -- end loop for insert into territory accesses
1425 l_sales_lead_id.DELETE;
1426 l_salesforce_id.DELETE;
1427 l_sales_group_id.DELETE;
1428 l_terr_id.DELETE;
1429 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1430 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1431 EXCEPTION
1432 WHEN others THEN
1433 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1434 x_errbuf := SQLERRM;
1435 x_retcode := SQLCODE;
1436 x_return_status := FND_API.G_RET_STS_ERROR;
1437 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1438 RAISE;
1439 END INSERT_TERR_ACCESSES_LEADS;
1440
1441 /************************** End Insert Into Terr Accesses*************/
1442
1443
1444 /************************** Start Lead Cleanup ***********************/
1445
1446 PROCEDURE Perform_Lead_Cleanup(
1447 x_errbuf OUT NOCOPY VARCHAR2,
1448 x_retcode OUT NOCOPY VARCHAR2,
1449 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1450 x_return_status OUT NOCOPY VARCHAR2)
1451 IS
1452
1453 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1454 l_sales_lead_id num_list;
1455 l_access_id num_list;
1456
1457
1458 l_flag BOOLEAN;
1459 l_first NUMBER;
1460 l_last NUMBER;
1461 l_var NUMBER;
1462 l_attempts NUMBER := 0;
1463
1464 l_worker_id NUMBER;
1465
1466 l_del_flag BOOLEAN:=FALSE;
1467 l_limit_flag BOOLEAN := FALSE;
1468 l_max_fetches NUMBER := 10000;
1469 l_loop_count NUMBER := 0;
1470 G_NUM_REC CONSTANT NUMBER:=10000;
1471 G_DEL_REC CONSTANT NUMBER:=10001;
1472
1473
1474
1475 CURSOR del_lead_totalmode(c_worker_id number) IS
1476 SELECT distinct trans_object_id
1477 FROM JTF_TAE_1001_LEAD_TRANS
1478 WHERE worker_id=c_worker_id;
1479
1480 CURSOR del_lead_newmode(c_worker_id number) IS
1481 SELECT distinct trans_object_id
1482 FROM JTF_TAE_1001_LEAD_NM_TRANS
1483 WHERE worker_id=c_worker_id;
1484
1485 BEGIN
1486 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
1487 x_return_status := FND_API.G_RET_STS_SUCCESS;
1488 l_worker_id := p_terr_globals.worker_id;
1489 l_var := p_terr_globals.bulk_size;
1490 l_max_fetches := p_terr_globals.cursor_limit;
1491
1492 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1493 OPEN del_lead_totalmode(l_worker_id);
1494 ELSE
1495 OPEN del_lead_newmode(l_worker_id);
1496 END IF;
1497 LOOP --{L1
1498 IF (l_limit_flag) THEN EXIT; END IF;
1499
1503 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1500 l_loop_count := l_loop_count + 1;
1501 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
1502 BEGIN
1504 EXIT WHEN del_lead_totalmode%NOTFOUND;
1505 FETCH del_lead_totalmode BULK COLLECT INTO l_sales_lead_id
1506 LIMIT l_max_fetches;
1507 ELSE
1508 EXIT WHEN del_lead_newmode%NOTFOUND;
1509 FETCH del_lead_newmode BULK COLLECT INTO l_sales_lead_id
1510 LIMIT l_max_fetches;
1511 END IF;
1512 -- Initialize variables (Ist Init)
1513 l_flag := TRUE;
1514 l_first := 0;
1515 l_last := 0;
1516 l_attempts := 1;
1517
1518 IF l_sales_lead_id.COUNT < l_max_fetches THEN
1519 l_limit_flag := TRUE;
1520 END IF;
1521
1522 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_START);
1523 IF l_sales_lead_id.count > 0 THEN --{I1
1524 l_flag := TRUE;
1525 l_first := l_sales_lead_id.first;
1526 l_last := l_first + l_var;
1527 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED ||
1528 l_sales_lead_id.FIRST || '-' ||
1529 l_sales_lead_id.LAST);
1530 WHILE l_flag LOOP --{L2 10K cust loop
1531 IF l_last > l_sales_lead_id.LAST THEN
1532 l_last := l_sales_lead_id.LAST;
1533 END IF;
1534 l_del_flag := FALSE;
1535 l_attempts := 1;
1536 LOOP --{L3 to update only 10k record at a time
1537 IF (l_del_flag) THEN EXIT; END IF;
1538 l_del_flag := FALSE;
1539 WHILE l_attempts < 3 LOOP --{L4
1540 BEGIN
1541 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);
1542 FORALL i in l_first..l_last
1543 UPDATE AS_ACCESSES_ALL_ALL ACC
1544 SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1545 WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1546 AND ACC.freeze_flag = 'N'
1547 AND ACC.lead_id IS NULL
1548 AND ACC.sales_lead_id IS NOT NULL
1549 AND ACC.delete_flag IS NULL
1550 AND NOT EXISTS (SELECT 'X'
1551 FROM JTF_TAE_1001_LEAD_WINNERS W
1552 WHERE W.trans_object_id = ACC.sales_lead_id
1553 AND W.worker_id = l_worker_id
1554 AND W.resource_id = ACC.salesforce_id
1555 AND W.group_id = ACC.sales_group_id)
1556 AND ROWNUM < G_DEL_REC;
1557 COMMIT;
1558 l_attempts := 3;
1559 IF l_access_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
1560 EXCEPTION
1561 WHEN DUP_VAL_ON_INDEX THEN
1562 BEGIN
1563 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);
1564 FORALL i in l_first..l_last
1565 DELETE FROM AS_ACCESSES_ALL_ALL ACC
1566 WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1567 AND ACC.freeze_flag = 'N'
1568 AND ACC.lead_id IS NULL
1569 AND ACC.sales_lead_id IS NOT NULL
1570 AND NOT EXISTS (SELECT 'X'
1571 FROM JTF_TAE_1001_LEAD_WINNERS W
1572 WHERE W.trans_object_id = ACC.sales_lead_id
1573 AND W.worker_id = l_worker_id
1574 AND W.resource_id = ACC.salesforce_id
1575 AND W.group_id = ACC.sales_group_id)
1576 AND ROWNUM < G_DEL_REC;
1577 COMMIT;
1578 l_attempts := 3;
1579 IF l_access_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 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));
1583 END;
1584 WHEN deadlock_detected THEN
1585 BEGIN --{I2
1586 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);
1587 ROLLBACK;
1588 l_attempts := l_attempts +1;
1589 IF l_attempts = 3 THEN
1590 FOR i IN l_first .. l_last LOOP --{L5
1591 BEGIN
1592 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);
1593 UPDATE AS_ACCESSES_ALL_ALL ACC
1594 SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1595 WHERE ACC.sales_lead_id = l_sales_lead_id(i)
1596 AND ACC.freeze_flag = 'N'
1597 AND ACC.lead_id IS NULL
1598 AND ACC.sales_lead_id IS NOT NULL
1599 AND ACC.delete_flag IS NULL
1600 AND NOT EXISTS (SELECT 'X'
1601 FROM JTF_TAE_1001_LEAD_WINNERS W
1602 WHERE W.trans_object_id = ACC.sales_lead_id
1603 AND W.resource_id = ACC.salesforce_id
1604 AND W.worker_id = l_worker_id
1605 AND W.group_id = ACC.sales_group_id);
1606 COMMIT;
1607 EXCEPTION
1608 WHEN DUP_VAL_ON_INDEX THEN
1609 BEGIN
1610 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);
1611 DELETE FROM AS_ACCESSES_ALL_ALL ACC
1612 WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1613 AND ACC.freeze_flag = 'N'
1614 AND ACC.lead_id IS NULL
1615 AND ACC.sales_lead_id IS NOT NULL
1616 AND NOT EXISTS (SELECT 'X'
1617 FROM JTF_TAE_1001_LEAD_WINNERS W
1618 WHERE W.trans_object_id = ACC.sales_lead_id
1619 AND W.resource_id = ACC.salesforce_id
1620 AND W.worker_id = l_worker_id
1624 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);
1621 AND W.group_id = ACC.sales_group_id);
1622 EXCEPTION
1623 WHEN OTHERS THEN
1625 AS_GAR.LOG('SALES_LEAD_ID - ' || l_sales_lead_id(i));
1626 END;
1627 END;
1628 END LOOP; --}L5
1629 COMMIT;
1630 l_del_flag := TRUE;
1631 END IF;
1632 END; --}I2 end of deadlock exception
1633 WHEN OTHERS THEN
1634 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1635 IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1636 IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1637 x_errbuf := SQLERRM;
1638 x_retcode := SQLCODE;
1639 x_return_status := FND_API.G_RET_STS_ERROR;
1640 RAISE;
1641 END;
1642 END LOOP; --}L4 l_attempts loop 3 trys
1643 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);
1644 END LOOP; --}L3 -- to update only 10k record at a time on accesses
1645 l_first := l_last + 1;
1646 l_last := l_first + l_var;
1647 IF l_first > l_sales_lead_id.LAST THEN
1648 l_flag := FALSE;
1649 END IF;
1650 END LOOP; --}L2 while l_flag loop (10K cust loop)
1651 END IF;--}I1
1652 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
1653 COMMIT;
1654 EXCEPTION
1655 WHEN Others THEN
1656 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1657 IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1658 IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1659 x_errbuf := SQLERRM;
1660 x_retcode := SQLCODE;
1661 x_return_status := FND_API.G_RET_STS_ERROR;
1662 RAISE;
1663 END;
1664 END LOOP;--}L1
1665 IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1666 IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1670 x_errbuf := SQLERRM;
1671 x_retcode := SQLCODE;
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 RAISE;
1674 END PERFORM_LEAD_CLEANUP;
1675
1676 /************************** End Lead Cleanup ***********************/
1677 /**************************** Start Assign Lead Owner ********************/
1678 PROCEDURE ASSIGN_LEAD_OWNER(
1679 x_errbuf OUT NOCOPY VARCHAR2,
1680 x_retcode OUT NOCOPY VARCHAR2,
1681 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1682 x_return_status OUT NOCOPY VARCHAR2)
1683 IS
1684 l_return_status VARCHAR2(1);
1685 l_msg_count NUMBER;
1686 l_msg_data VARCHAR2(2000);
1687
1688 CURSOR lead_owner_totalmode(c_worker_id number) IS
1689 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
1690 max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1691 FROM AS_ACCESSES_ALL_ALL aaa,
1692 ( SELECT distinct trans_object_id
1693 FROM JTF_TAE_1001_LEAD_TRANS
1694 WHERE worker_id=c_worker_id ) w
1695 WHERE aaa.lead_id is NULL
1696 AND aaa.sales_lead_id is NOT NULL
1697 AND aaa.delete_flag is NULL
1698 AND aaa.sales_lead_id=w.trans_object_id
1699 AND aaa.sales_lead_id+0=w.trans_object_id
1700 GROUP BY aaa.sales_lead_id
1701 HAVING SUM(DECODE(aaa.CREATED_BY_TAP_FLAG,'Y',1,0)) > 0
1702 AND SUM(DECODE(aaa.owner_flag,'Y',1,0)) = 0
1703 UNION -- Union added for Bug#4035168
1704 SELECT trans_object_id ,0
1705 FROM JTF_TAE_1001_LEAD_TRANS w
1706 WHERE worker_id = c_worker_id
1707 AND NOT EXISTS
1708 (SELECT 'x'
1709 FROM AS_ACCESSES_ALL aaa
1710 WHERE aaa.sales_lead_id =w.trans_object_id
1711 AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1712 OR aaa.owner_flag='Y'));
1713
1714
1715 CURSOR lead_owner_newmode(c_worker_id number) IS
1716 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
1717 max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1718 FROM AS_ACCESSES_ALL_ALL aaa,
1719 ( SELECT distinct trans_object_id
1720 FROM JTF_TAE_1001_LEAD_NM_TRANS
1721 WHERE worker_id=c_worker_id ) w
1722 WHERE aaa.lead_id is null
1723 AND aaa.delete_flag is null
1724 AND aaa.sales_lead_id=w.trans_object_id
1725 AND aaa.sales_lead_id+0=w.trans_object_id
1726 GROUP BY aaa.sales_lead_id
1727 HAVING SUM(DECODE(aaa.CREATED_BY_TAP_FLAG,'Y',1,0)) > 0
1728 AND SUM(DECODE(aaa.owner_flag,'Y',1,0)) = 0
1729 UNION -- Union added for Bug#4035168
1730 SELECT trans_object_id ,0
1731 FROM JTF_TAE_1001_LEAD_NM_TRANS w
1732 WHERE worker_id = c_worker_id
1733 AND NOT EXISTS
1734 (SELECT 'x'
1735 FROM AS_ACCESSES_ALL aaa
1736 WHERE aaa.sales_lead_id =w.trans_object_id
1737 AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1738 OR aaa.owner_flag='Y'));
1739
1740
1741
1742 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1743
1744 l_sales_lead_id num_list;
1745 l_access_id num_list;
1746
1747 l_limit_flag BOOLEAN := FALSE;
1748 l_max_fetches NUMBER := 10000;
1749 l_loop_count NUMBER := 0;
1750
1751 l_attempts NUMBER := 0;
1755 l_first NUMBER;
1752 l_exceptions BOOLEAN := FALSE;
1753
1754 l_flag BOOLEAN;
1756 l_last NUMBER;
1757 l_worker_id NUMBER;
1758 l_var NUMBER;
1759
1760
1761
1762 BEGIN
1763 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
1764
1765 x_return_status := FND_API.G_RET_STS_SUCCESS;
1766 l_worker_id:=p_terr_globals.worker_id;
1767 l_var :=p_terr_globals.bulk_size;
1768 l_max_fetches := p_terr_globals.cursor_limit;
1769 IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
1770 THEN
1771 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
1772 AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
1773 p_request_id => p_terr_globals.request_id,
1774 p_worker_id => p_terr_globals.worker_id,
1775 x_return_status => l_return_status,
1776 x_msg_count => l_msg_count,
1777 x_msg_data => l_msg_data);
1778 ELSE
1779 LOOP
1780 IF (l_limit_flag) THEN EXIT; END IF;
1781 l_loop_count := l_loop_count + 1;
1782 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
1783 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1784 OPEN lead_owner_totalmode(l_worker_id);
1785 FETCH lead_owner_totalmode BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
1786 CLOSE lead_owner_totalmode;
1787 ELSE
1788 OPEN lead_owner_newmode(l_worker_id);
1789 FETCH lead_owner_newmode BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
1790 CLOSE lead_owner_newmode;
1791 END IF;
1792 l_flag := TRUE;
1793 l_first := 0;
1794 l_last := 0;
1795 l_attempts := 1;
1796
1797 IF l_sales_lead_id.COUNT < l_max_fetches THEN
1798 l_limit_flag := TRUE;
1799 END IF;
1800
1801 IF l_sales_lead_id.COUNT > 0 THEN
1802 l_flag := TRUE;
1803 l_first := l_sales_lead_id.FIRST;
1804 l_last := l_first + l_var;
1805 WHILE l_flag LOOP
1806 IF l_last > l_sales_lead_id.LAST THEN
1807 l_last := l_sales_lead_id.LAST;
1808 END IF;
1809 WHILE l_attempts < 3 LOOP
1810 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
1811 BEGIN
1812 FORALL i in l_first .. l_last
1813 UPDATE AS_SALES_LEADS sl
1814 SET sl.last_update_date = SYSDATE,
1815 sl.last_updated_by = p_terr_globals.user_id,
1816 sl.last_update_login = p_terr_globals.last_update_login,
1817 sl.request_id = p_terr_globals.request_id,
1818 sl.program_application_id = p_terr_globals.prog_appl_id,
1819 sl.program_id = p_terr_globals.prog_id,
1820 sl.program_update_date = SYSDATE,
1821 ( sl.assign_to_salesforce_id,
1822 sl.assign_sales_group_id,
1823 sl.assign_to_person_id
1824 ) =
1825 ( SELECT salesforce_id,sales_group_id,person_id
1826 FROM as_accesses_all_all
1827 WHERE access_id = l_access_id(i)
1828 )
1829 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1830 COMMIT;
1831 l_attempts := 3;
1832 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1833 EXCEPTION
1834 WHEN deadlock_detected THEN
1835 BEGIN
1836 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
1837 ROLLBACK;
1838 l_attempts := l_attempts +1;
1839 IF l_attempts = 3 THEN
1840 FOR i IN l_first .. l_last LOOP
1841 BEGIN
1842 UPDATE AS_SALES_LEADS sl
1843 SET sl.last_update_date = SYSDATE,
1844 sl.last_updated_by = p_terr_globals.user_id,
1845 sl.last_update_login = p_terr_globals.last_update_login,
1846 sl.request_id = p_terr_globals.request_id,
1847 sl.program_application_id = p_terr_globals.prog_appl_id,
1848 sl.program_id = p_terr_globals.prog_id,
1849 sl.program_update_date = SYSDATE,
1850 ( sl.assign_to_salesforce_id,
1851 sl.assign_sales_group_id,
1852 sl.assign_to_person_id
1853 ) =
1854 ( SELECT salesforce_id,sales_group_id,person_id
1855 FROM as_accesses_all_all
1856 WHERE access_id = l_access_id(i)
1857 )
1858 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 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));
1862 END;
1863 END LOOP;
1864 COMMIT;
1865 END IF;
1866 END; -- end of deadlock exception
1867 WHEN OTHERS THEN
1868 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
1869 x_errbuf := SQLERRM;
1870 x_retcode := SQLCODE;
1871 x_return_status := FND_API.G_RET_STS_ERROR;
1872 RAISE;
1873 END;
1874 END LOOP;
1875
1876 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES');
1877 l_attempts := 1;
1878 WHILE l_attempts < 3 LOOP
1879 BEGIN
1880 FORALL i in l_first .. l_last
1881 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,
1882 ACC.LAST_UPDATE_DATE = SYSDATE,
1883 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1887 ACC.PROGRAM_ID = p_terr_globals.prog_id,
1884 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1885 ACC.REQUEST_ID = p_terr_globals.request_id,
1886 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
1888 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
1889 ACC.owner_flag = 'Y'
1890 WHERE ACC.access_id = l_access_id(i);
1891 COMMIT;
1892 l_attempts := 3;
1893 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);
1894 EXCEPTION
1895 WHEN deadlock_detected THEN
1896 BEGIN
1900 IF l_attempts = 3 THEN
1897 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_DEADLOCK ||l_attempts );
1898 l_attempts := l_attempts +1;
1899 ROLLBACK;
1901 FOR i IN l_first .. l_last LOOP
1902 BEGIN
1903 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,
1904 ACC.LAST_UPDATE_DATE = SYSDATE,
1905 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1906 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1907 ACC.REQUEST_ID = p_terr_globals.request_id,
1908 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
1909 ACC.PROGRAM_ID = p_terr_globals.prog_id,
1910 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
1911 ACC.owner_flag = 'Y'
1912 WHERE ACC.access_id = l_access_id(i);
1913 EXCEPTION
1914 WHEN OTHERS THEN
1915 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));
1916 END;
1917 END LOOP;
1918 COMMIT;
1919 END IF;
1920 END; -- end of deadlock exception
1921 WHEN OTHERS THEN
1922 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE SALES_LEADS ACCESSES', SQLERRM, TO_CHAR(SQLCODE));
1923 x_errbuf := SQLERRM;
1924 x_retcode := SQLCODE;
1925 x_return_status := FND_API.G_RET_STS_ERROR;
1926 RAISE;
1927 END;
1928 END LOOP;
1929 l_first := l_last + 1;
1930 l_last := l_first + l_var;
1931 IF l_first > l_sales_lead_id.LAST THEN
1932 l_flag := FALSE;
1933 END IF;
1934 END LOOP;
1935 END IF; --l_sales_lead_id.count > 0
1936 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
1937 END LOOP;
1938 l_limit_flag := FALSE;
1939 l_loop_count := 0;
1940 l_access_id.delete;
1941 l_sales_lead_id.delete;
1942 l_attempts := 1;
1943 END IF; -- (Custom or Non Custom)
1944
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1948 x_errbuf := SQLERRM;
1949 x_retcode := SQLCODE;
1950 x_return_status := FND_API.G_RET_STS_ERROR;
1951 RAISE;
1952 END ASSIGN_LEAD_OWNER;
1953
1954 /**************************** End Assign Lead Owner ********************/
1955 /**************************** Start Assign_Default_Lead_Owner ********************/
1956 PROCEDURE ASSIGN_DEFAULT_LEAD_OWNER(
1957 x_errbuf OUT NOCOPY VARCHAR2,
1958 x_retcode OUT NOCOPY VARCHAR2,
1959 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1960 x_return_status OUT NOCOPY VARCHAR2)
1961 IS
1962 l_return_status VARCHAR2(1);
1963 l_msg_count NUMBER;
1964 l_msg_data VARCHAR2(2000);
1965
1966 CURSOR tot_lead_owner_def(c_worker_id number) IS
1967 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
1968 FROM as_accesses_all_all aaa,
1969 ( select distinct trans_object_id
1970 from jtf_tae_1001_lead_trans
1971 where worker_id=c_worker_id ) w
1972 WHERE aaa.lead_id is null
1973 and aaa.delete_flag is null
1974 AND aaa.sales_lead_id=w.trans_object_id
1975 AND aaa.sales_lead_id+0=w.trans_object_id
1976 GROUP BY aaa.sales_lead_id
1977 HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 0
1978 UNION -- Union added for Bug#4035168
1979 select trans_object_id from jtf_tae_1001_lead_trans w
1980 where worker_id = c_worker_id and not exists
1981 (select 'x' from as_accesses_all aaa
1982 where aaa.sales_lead_id =w.trans_object_id);
1983
1984 CURSOR tot_lead_owner_tap(c_worker_id number) IS
1985 Select trans_object_id from jtf_tae_1001_lead_trans w
1986 where worker_id = c_worker_id and not exists
1987 (select 'x' from as_accesses_all aaa
1988 where aaa.sales_lead_id =w.trans_object_id);
1989
1990 CURSOR new_lead_owner_def(c_worker_id number) IS
1991 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
1995 where worker_id=c_worker_id ) w
1992 FROM as_accesses_all_all aaa,
1993 ( select distinct trans_object_id
1994 from JTF_TAE_1001_LEAD_NM_TRANS
1996 WHERE aaa.lead_id is null
1997 and aaa.delete_flag is null
1998 AND aaa.sales_lead_id=w.trans_object_id
1999 AND aaa.sales_lead_id+0=w.trans_object_id
2000 GROUP BY aaa.sales_lead_id
2001 HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 0
2002 UNION -- Union added for Bug#4035168
2003 select trans_object_id from JTF_TAE_1001_LEAD_NM_TRANS w
2004 where worker_id = c_worker_id and not exists
2005 (select 'x' from as_accesses_all aaa
2006 where aaa.sales_lead_id =w.trans_object_id);
2007
2008 CURSOR new_lead_owner_tap(c_worker_id number) IS
2009 Select trans_object_id from JTF_TAE_1001_LEAD_NM_TRANS w
2010 where worker_id = c_worker_id and not exists
2011 (select 'x' from as_accesses_all aaa
2012 where aaa.sales_lead_id =w.trans_object_id);
2013
2014 CURSOR c_get_group_id(c_resource_id NUMBER) IS
2015 SELECT grp.group_id
2016 FROM JTF_RS_GROUP_MEMBERS mem,
2017 JTF_RS_ROLE_RELATIONS rrel,
2018 JTF_RS_ROLES_B role,
2019 JTF_RS_GROUP_USAGES u,
2020 JTF_RS_GROUPS_B grp
2021 WHERE mem.group_member_id = rrel.role_resource_id
2022 AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
2023 AND rrel.role_id = role.role_id
2024 AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
2025 AND mem.delete_flag <> 'Y'
2026 AND rrel.delete_flag <> 'Y'
2027 AND SYSDATE BETWEEN rrel.start_date_active AND
2028 NVL(rrel.end_date_active,SYSDATE)
2029 AND mem.resource_id = c_resource_id
2030 AND mem.group_id = u.group_id
2031 AND u.usage = 'SALES'
2032 AND mem.group_id = grp.group_id
2033 AND SYSDATE BETWEEN grp.start_date_active AND
2034 NVL(grp.end_date_active,SYSDATE)
2035 AND ROWNUM < 2;
2036
2037 -- A resource may not be in any group. Besides, jtf_rs_group_members
2038 -- may not have person_id for all resources. Therefore, get person_id
2039 -- in this cursor, instead of in the above cursor.
2040 CURSOR c_get_person_id(c_resource_id NUMBER) IS
2041 SELECT res.source_id
2042 FROM jtf_rs_resource_extns res
2043 WHERE res.resource_id = c_resource_id;
2044
2045 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
2046
2047 l_sales_lead_id num_list;
2048 l_access_id num_list;
2049
2050 l_limit_flag BOOLEAN := FALSE;
2051 l_max_fetches NUMBER := 10000;
2052 l_loop_count NUMBER := 0;
2053
2054 l_attempts NUMBER := 0;
2055 l_exceptions BOOLEAN := FALSE;
2056
2057 l_flag BOOLEAN;
2058 l_first NUMBER;
2059 l_last NUMBER;
2060 l_worker_id NUMBER;
2061 l_var NUMBER;
2062
2063 l_assign_manual_flag VARCHAR2(1);
2064 l_resource_id NUMBER;
2065 l_group_id NUMBER;
2066 l_person_id NUMBER;
2067
2068 BEGIN
2069 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
2070
2071 x_return_status := FND_API.G_RET_STS_SUCCESS;
2072 l_worker_id:=p_terr_globals.worker_id;
2073 l_var :=p_terr_globals.bulk_size;
2074 l_max_fetches := p_terr_globals.cursor_limit;
2075
2076 l_assign_manual_flag := nvl(FND_PROFILE.Value('AS_LEAD_ASSIGN_MANUAL'),'N');
2077
2078 IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
2079 THEN
2080 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
2081 AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
2082 p_request_id => p_terr_globals.request_id,
2083 p_worker_id => p_terr_globals.worker_id,
2084 x_return_status => l_return_status,
2085 x_msg_count => l_msg_count,
2086 x_msg_data => l_msg_data);
2087 ELSE
2088
2089 l_resource_id := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
2090
2091 OPEN c_get_group_id (l_resource_id);
2092 FETCH c_get_group_id INTO l_group_id;
2093 CLOSE c_get_group_id;
2094 OPEN c_get_person_id (l_resource_id);
2095 FETCH c_get_person_id INTO l_person_id;
2096 CLOSE c_get_person_id;
2097 LOOP
2098 IF (l_limit_flag) THEN EXIT; END IF;
2099 l_loop_count := l_loop_count + 1;
2100 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2101
2102 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2103 IF l_assign_manual_flag = 'N' THEN
2104 OPEN tot_lead_owner_tap(l_worker_id);
2105 FETCH tot_lead_owner_tap BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2106 CLOSE tot_lead_owner_tap;
2107 ELSE
2108 OPEN tot_lead_owner_def(l_worker_id);
2109 FETCH tot_lead_owner_def BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2110 CLOSE tot_lead_owner_def;
2111 END IF;
2112 ELSE
2113 IF l_assign_manual_flag = 'N' THEN
2114 OPEN new_lead_owner_tap(l_worker_id);
2115 FETCH new_lead_owner_tap BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2116 CLOSE new_lead_owner_tap;
2117 ELSE
2118 OPEN new_lead_owner_def(l_worker_id);
2119 FETCH new_lead_owner_def BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2120 CLOSE new_lead_owner_def;
2121 END IF;
2122 END IF;
2123
2124 l_flag := TRUE;
2125 l_first := 0;
2126 l_last := 0;
2127 l_attempts := 1;
2128
2129 IF l_sales_lead_id.COUNT < l_max_fetches THEN
2130 l_limit_flag := TRUE;
2131 END IF;
2132
2136 l_last := l_first + l_var;
2133 IF l_sales_lead_id.COUNT > 0 THEN
2134 l_flag := TRUE;
2135 l_first := l_sales_lead_id.FIRST;
2137 WHILE l_flag LOOP
2138 IF l_last > l_sales_lead_id.LAST THEN
2139 l_last := l_sales_lead_id.LAST;
2140 END IF;
2141
2142 WHILE l_attempts < 3 LOOP
2143 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL');
2144 BEGIN
2145 FORALL i in l_first .. l_last
2146 INSERT INTO AS_ACCESSES_ALL_ALL
2147 (ACCESS_ID,
2148 LAST_UPDATE_DATE,
2149 LAST_UPDATED_BY,
2150 CREATION_DATE,
2151 CREATED_BY,
2152 LAST_UPDATE_LOGIN,
2153 PROGRAM_APPLICATION_ID,
2154 PROGRAM_UPDATE_DATE,
2155 ACCESS_TYPE,
2156 FREEZE_FLAG,
2157 REASSIGN_FLAG,
2158 TEAM_LEADER_FLAG,
2159 OWNER_FLAG,
2160 CREATED_BY_TAP_FLAG,
2161 CUSTOMER_ID,
2162 ADDRESS_ID,
2163 SALES_LEAD_ID,
2164 SALESFORCE_ID,
2165 PERSON_ID,
2166 SALES_GROUP_ID,
2167 REQUEST_ID,
2168 OPEN_FLAG,
2169 LEAD_RANK_SCORE,
2170 OBJECT_CREATION_DATE)
2171 ( SELECT
2172 as_accesses_s.nextval,
2173 SYSDATE,
2174 p_terr_globals.user_id,
2175 SYSDATE,
2176 p_terr_globals.user_id,
2177 p_terr_globals.last_update_login,
2178 p_terr_globals.prog_appl_id,
2179 SYSDATE,
2180 'X',
2181 NVL(L.accept_flag, 'N'),
2182 'N',
2183 'Y',
2184 'Y',
2185 'N',
2186 L.customer_id,
2187 L.address_id,
2188 l_sales_lead_id(i),
2189 l_resource_id,
2190 l_person_id,
2191 l_group_id,
2192 p_terr_globals.request_id,
2193 L.status_open_flag,
2194 L.lead_rank_score,
2195 L.creation_date
2196 FROM DUAL ,
2197 AS_SALES_LEADS L
2198 WHERE L.sales_lead_id = l_sales_lead_id(i)
2199 AND NOT EXISTS ( select 'X'
2200 from AS_ACCESSES_ALL_ALL aa
2201 where aa.sales_lead_id is not null
2202 and aa.lead_id is null
2203 and aa.delete_flag is null
2204 and aa.sales_lead_id = l_sales_lead_id(i)
2205 and aa.salesforce_id = l_resource_id
2206 and nvl(aa.sales_group_id,-777) = nvl(l_group_id,-777)
2207 )
2208 );
2209
2210 COMMIT;
2211 l_attempts := 3;
2212 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2213 EXCEPTION
2214 WHEN deadlock_detected THEN
2215 BEGIN
2216 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2217 ROLLBACK;
2218 l_attempts := l_attempts +1;
2219 IF l_attempts = 3 THEN
2220 FOR i IN l_first .. l_last LOOP
2221 BEGIN
2222 INSERT INTO AS_ACCESSES_ALL_ALL
2223 (ACCESS_ID,
2224 LAST_UPDATE_DATE,
2225 LAST_UPDATED_BY,
2226 CREATION_DATE,
2227 CREATED_BY,
2228 LAST_UPDATE_LOGIN,
2229 PROGRAM_APPLICATION_ID,
2230 PROGRAM_UPDATE_DATE,
2231 ACCESS_TYPE,
2232 FREEZE_FLAG,
2233 REASSIGN_FLAG,
2234 TEAM_LEADER_FLAG,
2235 OWNER_FLAG,
2239 SALES_LEAD_ID,
2236 CREATED_BY_TAP_FLAG,
2237 CUSTOMER_ID,
2238 ADDRESS_ID,
2240 SALESFORCE_ID,
2241 PERSON_ID,
2242 SALES_GROUP_ID,
2243 REQUEST_ID,
2244 OPEN_FLAG,
2245 LEAD_RANK_SCORE,
2246 OBJECT_CREATION_DATE)
2247 ( SELECT
2248 as_accesses_s.nextval,
2249 SYSDATE,
2250 p_terr_globals.user_id,
2251 SYSDATE,
2252 p_terr_globals.user_id,
2253 p_terr_globals.last_update_login,
2254 p_terr_globals.prog_appl_id,
2255 SYSDATE,
2256 'X',
2257 NVL(L.accept_flag, 'N'),
2258 'N',
2259 'Y',
2260 'Y',
2261 'N',
2262 L.customer_id,
2263 L.address_id,
2264 l_sales_lead_id(i),
2265 l_resource_id,
2266 l_person_id,
2267 l_group_id,
2271 L.creation_date
2268 p_terr_globals.request_id,
2269 L.status_open_flag,
2270 L.lead_rank_score,
2272 FROM DUAL ,
2273 AS_SALES_LEADS L
2274 WHERE L.sales_lead_id = l_sales_lead_id(i)
2275 AND NOT EXISTS ( select 'X'
2276 from AS_ACCESSES_ALL_ALL aa
2277 where aa.sales_lead_id is not null
2278 and aa.lead_id is null
2279 and aa.delete_flag is null
2280 and aa.sales_lead_id = l_sales_lead_id(i)
2281 and aa.salesforce_id = l_resource_id
2282 and nvl(aa.sales_group_id,-777) = nvl(l_group_id,-777)
2283 )
2284 );
2285 EXCEPTION
2286 WHEN OTHERS THEN
2287 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2288 END;
2289 END LOOP;
2290 COMMIT;
2291 END IF;
2292 END; -- end of deadlock exception
2293 WHEN OTHERS THEN
2294 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'INSERT INTO AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2295 x_errbuf := SQLERRM;
2296 x_retcode := SQLCODE;
2297 x_return_status := FND_API.G_RET_STS_ERROR;
2298 RAISE;
2299 END;
2300 END LOOP;
2301
2302 WHILE l_attempts < 3 LOOP
2303 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2304 BEGIN
2305 FORALL i in l_first .. l_last
2306 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2307 ACC.LAST_UPDATE_DATE = SYSDATE,
2308 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2309 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2310 ACC.REQUEST_ID = p_terr_globals.request_id,
2311 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
2312 ACC.PROGRAM_ID = p_terr_globals.prog_id,
2313 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
2314 ACC.owner_flag = 'Y'
2315 WHERE ACC.sales_lead_id is not null
2316 and ACC.lead_id is null
2317 and ACC.delete_flag is null
2318 and ACC.sales_lead_id = l_sales_lead_id(i)
2319 and ACC.salesforce_id = l_resource_id
2320 and nvl(ACC.sales_group_id,-777) = nvl(l_group_id,-777);
2321 COMMIT;
2322 l_attempts := 3;
2323 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2324 EXCEPTION
2325 WHEN deadlock_detected THEN
2326 BEGIN
2327 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2328 ROLLBACK;
2329 l_attempts := l_attempts +1;
2330 IF l_attempts = 3 THEN
2331 FOR i IN l_first .. l_last LOOP
2332 BEGIN
2333 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2334 ACC.LAST_UPDATE_DATE = SYSDATE,
2335 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2336 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2337 ACC.REQUEST_ID = p_terr_globals.request_id,
2338 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
2339 ACC.PROGRAM_ID = p_terr_globals.prog_id,
2340 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
2341 ACC.owner_flag = 'Y'
2342 WHERE ACC.sales_lead_id is not null
2343 and ACC.lead_id is null
2344 and ACC.delete_flag is null
2345 and ACC.sales_lead_id = l_sales_lead_id(i)
2346 and ACC.salesforce_id = l_resource_id
2347 and nvl(ACC.sales_group_id,-777) = nvl(l_group_id,-777);
2348 EXCEPTION
2349 WHEN OTHERS THEN
2350 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2351 END;
2352 END LOOP;
2353 COMMIT;
2354 END IF;
2355 END; -- end of deadlock exception
2356 WHEN OTHERS THEN
2357 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2358 x_errbuf := SQLERRM;
2359 x_retcode := SQLCODE;
2360 x_return_status := FND_API.G_RET_STS_ERROR;
2361 RAISE;
2362 END;
2363 END LOOP;
2364
2365 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
2366 l_attempts := 1;
2367 WHILE l_attempts < 3 LOOP
2368 BEGIN
2369 FORALL i in l_first .. l_last
2370 UPDATE AS_SALES_LEADS sl SET
2371 sl.last_update_date = SYSDATE,
2372 sl.last_updated_by = p_terr_globals.user_id,
2373 sl.last_update_login = p_terr_globals.last_update_login,
2374 sl.request_id = p_terr_globals.request_id,
2375 sl.program_application_id = p_terr_globals.prog_appl_id,
2376 sl.program_id = p_terr_globals.prog_id,
2377 sl.program_update_date = SYSDATE,
2378 sl.assign_to_salesforce_id = l_resource_id,
2379 sl.assign_sales_group_id = l_group_id,
2380 sl.assign_to_person_id = l_person_id
2381 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2382 COMMIT;
2383 l_attempts := 3;
2384 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2385 EXCEPTION
2386 WHEN deadlock_detected THEN
2387 BEGIN
2388 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS::' || AS_GAR.G_DEADLOCK ||l_attempts );
2392 FOR i IN l_first .. l_last LOOP
2389 l_attempts := l_attempts +1;
2390 ROLLBACK;
2391 IF l_attempts = 3 THEN
2393 BEGIN
2394 UPDATE AS_SALES_LEADS sl SET
2395 sl.last_update_date = SYSDATE,
2396 sl.last_updated_by = p_terr_globals.user_id,
2397 sl.last_update_login = p_terr_globals.last_update_login,
2398 sl.request_id = p_terr_globals.request_id,
2399 sl.program_application_id = p_terr_globals.prog_appl_id,
2400 sl.program_id = p_terr_globals.prog_id,
2401 sl.program_update_date = SYSDATE,
2402 sl.assign_to_salesforce_id = l_resource_id,
2403 sl.assign_sales_group_id = l_group_id,
2404 sl.assign_to_person_id = l_person_id
2405 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2406 EXCEPTION
2407 WHEN OTHERS THEN
2408 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
2409 END;
2410 END LOOP;
2411 COMMIT;
2412 END IF;
2413 END; -- end of deadlock exception
2414 WHEN OTHERS THEN
2415 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
2416 x_errbuf := SQLERRM;
2417 x_retcode := SQLCODE;
2418 x_return_status := FND_API.G_RET_STS_ERROR;
2419 RAISE;
2420 END;
2421 END LOOP;
2425 l_flag := FALSE;
2422 l_first := l_last + 1;
2423 l_last := l_first + l_var;
2424 IF l_first > l_sales_lead_id.LAST THEN
2426 END IF;
2427 END LOOP;
2428 END IF; --l_sales_lead_id.count > 0
2429 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
2430 END LOOP;
2431 l_limit_flag := FALSE;
2432 l_loop_count := 0;
2433 l_access_id.delete;
2434 l_sales_lead_id.delete;
2435 l_attempts := 1;
2436 END IF; -- (Custom or Non Custom)
2437
2438 EXCEPTION
2439 WHEN OTHERS THEN
2440 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2441 x_errbuf := SQLERRM;
2442 x_retcode := SQLCODE;
2443 x_return_status := FND_API.G_RET_STS_ERROR;
2444 RAISE;
2445 END ASSIGN_DEFAULT_LEAD_OWNER;
2446
2447 /**************************** End Assign_Default_Lead_Owner ********************/
2448 /**************************** Start Uncheck_Lead_Owner ********************/
2449 PROCEDURE UNCHECK_LEAD_OWNER(
2450 x_errbuf OUT NOCOPY VARCHAR2,
2451 x_retcode OUT NOCOPY VARCHAR2,
2452 p_terr_globals IN AS_GAR.TERR_GLOBALS,
2453 x_return_status OUT NOCOPY VARCHAR2)
2454 IS
2455 l_return_status VARCHAR2(1);
2456 l_msg_count NUMBER;
2457 l_msg_data VARCHAR2(2000);
2458
2459 CURSOR tot_lead_owner(c_worker_id number) IS
2460 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
2461 FROM as_accesses_all_all aaa,
2462 ( select distinct trans_object_id
2463 from jtf_tae_1001_lead_trans
2464 where worker_id=c_worker_id ) w
2465 WHERE aaa.lead_id is null
2466 and aaa.delete_flag is null
2467 AND aaa.sales_lead_id=w.trans_object_id
2468 AND aaa.sales_lead_id+0=w.trans_object_id
2469 GROUP BY aaa.sales_lead_id
2470 HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 1;
2471
2472 CURSOR new_lead_owner(c_worker_id number) IS
2473 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id
2474 FROM as_accesses_all_all aaa,
2475 ( select distinct trans_object_id
2476 from JTF_TAE_1001_LEAD_NM_TRANS
2477 where worker_id=c_worker_id ) w
2478 WHERE aaa.lead_id is null
2479 and aaa.delete_flag is null
2480 AND aaa.sales_lead_id=w.trans_object_id
2481 AND aaa.sales_lead_id+0=w.trans_object_id
2482 GROUP BY aaa.sales_lead_id
2483 HAVING sum(decode(aaa.owner_flag,'Y',1,0)) = 1;
2484
2485 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
2486
2487 l_sales_lead_id num_list;
2488 l_access_id num_list;
2489
2490 l_limit_flag BOOLEAN := FALSE;
2491 l_max_fetches NUMBER := 10000;
2492 l_loop_count NUMBER := 0;
2493
2494 l_attempts NUMBER := 0;
2495 l_exceptions BOOLEAN := FALSE;
2496
2497 l_flag BOOLEAN;
2498 l_first NUMBER;
2499 l_last NUMBER;
2500 l_worker_id NUMBER;
2501 l_var NUMBER;
2502
2503 BEGIN
2504 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
2505
2506 x_return_status := FND_API.G_RET_STS_SUCCESS;
2507 l_worker_id:=p_terr_globals.worker_id;
2508 l_var :=p_terr_globals.bulk_size;
2509 l_max_fetches := p_terr_globals.cursor_limit;
2510 IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
2511 THEN
2512 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
2513 AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
2514 p_request_id => p_terr_globals.request_id,
2515 p_worker_id => p_terr_globals.worker_id,
2516 x_return_status => l_return_status,
2517 x_msg_count => l_msg_count,
2518 x_msg_data => l_msg_data);
2519 ELSE
2520 LOOP
2521 IF (l_limit_flag) THEN EXIT; END IF;
2522 l_loop_count := l_loop_count + 1;
2523 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2524 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2525 OPEN tot_lead_owner(l_worker_id);
2526 FETCH tot_lead_owner BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2527 CLOSE tot_lead_owner;
2528 ELSE
2529 OPEN new_lead_owner(l_worker_id);
2530 FETCH new_lead_owner BULK COLLECT INTO l_sales_lead_id LIMIT l_max_fetches;
2531 CLOSE new_lead_owner;
2532 END IF;
2533 l_flag := TRUE;
2534 l_first := 0;
2535 l_last := 0;
2536 l_attempts := 1;
2537
2538 IF l_sales_lead_id.COUNT < l_max_fetches THEN
2539 l_limit_flag := TRUE;
2540 END IF;
2541
2542 IF l_sales_lead_id.COUNT > 0 THEN
2543 l_flag := TRUE;
2544 l_first := l_sales_lead_id.FIRST;
2545 l_last := l_first + l_var;
2546 WHILE l_flag LOOP
2547 IF l_last > l_sales_lead_id.LAST THEN
2548 l_last := l_sales_lead_id.LAST;
2549 END IF;
2550 WHILE l_attempts < 3 LOOP
2551 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL');
2552 BEGIN
2553 FORALL i in l_first .. l_last
2557 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2554 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2555 ACC.LAST_UPDATE_DATE = SYSDATE,
2556 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2558 ACC.REQUEST_ID = p_terr_globals.request_id,
2559 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
2560 ACC.PROGRAM_ID = p_terr_globals.prog_id,
2561 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
2562 ACC.owner_flag = 'N'
2563 WHERE ACC.sales_lead_id = l_sales_lead_id(i)
2564 and ACC.owner_flag = 'Y'
2565 and ACC.freeze_flag = 'N';
2566 COMMIT;
2567 l_attempts := 3;
2568 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2569 EXCEPTION
2570 WHEN deadlock_detected THEN
2571 BEGIN
2572 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2573 ROLLBACK;
2574 l_attempts := l_attempts +1;
2575 IF l_attempts = 3 THEN
2576 FOR i IN l_first .. l_last LOOP
2577 BEGIN
2578 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = nvl(object_version_number,0) + 1,
2579 ACC.LAST_UPDATE_DATE = SYSDATE,
2580 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
2581 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
2582 ACC.REQUEST_ID = p_terr_globals.request_id,
2583 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
2584 ACC.PROGRAM_ID = p_terr_globals.prog_id,
2585 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
2586 ACC.owner_flag = 'N'
2587 WHERE ACC.sales_lead_id = l_sales_lead_id(i)
2588 and ACC.owner_flag = 'Y'
2589 and ACC.freeze_flag = 'N';
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2593 END;
2594 END LOOP;
2595 COMMIT;
2596 END IF;
2597 END; -- end of deadlock exception
2598 WHEN OTHERS THEN
2602 x_return_status := FND_API.G_RET_STS_ERROR;
2599 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES_ALL_ALL', SQLERRM, TO_CHAR(SQLCODE));
2600 x_errbuf := SQLERRM;
2601 x_retcode := SQLCODE;
2603 RAISE;
2604 END;
2605 END LOOP;
2606
2607 l_first := l_last + 1;
2608 l_last := l_first + l_var;
2609 IF l_first > l_sales_lead_id.LAST THEN
2610 l_flag := FALSE;
2611 END IF;
2612 END LOOP;
2613 END IF; --l_sales_lead_id.count > 0
2614 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
2615 END LOOP;
2616 l_limit_flag := FALSE;
2617 l_loop_count := 0;
2618 l_access_id.delete;
2619 l_sales_lead_id.delete;
2620 l_attempts := 1;
2621 END IF; -- (Custom or Non Custom)
2622
2623 EXCEPTION
2624 WHEN OTHERS THEN
2625 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2626 x_errbuf := SQLERRM;
2627 x_retcode := SQLCODE;
2628 x_return_status := FND_API.G_RET_STS_ERROR;
2629 RAISE;
2630 END UNCHECK_LEAD_OWNER;
2631
2632 /**************************** End Uncheck_Lead_Owner ********************/
2633 /**************************** Start Uncheck_Assign_Salesforce ********************/
2634 PROCEDURE UNCHECK_ASSIGN_SALESFORCE(
2635 x_errbuf OUT NOCOPY VARCHAR2,
2636 x_retcode OUT NOCOPY VARCHAR2,
2637 p_terr_globals IN AS_GAR.TERR_GLOBALS,
2638 x_return_status OUT NOCOPY VARCHAR2)
2639 IS
2640 l_return_status VARCHAR2(1);
2641 l_msg_count NUMBER;
2642 l_msg_data VARCHAR2(2000);
2643
2644 CURSOR tot_lead_owner(c_worker_id number) IS
2645 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
2646 max(decode(aaa.owner_flag,'Y',aaa.access_id,-999)) access_id
2647 FROM as_accesses_all_all aaa,
2648 ( select distinct trans_object_id
2649 from jtf_tae_1001_lead_trans
2650 where worker_id=c_worker_id ) w
2651 WHERE aaa.lead_id is null
2652 and aaa.delete_flag is null
2653 AND aaa.sales_lead_id=w.trans_object_id
2654 AND aaa.sales_lead_id+0=w.trans_object_id
2655 GROUP BY aaa.sales_lead_id
2656 UNION -- Union added for Bug#4035168
2657 select trans_object_id ,0 from jtf_tae_1001_lead_trans w
2658 where worker_id = c_worker_id and not exists
2659 (select 'x' from as_accesses_all aaa
2660 where aaa.sales_lead_id =w.trans_object_id);
2661
2662
2663 CURSOR new_lead_owner(c_worker_id number) IS
2664 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
2665 max(decode(aaa.owner_flag,'Y',aaa.access_id,-999)) access_id
2666 FROM as_accesses_all_all aaa,
2667 ( select distinct trans_object_id
2668 from JTF_TAE_1001_LEAD_NM_TRANS
2669 where worker_id=c_worker_id ) w
2670 WHERE aaa.lead_id is null
2671 and aaa.delete_flag is null
2672 AND aaa.sales_lead_id=w.trans_object_id
2673 AND aaa.sales_lead_id+0=w.trans_object_id
2674 GROUP BY aaa.sales_lead_id
2675 UNION -- Union added for Bug#4035168
2676 select trans_object_id ,0 from JTF_TAE_1001_LEAD_NM_TRANS w
2677 where worker_id = c_worker_id and not exists
2678 (select 'x' from as_accesses_all aaa
2679 where aaa.sales_lead_id =w.trans_object_id);
2680
2681 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
2682
2683 l_sales_lead_id num_list;
2684 l_access_id num_list;
2685
2686 l_limit_flag BOOLEAN := FALSE;
2687 l_max_fetches NUMBER := 10000;
2688 l_loop_count NUMBER := 0;
2689
2690 l_attempts NUMBER := 0;
2691 l_exceptions BOOLEAN := FALSE;
2692
2693 l_flag BOOLEAN;
2694 l_first NUMBER;
2695 l_last NUMBER;
2696 l_worker_id NUMBER;
2697 l_var NUMBER;
2698
2699 BEGIN
2700 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
2701
2702 x_return_status := FND_API.G_RET_STS_SUCCESS;
2703 l_worker_id:=p_terr_globals.worker_id;
2704 l_var :=p_terr_globals.bulk_size;
2705 l_max_fetches := p_terr_globals.cursor_limit;
2706 IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
2707 THEN
2708 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
2709 AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
2710 p_request_id => p_terr_globals.request_id,
2711 p_worker_id => p_terr_globals.worker_id,
2712 x_return_status => l_return_status,
2713 x_msg_count => l_msg_count,
2714 x_msg_data => l_msg_data);
2715 ELSE
2716 LOOP
2717 IF (l_limit_flag) THEN EXIT; END IF;
2718 l_loop_count := l_loop_count + 1;
2719 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
2720 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2721 OPEN tot_lead_owner(l_worker_id);
2722 FETCH tot_lead_owner BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
2723 CLOSE tot_lead_owner;
2724 ELSE
2725 OPEN new_lead_owner(l_worker_id);
2726 FETCH new_lead_owner BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
2727 CLOSE new_lead_owner;
2728 END IF;
2729 l_flag := TRUE;
2730 l_first := 0;
2731 l_last := 0;
2732 l_attempts := 1;
2733
2734 IF l_sales_lead_id.COUNT < l_max_fetches THEN
2738 IF l_sales_lead_id.COUNT > 0 THEN
2735 l_limit_flag := TRUE;
2736 END IF;
2737
2739 l_flag := TRUE;
2740 l_first := l_sales_lead_id.FIRST;
2741 l_last := l_first + l_var;
2742 WHILE l_flag LOOP
2743 IF l_last > l_sales_lead_id.LAST THEN
2744 l_last := l_sales_lead_id.LAST;
2745 END IF;
2746 WHILE l_attempts < 3 LOOP
2747 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
2748 BEGIN
2749 FORALL i in l_first .. l_last
2750 UPDATE AS_SALES_LEADS sl SET
2751 sl.last_update_date = SYSDATE,
2752 sl.last_updated_by = p_terr_globals.user_id,
2753 sl.last_update_login = p_terr_globals.last_update_login,
2754 sl.request_id = p_terr_globals.request_id,
2755 sl.program_application_id = p_terr_globals.prog_appl_id,
2756 sl.program_id = p_terr_globals.prog_id,
2757 sl.program_update_date = SYSDATE,
2758 ( sl.assign_to_salesforce_id,
2759 sl.assign_sales_group_id,
2760 sl.assign_to_person_id
2761 ) =
2762 ( SELECT salesforce_id,sales_group_id,person_id
2763 FROM as_accesses_all_all
2764 WHERE access_id = l_access_id(i)
2765 )
2766 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2767 COMMIT;
2768 l_attempts := 3;
2769 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
2770 EXCEPTION
2771 WHEN deadlock_detected THEN
2772 BEGIN
2773 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
2774 ROLLBACK;
2775 l_attempts := l_attempts +1;
2776 IF l_attempts = 3 THEN
2777 FOR i IN l_first .. l_last LOOP
2778 BEGIN
2779 UPDATE AS_SALES_LEADS sl SET
2780 sl.last_update_date = SYSDATE,
2781 sl.last_updated_by = p_terr_globals.user_id,
2782 sl.last_update_login = p_terr_globals.last_update_login,
2783 sl.request_id = p_terr_globals.request_id,
2784 sl.program_application_id = p_terr_globals.prog_appl_id,
2785 sl.program_id = p_terr_globals.prog_id,
2786 sl.program_update_date = SYSDATE,
2787 ( sl.assign_to_salesforce_id,
2788 sl.assign_sales_group_id,
2789 sl.assign_to_person_id
2790 ) =
2791 ( SELECT salesforce_id,sales_group_id,person_id
2792 FROM as_accesses_all_all
2793 WHERE access_id = l_access_id(i)
2794 )
2795 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
2796 EXCEPTION
2797 WHEN OTHERS THEN
2798 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));
2799 END;
2800 END LOOP;
2801 COMMIT;
2802 END IF;
2803 END; -- end of deadlock exception
2804 WHEN OTHERS THEN
2805 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
2806 x_errbuf := SQLERRM;
2807 x_retcode := SQLCODE;
2808 x_return_status := FND_API.G_RET_STS_ERROR;
2809 RAISE;
2810 END;
2811 END LOOP;
2812
2813 l_first := l_last + 1;
2814 l_last := l_first + l_var;
2815 IF l_first > l_sales_lead_id.LAST THEN
2816 l_flag := FALSE;
2817 END IF;
2818 END LOOP;
2819 END IF; --l_sales_lead_id.count > 0
2820 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
2821 END LOOP;
2822 l_limit_flag := FALSE;
2823 l_loop_count := 0;
2824 l_access_id.delete;
2825 l_sales_lead_id.delete;
2826 l_attempts := 1;
2827 END IF; -- (Custom or Non Custom)
2828
2829 EXCEPTION
2830 WHEN OTHERS THEN
2831 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2832 x_errbuf := SQLERRM;
2833 x_retcode := SQLCODE;
2834 x_return_status := FND_API.G_RET_STS_ERROR;
2835 RAISE;
2836 END UNCHECK_ASSIGN_SALESFORCE;
2837
2838 /**************************** End Uncheck_Assign_Salesforce ********************/
2839
2840 END AS_GAR_LEADS_PUB;
2841