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