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