[Home] [Help]
PACKAGE BODY: APPS.ASN_MIG_SALES_TEAM_PVT
Source
1 PACKAGE BODY asn_mig_sales_team_pvt AS
2 /* $Header: asnvmstb.pls 120.7 2007/12/26 08:22:31 snsarava ship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- asn_mig_sales_team_pvt
10 --
11 -- PURPOSE
12 -- This package contains migration related code for sales team.
13 --
14 -- NOTES
15 --
16 -- HISTORY
17 -- sumahali 01/09/2005 Created
18 -- **********************************************************************************************************
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):='asn_mig_sales_team_pvt';
21 G_FILE_NAME CONSTANT VARCHAR2(12):='asnvmscb.pls';
22
23 --
24 --
25
26 -- Fix Lead Line and Sales Credits End Day Flags
27 PROCEDURE FixEnddayFlags (p_lead_id IN NUMBER, p_debug_flag IN VARCHAR2)
28 IS
29
30 l_module_name CONSTANT VARCHAR2(256) :=
31 'asn.plsql.asn_mig_sales_team_pvt.FixEnddayFlags';
32
33 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
34 TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
35 TYPE Var1Tab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
36
37 l_log_ids NumTab;
38 l_lead_line_ids NumTab;
39 l_sales_credit_ids NumTab;
40 l_last_update_dates DateTab;
41 l_endday_log_flags Var1Tab;
42
43 l_future_date DATE := sysdate + 1000;
44 l_prev_last_update_date DATE;
45 l_last_update_date DATE;
46 l_prev_lead_line_id NUMBER;
47 l_prev_sales_credit_id NUMBER;
48 l_endday_log_flag VARCHAR2(1);
49 l_update_count NUMBER;
50
51 CURSOR c_lead_line_logs(p_lead_id NUMBER) IS
52 SELECT log_id, lead_line_id, last_update_date
53 FROM as_lead_lines_log
54 WHERE lead_id = p_lead_id
55 ORDER BY lead_line_id ASC, last_update_date DESC, log_id DESC;
56
57 CURSOR c_sales_credits_logs(p_lead_id NUMBER) IS
58 SELECT log_id, sales_credit_id, last_update_date
59 FROM as_sales_credits_log
60 WHERE lead_id = p_lead_id
61 ORDER BY sales_credit_id ASC, last_update_date DESC, log_id DESC;
62
63 BEGIN
64 IF (p_debug_flag = 'Y' AND
65 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
66 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
67 'Start Lead Line Logs Log for lead_id=' || p_lead_id);
68 END IF;
69
70 OPEN c_lead_line_logs(p_lead_id);
71 FETCH c_lead_line_logs BULK COLLECT
72 INTO l_log_ids, l_lead_line_ids, l_last_update_dates;
73 CLOSE c_lead_line_logs;
74
75 IF (p_debug_flag = 'Y' AND
76 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
77 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
78 'Num Lead Line Logs Logs=' || l_log_ids.COUNT);
79 END IF;
80
81 l_update_count := 0;
82
83 IF l_log_ids.COUNT > 0 THEN
84 l_prev_lead_line_id := -37;
85 FOR i IN l_log_ids.FIRST..l_log_ids.LAST LOOP
86 IF l_lead_line_ids(i) <> l_prev_lead_line_id THEN
87 l_prev_lead_line_id := l_lead_line_ids(i);
88 l_prev_last_update_date := l_future_date;
89 END IF;
90
91 l_endday_log_flag := 'Y';
92 l_last_update_date := trunc(l_last_update_dates(i));
93 IF l_prev_last_update_date = l_last_update_date THEN
94 l_endday_log_flag := 'N';
95 END IF;
96
97 l_endday_log_flags(i) := l_endday_log_flag;
98 l_prev_last_update_date := l_last_update_date;
99 END LOOP;
100
101 FORALL i IN l_log_ids.FIRST..l_log_ids.LAST
102 UPDATE as_lead_lines_log -- @@
103 SET endday_log_flag = l_endday_log_flags(i)
104 WHERE log_id = l_log_ids(i) AND endday_log_flag IS NULL;
105
106 l_update_count := SQL%ROWCOUNT;
107 END IF;
108
109 IF (p_debug_flag = 'Y' AND
110 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
112 'Num Lead Line Logs Updated=' || l_update_count);
113 END IF;
114
115 OPEN c_sales_credits_logs(p_lead_id);
116 FETCH c_sales_credits_logs BULK COLLECT
117 INTO l_log_ids, l_sales_credit_ids, l_last_update_dates;
118 CLOSE c_sales_credits_logs;
119
120 IF (p_debug_flag = 'Y' AND
121 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
122 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
123 'Num Sales Credits Logs=' || l_log_ids.COUNT);
124 END IF;
125
126 l_update_count := 0;
127
128 IF l_log_ids.COUNT > 0 THEN
129 l_prev_sales_credit_id := -37;
130 FOR i IN l_log_ids.FIRST..l_log_ids.LAST LOOP
131 IF l_sales_credit_ids(i) <> l_prev_sales_credit_id THEN
132 l_prev_sales_credit_id := l_sales_credit_ids(i);
133 l_prev_last_update_date := l_future_date;
134 END IF;
135
136 l_endday_log_flag := 'Y';
137 l_last_update_date := trunc(l_last_update_dates(i));
138 IF l_prev_last_update_date = l_last_update_date THEN
139 l_endday_log_flag := 'N';
140 END IF;
141
142 l_endday_log_flags(i) := l_endday_log_flag;
143 l_prev_last_update_date := l_last_update_date;
144 END LOOP;
145
146 FORALL i IN l_log_ids.FIRST..l_log_ids.LAST
147 UPDATE as_sales_credits_log -- @@
148 SET endday_log_flag = l_endday_log_flags(i)
149 WHERE log_id = l_log_ids(i) AND endday_log_flag IS NULL;
150
151 l_update_count := SQL%ROWCOUNT;
152 END IF;
153
154 IF (p_debug_flag = 'Y' AND
155 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
156 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
157 'End fixlog for Lead id: ' || p_lead_id ||
158 ' Num Sales Credits Updated=' || l_update_count);
159 END IF;
160 END FixEnddayFlags;
161 --Code added for ASN migration approach suggested by lester -- Start -- @@
162 PROCEDURE Mig_Dup_SalesRep_Opp (
163 errbuf OUT NOCOPY VARCHAR2,
164 retcode OUT NOCOPY NUMBER,
165 p_start_id IN VARCHAR2,
166 p_end_id IN VARCHAR2,
167 p_commit_flag IN VARCHAR2,
168 p_batch_size IN NUMBER,
169 p_debug_flag IN VARCHAR2
170 )
171 IS
172
173 l_module_name CONSTANT VARCHAR2(256) :=
174 'asn.plsql.asn_mig_sales_team_pvt.Mig_Dup_SalesRep_Opp';
175
176 l_uncommitted_opps NUMBER := 0;
177 l_remove_count NUMBER;
178 l_custfix_count NUMBER;
179 l_lead_id NUMBER;
180 l_customer_id NUMBER;
181 l_access_id NUMBER;
182 l_user_id NUMBER;
183
184 /* CURSOR c_opps_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
185 SELECT lead_id, customer_id
186 FROM as_leads_all
187 WHERE lead_id BETWEEN p_start_id AND p_end_id; */
188
189 CURSOR c_opps_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
190 SELECT DISTINCT lead_id
191 FROM AS_ACCESSES_ALL_OPP_TEMP
192 WHERE lead_id BETWEEN p_start_id AND p_end_id;
193
194 /* CURSOR c_uniq_steam(p_lead_id NUMBER) IS
195 SELECT max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
196 salesforce_id, sales_group_id, partner_customer_id,
197 partner_cont_party_id,
198 max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
199 max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
200 max(nvl(OWNER_FLAG, 'N')) owner_flag,
201 max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
202 FROM AS_ACCESSES_ALL
203 WHERE lead_id = p_lead_id
204 GROUP BY salesforce_id, sales_group_id, partner_customer_id,
205 partner_cont_party_id
206 HAVING count(access_id) > 1; */
207
208 --Code added for ASN migration approach suggested by lester -- Start
209 CURSOR c_uniq_steam(p_lead_id NUMBER) IS
210 SELECT lead_id,max(code_access_id) code_access_id,
211 salesforce_id, sales_group_id, partner_customer_id,
212 partner_cont_party_id,
213 max(FREEZE_FLAG) freeze_flag,
214 max(TEAM_LEADER_FLAG) team_leader_flag,
215 max(OWNER_FLAG) owner_flag,
216 max(CONTRIBUTOR_FLAG) contributor_flag
217 FROM AS_ACCESSES_ALL_OPP_TEMP -- AS_ACCESSES_ALL
218 WHERE lead_id = p_lead_id
219 GROUP BY lead_id,salesforce_id, sales_group_id, partner_customer_id,
220 partner_cont_party_id;
221
222 CURSOR c1 IS SELECT as_leads_s.nextval FROM dual;
223 l_max_num_rows NUMBER;
224 --Code added for ASN migration approach suggested by lester -- End
225
226 BEGIN
227
228 -- Log
229 IF (p_debug_flag = 'Y' AND
230 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
231 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
232 'Begin OSO->ASN Duplicate Sales Rep Opportunity Data Migration.');
233 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
234 'Start:' || 'p_start_id=' || p_start_id ||
235 ',p_end_id='||p_end_id ||
236 ',p_debug_flag='||p_debug_flag);
237 END IF;
238
239 l_user_id := FND_GLOBAL.user_id;
240
241 IF l_user_id IS NULL THEN
242 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
243 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
244 'Opportunities: Error: Global User Id is not set');
245 END IF;
246 RETURN;
247 END IF;
248
249 OPEN c1;
250 FETCH c1 INTO l_max_num_rows;
251 CLOSE c1;
252
253 FOR opp_rec in c_opps_in_range(p_start_id, p_end_id) LOOP -- start Main opportunity loop -- @@
254 BEGIN
255 savepoint CURR_OPP;
256
257 l_lead_id := opp_rec.lead_id; -- @@
258
259 -- FixEnddayFlags(l_lead_id, p_debug_flag);
260
261 l_remove_count := 0;
262 -- Will return one of Sales Team members of duplicates grouped by
263 -- Salesforce id, Sales Group id, union of flags (i.e flag is Y if
264 -- any one of duplicate members has it as Y, else N), returns
265 -- access_id of a record with SALESFORCE_ROLE_CODE non null
266 -- if available else any access_id prefixed with Y or N
267 FOR uniq_steam_rec in c_uniq_steam(l_lead_id) LOOP
268
269 IF (p_debug_flag = 'Y' AND
270 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
271 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
272 'Opportunities: Cleaning up duplicates for coded_access_id: ' ||
273 uniq_steam_rec.code_access_id ||
274 ' sf_id ' || uniq_steam_rec.salesforce_id ||
275 ' sg_id ' || uniq_steam_rec.sales_group_id ||
276 ' freeze_flag ' || uniq_steam_rec.freeze_flag ||
277 ' team_leader_flag ' || uniq_steam_rec.team_leader_flag ||
278 ' owner_flag ' || uniq_steam_rec.owner_flag ||
279 ' contributor_flag ' || uniq_steam_rec.contributor_flag);
280 END IF;
281
282 l_access_id := substr(uniq_steam_rec.code_access_id, 2);
283 UPDATE AS_ACCESSES_ALL_ALL -- @@
284 SET DELETE_FLAG = 'Y',
285 LAST_UPDATED_BY = l_user_id,
286 LAST_UPDATE_DATE = sysdate,
287 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
288 WHERE lead_id = l_lead_id AND -- @@
289 salesforce_id = uniq_steam_rec.salesforce_id AND
290 nvl(sales_group_id, -37) = nvl(uniq_steam_rec.sales_group_id, -37) AND
291 nvl(partner_customer_id, -37) = nvl(uniq_steam_rec.partner_customer_id, -37) AND
292 nvl(partner_cont_party_id, -37) = nvl(uniq_steam_rec.partner_cont_party_id, -37) AND
293 access_id <> l_access_id AND
294 delete_flag IS NULL;
295
296 l_remove_count := l_remove_count + SQL%ROWCOUNT;
297
298 UPDATE AS_ACCESSES_ALL -- @@
299 SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
300 TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
301 OWNER_FLAG = uniq_steam_rec.owner_flag,
302 CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,
303 LAST_UPDATED_BY = l_user_id,
304 LAST_UPDATE_DATE = sysdate,
305 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
306 WHERE access_id = l_access_id;
307 END LOOP;
308 ---- @@
309 IF (p_debug_flag = 'Y' AND
310 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
311 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
312 'Successfully processed Opp Id: ' || l_lead_id || ' Entries removed ' || l_remove_count);
313 IF l_custfix_count > 0 THEN
314 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
315 'Opp Id: ' || l_lead_id || ' CUSTOMER_ID fixes ' || l_custfix_count);
316 END IF;
317 END IF;
318
319
320 l_uncommitted_opps := l_uncommitted_opps + 1;
321
322 IF l_uncommitted_opps >= p_batch_size THEN
323 IF p_commit_flag = 'Y' THEN
324 IF (p_debug_flag = 'Y' AND
325 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
326 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
327 'Calling Commit after processing ' || l_uncommitted_opps || ' Opportunities');
328 END IF;
329 COMMIT;
330 ELSE
331 ROLLBACK;
332 END IF;
333 l_uncommitted_opps := 0;
334 END IF;
335
336 EXCEPTION
337 WHEN OTHERS then
338 Rollback to CURR_OPP;
339 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
340 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
341 'Error Processing Opp Id : ' || l_lead_id);
342 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
343 'SQL Error Msg, opp_id: ' || l_lead_id || ': '
344 || substr(SQLERRM, 1, 1950));
345 END IF;
346 END;
347 END LOOP; -- end Main opportunity loop -- @@
348
349 --Logic needs to be changed -- @@ START
350
351 IF (p_debug_flag = 'Y' AND
352 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
353 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
354 'Successfully processed Opp Id: ' || l_lead_id || ' Entries removed ' || l_remove_count);
355 END IF;
356
357 --Logic needs to be changed -- @@ --END
358 -- Commit
359 IF (p_commit_flag = 'Y') THEN
360 -- Log
361 IF (p_debug_flag = 'Y' AND
362 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
363 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
364 'Opprtunities: Committing');
365 END IF;
366 COMMIT;
367 ELSE
368 -- Log
369 IF (p_debug_flag = 'Y' AND
370 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
372 'Opportunities: Rolling back');
373 END IF;
374 ROLLBACK;
375 END IF;
376
377 IF (p_debug_flag = 'Y' AND
378 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
379 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'End of OSO->ASN Duplicate Sales Team Opportunity Data Migration.');
380 END IF;
381
382 End Mig_Dup_SalesRep_Opp;
383 --Code added for ASN migration approach suggested by lester -- End -- @@
384
385 --
386 --
387 --Code added for ASN migration approach suggested by lester -- Start -- @@
388 --lead sub
389 PROCEDURE Mig_Dup_SalesRep_Lead (
390 errbuf OUT NOCOPY VARCHAR2,
391 retcode OUT NOCOPY NUMBER,
392 p_start_id IN VARCHAR2,
393 p_end_id IN VARCHAR2,
394 p_commit_flag IN VARCHAR2,
395 p_batch_size IN NUMBER,
396 p_debug_flag IN VARCHAR2
397 )
398 IS
399
400 l_module_name CONSTANT VARCHAR2(256) :=
401 'asn.plsql.asn_mig_sales_team_pvt.Mig_Dup_SalesRep_Lead';
402
403 l_uncommitted_leads NUMBER := 0;
404 l_remove_count NUMBER;
405 l_updated_flag BOOLEAN;
406 l_sales_lead_id NUMBER;
407 l_access_id NUMBER;
408 l_user_id NUMBER;
409
410 CURSOR c_leads_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
411 SELECT DISTINCT sales_lead_id
412 FROM AS_ACCESSES_ALL_LEAD_TEMP
413 WHERE sales_lead_id BETWEEN p_start_id AND p_end_id;
414
415
416
417 /* CURSOR c_uniq_steam(p_sales_lead_id NUMBER) IS
418 SELECT max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
419 salesforce_id, sales_group_id,
420 max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
421 max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
422 max(nvl(OWNER_FLAG, 'N')) owner_flag,
423 max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
424 FROM AS_ACCESSES_ALL
425 WHERE sales_lead_id = p_sales_lead_id
426 GROUP BY salesforce_id, sales_group_id
427 HAVING count(access_id) > 1; */ -- @@
428
429 --Code added for ASN migration approach suggested by lester -- Start
430
431 CURSOR c_uniq_steam(p_sales_lead_id NUMBER) IS
432 SELECT sales_lead_id,max(code_access_id) code_access_id,
433 salesforce_id, sales_group_id,
434 max(FREEZE_FLAG) freeze_flag,
435 max(TEAM_LEADER_FLAG) team_leader_flag,
436 max(OWNER_FLAG) owner_flag,
437 max(CONTRIBUTOR_FLAG) contributor_flag
438 FROM AS_ACCESSES_ALL_LEAD_TEMP -- AS_ACCESSES_ALL
439 WHERE sales_lead_id = p_sales_lead_id
440 GROUP BY salesforce_id, sales_group_id;
441
442 --Code added for ASN migration approach suggested by lester -- End
443
444 BEGIN
445
446 -- Log
447 IF (p_debug_flag = 'Y' AND
448 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
449 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
450 'Begin OSO->ASN Duplicate Sales Rep Sales Lead Data Migration.');
451 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
452 'Start:' || 'p_start_id=' || p_start_id ||
453 ',p_end_id='||p_end_id ||
454 ',p_debug_flag='||p_debug_flag);
455 END IF;
456
457 l_user_id := FND_GLOBAL.user_id;
458
459 IF l_user_id IS NULL THEN
460 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
461 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
462 'Leads: Error: Global User Id is not set');
463 END IF;
464 RETURN;
465 END IF;
466
467 FOR lead_rec in c_leads_in_range(p_start_id, p_end_id) LOOP -- start Main lead loop -- @@
468 BEGIN
469 savepoint CURR_LEAD;
470
471 l_updated_flag := false;
472
473 l_sales_lead_id := lead_rec.sales_lead_id; -- @@
474
475 l_remove_count := 0;
476 -- Will return one of Sales Team members of duplicates grouped by
477 -- Salesforce id, Sales Group id, union of flags (i.e flag is Y if
478 -- any one of duplicate members has it as Y, else N), returns
479 -- access_id of a record with SALESFORCE_ROLE_CODE non null
480 -- if available else any access_id prefixed with Y or N
481 FOR uniq_steam_rec in c_uniq_steam(l_sales_lead_id) LOOP -- @@
482 IF (p_debug_flag = 'Y' AND
483 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
484 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
485 'Leads: Cleaning up duplicates for code_access_id: ' ||
486 uniq_steam_rec.code_access_id ||
487 ' sf_id ' || uniq_steam_rec.salesforce_id ||
488 ' sg_id ' || uniq_steam_rec.sales_group_id ||
489 ' freeze_flag ' || uniq_steam_rec.freeze_flag ||
490 ' team_leader_flag ' || uniq_steam_rec.team_leader_flag ||
491 ' owner_flag ' || uniq_steam_rec.owner_flag ||
492 ' contributor_flag ' || uniq_steam_rec.contributor_flag);
493 END IF;
494
495 l_updated_flag := true;
496
497 l_access_id := substr(uniq_steam_rec.code_access_id, 2);
498 UPDATE AS_ACCESSES_ALL_ALL -- @@
499 SET DELETE_FLAG = 'Y',
500 LAST_UPDATED_BY = l_user_id,
501 LAST_UPDATE_DATE = sysdate,
502 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
503 WHERE sales_lead_id = l_sales_lead_id AND -- @@
504 salesforce_id = uniq_steam_rec.salesforce_id AND
505 nvl(sales_group_id, -37) = nvl(uniq_steam_rec.sales_group_id, -37) AND
506 access_id <> l_access_id AND
507 delete_flag IS NULL;
508
509 l_remove_count := l_remove_count + SQL%ROWCOUNT;
510
511 UPDATE AS_ACCESSES_ALL -- @@
512 SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
513 TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
514 OWNER_FLAG = uniq_steam_rec.owner_flag,
515 CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,
516 LAST_UPDATED_BY = l_user_id,
517 LAST_UPDATE_DATE = sysdate,
518 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
519 WHERE access_id = l_access_id;
520 END LOOP;
521
522 IF (p_debug_flag = 'Y' AND
523 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
524 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
525 'Successfully processed Sales Lead Id: ' || l_sales_lead_id || ' Entries removed ' || l_remove_count);
526 END IF;
527
528 IF l_updated_flag THEN
529 l_uncommitted_leads := l_uncommitted_leads + 1;
530 END IF;
531 IF l_uncommitted_leads >= p_batch_size THEN
532 IF p_commit_flag = 'Y' THEN
533 IF (p_debug_flag = 'Y' AND
534 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
536 'Calling Commit after processing ' || l_uncommitted_leads || ' Sales Leads');
537 END IF;
538 COMMIT;
539 ELSE
540 ROLLBACK;
541 END IF;
542 l_uncommitted_leads := 0;
543 END IF;
544
545 EXCEPTION
546 WHEN OTHERS then
547 Rollback to CURR_LEAD;
548 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
549 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
550 'Error Processing Lead Id : ' || l_sales_lead_id);
551 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
552 'SQL Error Msg, lead_id: ' || l_sales_lead_id || ': '
553 || substr(SQLERRM, 1, 1950));
554 END IF;
555 END;
556 END LOOP; -- end Main lead loop -- @@
557
558 -- Commit
559 IF (p_commit_flag = 'Y') THEN
560 -- Log
561 IF (p_debug_flag = 'Y' AND
562 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
563 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
564 'Leads: Committing');
565 END IF;
566 COMMIT;
567 ELSE
568 -- Log
569 IF (p_debug_flag = 'Y' AND
570 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
571 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
572 'Leads: Rolling back');
573 END IF;
574 ROLLBACK;
575 END IF;
576
577 IF (p_debug_flag = 'Y' AND
578 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
579 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'Leads: End of OSO->ASN Duplicate Sales Team Sales Lead Data Migration.');
580 END IF;
581
582 End Mig_Dup_SalesRep_Lead;
583 --Code added for ASN migration approach suggested by lester -- End -- @@
584
585 --
586 --
587 --Procedure modified for ASN migration approach suggested by lester -- Start -- @@
588 --cust main
589 PROCEDURE Mig_Dup_SalesRep_Main
590 (
591 errbuf OUT NOCOPY VARCHAR2,
592 retcode OUT NOCOPY NUMBER,
593 p_num_workers IN NUMBER,
594 p_commit_flag IN VARCHAR2,
595 p_debug_flag IN VARCHAR2
596 )
597 IS
598 l_api_name CONSTANT VARCHAR2(30) :=
599 'Mig_Dup_SalesRep_Main';
600 l_module_name CONSTANT VARCHAR2(256) :=
601 'asn.plsql.asn_mig_sales_team_pvt.Mig_Dup_SalesRep_Main';
602 l_msg_count NUMBER;
603 l_msg_data VARCHAR2(2000);
604 l_req_id NUMBER;
605 l_request_data VARCHAR2(30);
606 l_max_num_rows NUMBER;
607 l_rows_per_worker NUMBER;
608 l_rows_per_worker1 NUMBER;
609 l_start_id NUMBER;
610 l_end_id NUMBER;
611 l_batch_size CONSTANT NUMBER := 10000;
612
613 CURSOR c1 IS SELECT hz_parties_s.nextval FROM dual;
614
615 --Code added for ASN migration approach suggested by lester -- Start
616 ls_create_temp varchar2(1000);
617 l_dup_count_cust NUMBER;
618 l_dup_count_Lead NUMBER;
619 l_dup_count_Opp NUMBER;
620
621 l_dup_min_cust NUMBER;
622 l_dup_min_Lead NUMBER;
623 l_dup_min_Opp NUMBER;
624
625 ls_program VARCHAR2(100);
626
627 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
628 l_id_list num_list;
629
630 l_dummy NUMBER:=0;
631
632 CURSOR c_opps_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
633 SELECT lead_id, customer_id
634 FROM as_leads_all
635 WHERE lead_id BETWEEN p_start_id AND p_end_id;
636
637 CURSOR c2 IS SELECT as_leads_s.nextval FROM dual;
638
639 l_lead_id NUMBER;
640 l_customer_id NUMBER;
641 l_custfix_count NUMBER;
642 l_user_id NUMBER;
643 l_max_id NUMBER;
644
645 --Code added for ASN migration approach suggested by lester -- End
646
647 BEGIN
648
649 --Code added for ASN migration approach suggested by lester -- Start
650 /*--Bug#5816258:- we should have the single, top level
651 parent program fire 3 parallel query, full scan sqls
652 on as_accesses to insert into 3 tables the few thousand dups of each type
653 (cust, leads, opps) ..parallel full scans even of the
654 large as_accesses_all table should just take a few minutes.
655 - then proceed to launch the worker programs to query up thier respective dup
656 set from the above tables, and do the corresponding updates. */
657 BEGIN
658 IF (fnd_conc_global.request_data IS NULL) THEN
659
660 --Create temp table for customer -- @@
661 INSERT /*+ APPEND PARALLEL(CUST) */
662 into AS_ACCESSES_ALL_CUST_TEMP CUST
663 (customer_id,
664 code_access_id,
665 salesforce_id,
666 sales_group_id,
667 partner_customer_id,
668 partner_cont_party_id,
669 freeze_flag,
670 team_leader_flag,
671 owner_flag,
672 contributor_flag)
673 Select /*+ PARALLEL(A)*/
674 customer_id,max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
675 salesforce_id, sales_group_id, partner_customer_id,
676 partner_cont_party_id,
677 max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
678 max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
679 max(nvl(OWNER_FLAG, 'N')) owner_flag,
680 max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
681 FROM AS_ACCESSES_ALL A ---- @@
682 WHERE lead_id IS NULL AND sales_lead_id IS NULL
683 GROUP BY customer_id,salesforce_id, sales_group_id, partner_customer_id,
684 partner_cont_party_id
685 HAVING count(access_id) > 1;
686 COMMIT;
687
688 INSERT /*+ APPEND PARALLEL(CUST) */ into AS_ACCESSES_ALL_LEAD_TEMP CUST
689 (sales_lead_id,
690 code_access_id,
691 salesforce_id,
692 sales_group_id,
693 freeze_flag,
694 team_leader_flag,
695 owner_flag,
696 contributor_flag)
697 Select /*+ PARALLEL(A)*/
698 sales_lead_id,max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID),
699 salesforce_id, sales_group_id,
700 max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
701 max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
702 max(nvl(OWNER_FLAG, 'N')) owner_flag,
703 max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
704 FROM AS_ACCESSES_ALL A ---- @@
705 WHERE sales_lead_id IS NOT NULL
706 GROUP BY sales_lead_id,salesforce_id, sales_group_id
707 HAVING count(access_id) > 1;
708 COMMIT;
709
710 INSERT /*+ APPEND PARALLEL(CUST) */ into AS_ACCESSES_ALL_OPP_TEMP CUST
711 (lead_id,
712 code_access_id,
713 salesforce_id,
714 sales_group_id,
715 partner_customer_id,
716 partner_cont_party_id,
717 freeze_flag,
718 team_leader_flag,
719 owner_flag,
720 contributor_flag)
721 Select /*+ PARALLEL(A)*/
722 Lead_id,max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) ,
723 salesforce_id, sales_group_id, partner_customer_id,partner_cont_party_id,
724 max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
725 max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
726 max(nvl(OWNER_FLAG, 'N')) owner_flag,
727 max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
728 FROM AS_ACCESSES_ALL A ---- @@
729 WHERE Lead_id IS NOT NULL
730 GROUP BY lead_id,salesforce_id, sales_group_id, partner_customer_id,
731 partner_cont_party_id
732 HAVING count(access_id) > 1;
733 COMMIT;
734
735 -- Log
736 IF (p_debug_flag = 'Y' AND
737 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
738 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
739 'Customers: Start:' || 'p_num_workers=' || p_num_workers ||
740 ',p_commit_flag=' || p_commit_flag ||
741 ',p_debug_flag=' || p_debug_flag);
742 END IF;
743
744 FOR i IN 1..3 LOOP ---First loop start -- @ @
745
746 l_id_list.delete;
747 IF i=1 THEN --Opp
748 Select count(DISTINCT lead_id),min(lead_id),max(lead_id)
749 into l_dup_count_opp,l_dup_min_opp,l_max_id
750 From AS_ACCESSES_ALL_OPP_TEMP;
751
752 l_max_num_rows := l_dup_count_opp;
753 l_start_id := l_dup_min_opp-1;
754 ls_program := 'ASN_MIG_DUP_ST_OPP_PRG';
755
756 Select distinct lead_id
757 BULK COLLECT INTO
758 l_id_list
759 FROM AS_ACCESSES_ALL_OPP_TEMP
760 ORDER BY lead_id;
761
762 ELSIF i=2 THEN --cust
763
764 Select count(DISTINCT customer_id),min(customer_id),max(customer_id)
765 into l_dup_count_cust,l_dup_min_cust,l_max_id
766 From AS_ACCESSES_ALL_CUST_TEMP;
767
768 l_max_num_rows := l_dup_count_cust;
769 l_start_id := l_dup_min_cust-1;
770 ls_program := 'ASN_MIG_DUP_ST_CUST_PRG';
771
772 Select distinct customer_id
773 BULK COLLECT INTO
774 l_id_list
775 FROM AS_ACCESSES_ALL_CUST_TEMP
776 ORDER BY customer_id;
777 ELSE -- lead
778 Select count(DISTINCT sales_lead_id),min(sales_lead_id),max(sales_lead_id)
779 into l_dup_count_lead,l_dup_min_lead,l_max_id
780 From AS_ACCESSES_ALL_LEAD_TEMP;
781
782 l_max_num_rows := l_dup_count_lead;
783 l_start_id := l_dup_min_lead-1;
784 ls_program := 'ASN_MIG_DUP_ST_LEAD_PRG';
785 Select distinct sales_lead_id
786 BULK COLLECT INTO
787 l_id_list
788 FROM AS_ACCESSES_ALL_LEAD_TEMP
789 ORDER BY sales_lead_id;
790 END IF;
791 --Code added for ASN migration approach suggested by lester --Bug#5816258 -- End
792 --
793 -- If this is first time parent is called, then split the rows
794 -- among workers and put the parent in paused state
795 --
796
797
798 --
799 -- Get maximum number of possible rows in as_leads_all
800 --
801 /* OPEN c1;
802 FETCH c1 INTO l_max_num_rows;
803 CLOSE c1; */ -- @@
804
805 --
806 -- Compute row range to be assigned to each worker
807 --
808 l_rows_per_worker := ROUND(l_max_num_rows/p_num_workers) + 1;
809
810 --
811 -- Assign rows to each worker
812 --
813
814 -- Initialize start ID value
815 -- l_start_id := 0; -- @@
816
817 l_rows_per_worker1 :=0;
818 FOR i IN 1..p_num_workers LOOP ----I
819
820 -- Initialize end ID value
821 -- l_end_id := l_start_id + l_id_list(l_rows_per_worker);
822 begin
823 l_end_id := l_id_list(l_rows_per_worker+l_rows_per_worker1);
824 exception
825 When NO_DATA_FOUND then
826 l_end_id := l_max_id;
827 end;
828
829
830
831 -- Submit the request
832 l_req_id :=
833 fnd_request.submit_request
834 (
835 application => 'ASN',
836 --program => 'ASN_MIG_DUP_ST_CUST_SUB_PRG',
837 program => ls_program, -- @@
838 description => null,
839 start_time => sysdate,
840 sub_request => true,
841 argument1 => l_start_id,
842 argument2 => l_end_id,
843 argument3 => p_commit_flag,
844 argument4 => l_batch_size,
845 argument5 => p_debug_flag,
846 argument6 => CHR(0),
847 argument7 => CHR(0),
848 argument8 => CHR(0),
849 argument9 => CHR(0),
850 argument10 => CHR(0),
851 argument11 => CHR(0),
852 argument12 => CHR(0),
853 argument13 => CHR(0),
854 argument14 => CHR(0),
855 argument15 => CHR(0),
856 argument16 => CHR(0),
857 argument17 => CHR(0),
858 argument18 => CHR(0),
859 argument19 => CHR(0),
860 argument20 => CHR(0),
861 argument21 => CHR(0),
862 argument22 => CHR(0),
863 argument23 => CHR(0),
864 argument24 => CHR(0),
865 argument25 => CHR(0),
866 argument26 => CHR(0),
867 argument27 => CHR(0),
868 argument28 => CHR(0),
869 argument29 => CHR(0),
870 argument30 => CHR(0),
871 argument31 => CHR(0),
872 argument32 => CHR(0),
873 argument33 => CHR(0),
874 argument34 => CHR(0),
875 argument35 => CHR(0),
876 argument36 => CHR(0),
877 argument37 => CHR(0),
878 argument38 => CHR(0),
879 argument39 => CHR(0),
880 argument40 => CHR(0),
881 argument41 => CHR(0),
882 argument42 => CHR(0),
883 argument43 => CHR(0),
884 argument44 => CHR(0),
885 argument45 => CHR(0),
886 argument46 => CHR(0),
887 argument47 => CHR(0),
888 argument48 => CHR(0),
889 argument49 => CHR(0),
890 argument50 => CHR(0),
891 argument51 => CHR(0),
892 argument52 => CHR(0),
893 argument53 => CHR(0),
894 argument54 => CHR(0),
895 argument55 => CHR(0),
896 argument56 => CHR(0),
897 argument57 => CHR(0),
898 argument58 => CHR(0),
899 argument59 => CHR(0),
900 argument60 => CHR(0),
901 argument61 => CHR(0),
902 argument62 => CHR(0),
903 argument63 => CHR(0),
904 argument64 => CHR(0),
905 argument65 => CHR(0),
906 argument66 => CHR(0),
907 argument67 => CHR(0),
908 argument68 => CHR(0),
909 argument69 => CHR(0),
910 argument70 => CHR(0),
911 argument71 => CHR(0),
912 argument72 => CHR(0),
913 argument73 => CHR(0),
914 argument74 => CHR(0),
915 argument75 => CHR(0),
916 argument76 => CHR(0),
917 argument77 => CHR(0),
918 argument78 => CHR(0),
919 argument79 => CHR(0),
920 argument80 => CHR(0),
921 argument81 => CHR(0),
922 argument82 => CHR(0),
923 argument83 => CHR(0),
924 argument84 => CHR(0),
925 argument85 => CHR(0),
926 argument86 => CHR(0),
927 argument87 => CHR(0),
928 argument88 => CHR(0),
929 argument89 => CHR(0),
930 argument90 => CHR(0),
931 argument91 => CHR(0),
932 argument92 => CHR(0),
933 argument93 => CHR(0),
934 argument94 => CHR(0),
935 argument95 => CHR(0),
936 argument96 => CHR(0),
937 argument97 => CHR(0),
938 argument98 => CHR(0),
939 argument99 => CHR(0),
940 argument100 => CHR(0)
941 );
942
943 --
944 -- If request submission failed, exit with error.
945 --
946 IF (l_req_id = 0) THEN
947
948 errbuf := fnd_message.get;
949 retcode := 2;
950 RETURN;
951
952 END IF;
953
954 -- Set start ID value
955 -- l_start_id := l_end_id + 1; -- @@
956 l_rows_per_worker1:=l_rows_per_worker+l_rows_per_worker1;
957 begin
958 l_start_id:= l_id_list(l_rows_per_worker1-1);
959 exception
960 when no_data_found then
961 null;
962 end;
963
964 END LOOP;-------I
965
966
967 END LOOP; ---First loop End -- @ @
968 --
969 -- After submitting request for all workers, put the parent
970 -- in paused state. When all children are done, the parent
971 -- would be called again, and then it will terminate
972 --
973 fnd_conc_global.set_req_globals
974 (
975 conc_status => 'PAUSED',
976 request_data => to_char(l_req_id) --,
977 -- conc_restart_time => to_char(sysdate),
978 -- release_sub_request => 'N'
979 );
980
981
982 ELSE
983
984 -- Log
985 IF (p_debug_flag = 'Y' AND
986 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
988 'Customers: Re-entering:' || 'p_num_workers=' || p_num_workers ||
989 ',p_commit_flag=' || p_commit_flag ||
990 ',p_debug_flag='||p_debug_flag);
991
992 END IF;
993 END IF;
994 END;
995 EXCEPTION
996
997 WHEN OTHERS THEN
998
999 ROLLBACK;
1000
1001 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1002
1003 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1004 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1005 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1006 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1007 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
1008 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1009 l_api_name||':'||sqlcode||':'||sqlerrm);
1010 END IF;
1011
1012 END Mig_Dup_SalesRep_Main;
1013
1014 --Procedure modified for ASN migration approach suggested by lester -- End -- @@
1015 --Procedure modified for ASN migration approach suggested by lester -- Start -- @@
1016
1017 PROCEDURE Mig_Dup_SalesRep_Cust (
1018 errbuf OUT NOCOPY VARCHAR2,
1019 retcode OUT NOCOPY NUMBER,
1020 p_start_id IN VARCHAR2,
1021 p_end_id IN VARCHAR2,
1022 p_commit_flag IN VARCHAR2,
1023 p_batch_size IN NUMBER,
1024 p_debug_flag IN VARCHAR2
1025 )
1026 IS
1027
1028 l_module_name CONSTANT VARCHAR2(256) :=
1029 'asn.plsql.asn_mig_sales_team_pvt.Mig_Dup_SalesRep_Cust';
1030
1031 l_uncommitted_parties NUMBER := 0;
1032 l_remove_count NUMBER;
1033 l_updated_flag BOOLEAN;
1034 l_party_id NUMBER;
1035 l_access_id NUMBER;
1036 l_user_id NUMBER;
1037
1038 /* CURSOR c_parties_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
1039 SELECT party_id
1040 FROM hz_parties
1041 WHERE party_id BETWEEN p_start_id AND p_end_id
1042 AND party_type IN ('ORGANIZATION', 'PERSON');*/
1043
1044 CURSOR c_parties_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
1045 SELECT distinct temp.customer_id
1046 FROM AS_ACCESSES_ALL_cust_TEMP temp ,hz_parties hz
1047 WHERE temp.customer_id BETWEEN p_start_id AND p_end_id
1048 AND hz.party_type IN ('ORGANIZATION', 'PERSON')
1049 AND temp.customer_id=hz.party_id ;
1050
1051 /* CURSOR c_uniq_steam(p_party_id NUMBER) IS
1052 SELECT max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
1053 salesforce_id, sales_group_id, partner_customer_id,
1054 partner_cont_party_id,
1055 max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
1056 max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
1057 max(nvl(OWNER_FLAG, 'N')) owner_flag,
1058 max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
1059 FROM AS_ACCESSES_ALL
1060 WHERE customer_id = p_party_id AND lead_id IS NULL AND sales_lead_id IS NULL
1061 GROUP BY salesforce_id, sales_group_id, partner_customer_id,
1062 partner_cont_party_id
1063 HAVING count(access_id) > 1; */ -- @@
1064
1065 --Code added for ASN migration approach suggested by lester -- Start
1066 --CURSOR c_uniq_steam IS
1067 CURSOR c_uniq_steam(p_party_id NUMBER) IS
1068 SELECT customer_id,max(code_access_id) code_access_id,
1069 salesforce_id, sales_group_id, partner_customer_id,
1070 partner_cont_party_id,
1071 max(FREEZE_FLAG) freeze_flag,
1072 max(TEAM_LEADER_FLAG) team_leader_flag,
1073 max(OWNER_FLAG) owner_flag,
1074 max(CONTRIBUTOR_FLAG) contributor_flag
1075 FROM AS_ACCESSES_ALL_CUST_TEMP -- AS_ACCESSES_ALL
1076 WHERE customer_id = p_party_id
1077 GROUP BY customer_id,salesforce_id, sales_group_id, partner_customer_id,
1078 partner_cont_party_id;
1079 --Code added for ASN migration approach suggested by lester -- End
1080
1081 BEGIN
1082
1083 -- Log
1084 IF (p_debug_flag = 'Y' AND
1085 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1087 'Begin OSO->ASN Duplicate Sales Rep Customer Data Migration.');
1088 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1089 'Start:' || 'p_start_id=' || p_start_id ||
1090 ',p_end_id='||p_end_id ||
1091 ',p_debug_flag='||p_debug_flag);
1092 END IF;
1093
1094 l_user_id := FND_GLOBAL.user_id;
1095
1096 IF l_user_id IS NULL THEN
1097 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1098 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
1099 'Customers: Error: Global User Id is not set');
1100 END IF;
1101 RETURN;
1102 END IF;
1103
1104 FOR party_rec in c_parties_in_range(p_start_id, p_end_id) LOOP -- start Main party loop -- @@
1105 BEGIN
1106 savepoint CURR_PARTY;
1107
1108 l_updated_flag := false;
1109
1110 --l_party_id := party_rec.party_id; -- @@
1111 l_party_id := party_rec.customer_id ; -- @@
1112
1113 l_remove_count := 0;
1114 -- Will return one of Sales Team members of duplicates grouped by
1115 -- Salesforce id, Sales Group id, union of flags (i.e flag is Y if
1116 -- any one of duplicate members has it as Y, else N), returns
1117 -- access_id of a record with SALESFORCE_ROLE_CODE non null
1118 -- if available else any access_id prefixed with Y or N
1119 FOR uniq_steam_rec in c_uniq_steam(l_party_id) LOOP -- @@
1120 IF (p_debug_flag = 'Y' AND
1121 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1122 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1123 'Customers: Cleaning up duplicates for coded_access_id: ' ||
1124 uniq_steam_rec.code_access_id ||
1125 ' sf_id ' || uniq_steam_rec.salesforce_id ||
1126 ' sg_id ' || uniq_steam_rec.sales_group_id ||
1127 ' freeze_flag ' || uniq_steam_rec.freeze_flag ||
1128 ' team_leader_flag ' || uniq_steam_rec.team_leader_flag ||
1129 ' owner_flag ' || uniq_steam_rec.owner_flag ||
1130 ' contributor_flag ' || uniq_steam_rec.contributor_flag);
1131 END IF;
1132
1133 l_updated_flag := true;
1134
1135 l_access_id := substr(uniq_steam_rec.code_access_id, 2);
1136 UPDATE AS_ACCESSES_ALL_ALL -- @@
1137 SET DELETE_FLAG = 'Y',
1138 LAST_UPDATED_BY = l_user_id,
1139 LAST_UPDATE_DATE = sysdate,
1140 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
1141 WHERE customer_id = l_party_id AND -- @@
1142 lead_id IS NULL AND
1143 sales_lead_id IS NULL AND
1144 salesforce_id = uniq_steam_rec.salesforce_id AND
1145 nvl(sales_group_id, -37) = nvl(uniq_steam_rec.sales_group_id, -37) AND
1146 nvl(partner_customer_id, -37) = nvl(uniq_steam_rec.partner_customer_id, -37) AND
1147 nvl(partner_cont_party_id, -37) = nvl(uniq_steam_rec.partner_cont_party_id, -37) AND
1148 access_id <> l_access_id AND
1149 delete_flag IS NULL;
1150
1151 l_remove_count := l_remove_count + SQL%ROWCOUNT;
1152
1153 UPDATE AS_ACCESSES_ALL -- @@
1154 SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
1155 TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
1156 OWNER_FLAG = uniq_steam_rec.owner_flag,
1157 CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,
1158 LAST_UPDATED_BY = l_user_id,
1159 LAST_UPDATE_DATE = sysdate,
1160 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
1161 WHERE access_id = l_access_id;
1162 END LOOP;
1163
1164 IF (p_debug_flag = 'Y' AND
1165 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1167 'Successfully processed Party Id: ' || l_party_id || ' Entries removed ' || l_remove_count);
1168 END IF;
1169
1170 IF l_updated_flag THEN
1171 l_uncommitted_parties := l_uncommitted_parties + 1;
1172 END IF;
1173
1174 IF l_uncommitted_parties >= p_batch_size THEN
1175 IF p_commit_flag = 'Y' THEN
1176 IF (p_debug_flag = 'Y' AND
1177 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1178 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1179 'Calling Commit after processing ' || l_uncommitted_parties || ' Customers');
1180 END IF;
1181 COMMIT;
1182 ELSE
1183 ROLLBACK;
1184 END IF;
1185 l_uncommitted_parties := 0;
1186 END IF;
1187
1188 EXCEPTION
1189 WHEN OTHERS then
1190 Rollback to CURR_PARTY;
1191 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1192 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1193 'Error Processing Party Id : ' || l_party_id);
1194 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1195 'SQL Error Msg, party_id: ' || l_party_id || ': '
1196 || substr(SQLERRM, 1, 1950));
1197 END IF;
1198 END;
1199 END LOOP; -- end Main customer loop -- @@
1200
1201 -- Commit
1202 IF (p_commit_flag = 'Y') THEN
1203 -- Log
1204 IF (p_debug_flag = 'Y' AND
1205 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1206 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1207 'Customers: Committing');
1208 END IF;
1209 COMMIT;
1210 ELSE
1211 -- Log
1212 IF (p_debug_flag = 'Y' AND
1213 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1214 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1215 'Customers: Rolling back');
1216 END IF;
1217 ROLLBACK;
1218 END IF;
1219
1220 IF (p_debug_flag = 'Y' AND
1221 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1222 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'End of OSO->ASN Duplicate Sales Team Customer Data Migration.');
1223 END IF;
1224
1225 End Mig_Dup_SalesRep_Cust;
1226 --Procedure modified for ASN migration approach suggested by lester -- End -- @@
1227
1228 --Newly added for concurrent program ASN Post Upgrade Log and Customer Update -- Start
1229 PROCEDURE Mig_Customerid_Enddaylog_Main
1230 (
1231 errbuf OUT NOCOPY VARCHAR2,
1232 retcode OUT NOCOPY NUMBER,
1233 p_num_workers IN NUMBER,
1234 p_commit_flag IN VARCHAR2,
1235 p_debug_flag IN VARCHAR2
1236 )
1237 IS
1238 l_api_name CONSTANT VARCHAR2(30) :=
1239 'Mig_Customerid_Enddaylog_Main';
1240 l_module_name CONSTANT VARCHAR2(256) :=
1241 'asn.plsql.asn_mig_sales_team_pvt.Mig_Customerid_Enddaylog_Main';
1242 l_msg_count NUMBER;
1243 l_msg_data VARCHAR2(2000);
1244 l_req_id NUMBER;
1245 l_request_data VARCHAR2(30);
1246 l_max_num_rows NUMBER;
1247 l_rows_per_worker NUMBER;
1248 l_start_id NUMBER;
1249 l_end_id NUMBER;
1250 l_batch_size CONSTANT NUMBER := 10000;
1251
1252 -- CURSOR c1 IS SELECT as_leads_s.nextval FROM dual;
1253 CURSOR c1 IS SELECT count(lead_id) FROM as_leads_all;
1254
1255 BEGIN
1256
1257 --
1258 -- If this is first time parent is called, then split the rows
1259 -- among workers and put the parent in paused state
1260 --
1261 IF (fnd_conc_global.request_data IS NULL) THEN
1262
1263 -- Log
1264 IF (p_debug_flag = 'Y' AND
1265 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1266 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1267 'Opportunities: Start:' || 'p_num_workers=' || p_num_workers ||
1268 ',p_commit_flag=' || p_commit_flag ||
1269 ',p_debug_flag=' || p_debug_flag);
1270 END IF;
1271
1272 --
1273 -- Get maximum number of possible rows in as_leads_all
1274 --
1275 OPEN c1;
1276 FETCH c1 INTO l_max_num_rows;
1277 CLOSE c1;
1278
1279 --
1280 -- Compute row range to be assigned to each worker
1281 --
1282 l_rows_per_worker := ROUND(l_max_num_rows/p_num_workers) + 1;
1283
1284 --
1285 -- Assign rows to each worker
1286 --
1287
1288 -- Initialize start ID value
1289 l_start_id := 0;
1290 FOR i IN 1..p_num_workers LOOP
1291
1292 -- Initialize end ID value
1293 l_end_id := l_start_id + l_rows_per_worker;
1294
1295 -- Submit the request
1296 l_req_id :=
1297 fnd_request.submit_request
1298 (
1299 application => 'ASN',
1300 program => 'ASN_UPG_LOG_CUSTOMER_SUB_PRG',
1301 description => null,
1302 start_time => sysdate,
1303 sub_request => true,
1304 argument1 => l_start_id,
1305 argument2 => l_end_id,
1306 argument3 => p_commit_flag,
1307 argument4 => l_batch_size,
1308 argument5 => p_debug_flag,
1309 argument6 => CHR(0),
1310 argument7 => CHR(0),
1311 argument8 => CHR(0),
1312 argument9 => CHR(0),
1313 argument10 => CHR(0),
1314 argument11 => CHR(0),
1315 argument12 => CHR(0),
1316 argument13 => CHR(0),
1317 argument14 => CHR(0),
1318 argument15 => CHR(0),
1319 argument16 => CHR(0),
1320 argument17 => CHR(0),
1321 argument18 => CHR(0),
1322 argument19 => CHR(0),
1323 argument20 => CHR(0),
1324 argument21 => CHR(0),
1325 argument22 => CHR(0),
1326 argument23 => CHR(0),
1327 argument24 => CHR(0),
1328 argument25 => CHR(0),
1329 argument26 => CHR(0),
1330 argument27 => CHR(0),
1331 argument28 => CHR(0),
1332 argument29 => CHR(0),
1333 argument30 => CHR(0),
1334 argument31 => CHR(0),
1335 argument32 => CHR(0),
1336 argument33 => CHR(0),
1337 argument34 => CHR(0),
1338 argument35 => CHR(0),
1339 argument36 => CHR(0),
1340 argument37 => CHR(0),
1341 argument38 => CHR(0),
1342 argument39 => CHR(0),
1343 argument40 => CHR(0),
1344 argument41 => CHR(0),
1345 argument42 => CHR(0),
1346 argument43 => CHR(0),
1347 argument44 => CHR(0),
1348 argument45 => CHR(0),
1349 argument46 => CHR(0),
1350 argument47 => CHR(0),
1351 argument48 => CHR(0),
1352 argument49 => CHR(0),
1353 argument50 => CHR(0),
1354 argument51 => CHR(0),
1355 argument52 => CHR(0),
1356 argument53 => CHR(0),
1357 argument54 => CHR(0),
1358 argument55 => CHR(0),
1359 argument56 => CHR(0),
1360 argument57 => CHR(0),
1361 argument58 => CHR(0),
1362 argument59 => CHR(0),
1363 argument60 => CHR(0),
1364 argument61 => CHR(0),
1365 argument62 => CHR(0),
1366 argument63 => CHR(0),
1367 argument64 => CHR(0),
1368 argument65 => CHR(0),
1369 argument66 => CHR(0),
1370 argument67 => CHR(0),
1371 argument68 => CHR(0),
1372 argument69 => CHR(0),
1373 argument70 => CHR(0),
1374 argument71 => CHR(0),
1375 argument72 => CHR(0),
1376 argument73 => CHR(0),
1377 argument74 => CHR(0),
1378 argument75 => CHR(0),
1379 argument76 => CHR(0),
1380 argument77 => CHR(0),
1381 argument78 => CHR(0),
1382 argument79 => CHR(0),
1383 argument80 => CHR(0),
1384 argument81 => CHR(0),
1385 argument82 => CHR(0),
1386 argument83 => CHR(0),
1387 argument84 => CHR(0),
1388 argument85 => CHR(0),
1389 argument86 => CHR(0),
1390 argument87 => CHR(0),
1391 argument88 => CHR(0),
1392 argument89 => CHR(0),
1393 argument90 => CHR(0),
1394 argument91 => CHR(0),
1395 argument92 => CHR(0),
1396 argument93 => CHR(0),
1397 argument94 => CHR(0),
1398 argument95 => CHR(0),
1399 argument96 => CHR(0),
1400 argument97 => CHR(0),
1401 argument98 => CHR(0),
1402 argument99 => CHR(0),
1403 argument100 => CHR(0)
1404 );
1405
1406 --
1407 -- If request submission failed, exit with error.
1408 --
1409 IF (l_req_id = 0) THEN
1410
1411 errbuf := fnd_message.get;
1412 retcode := 2;
1413 RETURN;
1414
1415 END IF;
1416
1417 -- Set start ID value
1418 l_start_id := l_end_id + 1;
1419
1420 END LOOP; -- end i
1421
1422 --
1423 -- After submitting request for all workers, put the parent
1424 -- in paused state. When all children are done, the parent
1425 -- would be called again, and then it will terminate
1426 --
1427 fnd_conc_global.set_req_globals
1428 (
1429 conc_status => 'PAUSED',
1430 request_data => to_char(l_req_id) --,
1431 -- conc_restart_time => to_char(sysdate),
1432 -- release_sub_request => 'N'
1433 );
1434
1435 ELSE
1436
1437 -- Log
1438 IF (p_debug_flag = 'Y' AND
1439 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1440 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1441 'Opportunities: Re-entering:' || 'p_num_workers=' || p_num_workers ||
1442 ',p_commit_flag=' || p_commit_flag ||
1443 ',p_debug_flag='||p_debug_flag);
1444 END IF;
1445
1446 errbuf := 'Migration completed';
1447 retcode := 0;
1448
1449 -- Log
1450 IF (p_debug_flag = 'Y' AND
1451 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1452 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1453 'Opportunities: Done:' || 'p_num_workers=' || p_num_workers ||
1454 ',p_commit_flag=' || p_commit_flag ||
1455 ',p_debug_flag='||p_debug_flag);
1456 END IF;
1457
1458 END IF;
1459
1460 EXCEPTION
1461
1462 WHEN OTHERS THEN
1463 ROLLBACK;
1464
1465 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1466
1467 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1468 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1469 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1470 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1471 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
1472 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1473 l_api_name||':'||sqlcode||':'||sqlerrm);
1474 END IF;
1475
1476 END Mig_Customerid_Enddaylog_Main;
1477
1478
1479 PROCEDURE Mig_Customerid_Enddaylog_Sub (
1480 errbuf OUT NOCOPY VARCHAR2,
1481 retcode OUT NOCOPY NUMBER,
1482 p_start_id IN VARCHAR2,
1483 p_end_id IN VARCHAR2,
1484 p_commit_flag IN VARCHAR2,
1485 p_batch_size IN NUMBER,
1486 p_debug_flag IN VARCHAR2)
1487 IS
1488
1489 l_api_name CONSTANT VARCHAR2(30) :=
1490 'Mig_Customerid_Enddaylog_Sub';
1491 l_module_name CONSTANT VARCHAR2(256) :=
1492 'asn.plsql.asn_mig_sales_team_pvt.Mig_Customerid_Enddaylog_Sub';
1493
1494 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1495 TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1496 TYPE Var1Tab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1497
1498 l_log_ids NumTab;
1499 l_lead_line_ids NumTab;
1500 l_sales_credit_ids NumTab;
1501 l_last_update_dates DateTab;
1502 l_endday_log_flags Var1Tab;
1503
1504 l_future_date DATE := sysdate + 1000;
1505 l_prev_last_update_date DATE;
1506 l_last_update_date DATE;
1507 l_prev_lead_line_id NUMBER;
1508 l_prev_sales_credit_id NUMBER;
1509 l_endday_log_flag VARCHAR2(1);
1510 l_update_count NUMBER;
1511
1512
1513 -- CURSOR c_opps_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
1514 CURSOR c_opps_in_range IS
1515 SELECT lead_id, customer_id
1516 FROM as_leads_all;
1517
1518 CURSOR c_lead_line_logs(p_lead_id NUMBER) IS
1519 SELECT log_id, lead_line_id, last_update_date
1520 FROM as_lead_lines_log
1521 WHERE lead_id = p_lead_id
1522 ORDER BY lead_line_id ASC, last_update_date DESC, log_id DESC;
1523
1524 CURSOR c_sales_credits_logs(p_lead_id NUMBER) IS
1525 SELECT log_id, sales_credit_id, last_update_date
1526 FROM as_sales_credits_log
1527 WHERE lead_id = p_lead_id
1528 ORDER BY sales_credit_id ASC, last_update_date DESC, log_id DESC;
1529
1530 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1531 l_lead_id num_list;
1532 l_customer_id num_list;
1533 l_MAX_fetches NUMBER := 10000;
1534 l_user_id NUMBER;
1535 l_CUSTFIX_COUNT NUMBER;
1536 l_uncommitted_opps NUMBER := 0;
1537
1538 BEGIN
1539 l_custfix_count := 0;
1540 l_user_id := FND_GLOBAL.user_id;
1541 IF l_user_id IS NULL THEN
1542 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1543 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
1544 'Opportunities: Error: Global User Id is not set');
1545 END IF;
1546 RETURN;
1547 END IF;
1548 OPEN c_opps_in_range;
1549 LOOP
1550 BEGIN
1551 savepoint CURR_OPP_CUS;
1552 FETCH c_opps_in_range BULK COLLECT into l_lead_id, l_customer_id LIMIT l_MAX_fetches;
1553
1554 FOR I IN l_lead_id.first..l_lead_id.last LOOP
1555
1556
1557 IF (p_debug_flag = 'Y' AND
1558 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1559 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1560 'Start Lead Line Logs Log for lead_id=' || l_lead_id(i));
1561 END IF;
1562
1563 OPEN c_lead_line_logs(l_lead_id(i));
1564 FETCH c_lead_line_logs BULK COLLECT
1565 INTO l_log_ids, l_lead_line_ids, l_last_update_dates;
1566 CLOSE c_lead_line_logs;
1567
1568 IF (p_debug_flag = 'Y' AND
1569 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1570 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1571 'Num Lead Line Logs Logs=' || l_log_ids.COUNT);
1572 END IF;
1573 l_update_count := 0;
1574 IF l_log_ids.COUNT > 0 THEN
1575 l_prev_lead_line_id := -37;
1576 FOR i IN l_log_ids.FIRST..l_log_ids.LAST LOOP
1577 IF l_lead_line_ids(i) <> l_prev_lead_line_id THEN
1578 l_prev_lead_line_id := l_lead_line_ids(i);
1579 l_prev_last_update_date := l_future_date;
1580 END IF;
1581
1582 l_endday_log_flag := 'Y';
1583 l_last_update_date := trunc(l_last_update_dates(i));
1584 IF l_prev_last_update_date = l_last_update_date THEN
1585 l_endday_log_flag := 'N';
1586 END IF;
1587
1588 l_endday_log_flags(i) := l_endday_log_flag;
1589 l_prev_last_update_date := l_last_update_date;
1590 END LOOP;
1591 FORALL i IN l_log_ids.FIRST..l_log_ids.LAST
1592 UPDATE as_lead_lines_log -- @@
1593 SET endday_log_flag = l_endday_log_flags(i)
1594 WHERE log_id = l_log_ids(i) AND endday_log_flag IS NULL;
1595
1596 l_update_count := SQL%ROWCOUNT;
1597 END IF;
1598 IF (p_debug_flag = 'Y' AND
1599 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1601 'Num Lead Line Logs Updated=' || l_update_count);
1602 END IF;
1603
1604 OPEN c_sales_credits_logs(l_lead_id(i));
1605 FETCH c_sales_credits_logs BULK COLLECT
1606 INTO l_log_ids, l_sales_credit_ids, l_last_update_dates;
1607 CLOSE c_sales_credits_logs;
1608
1609 IF (p_debug_flag = 'Y' AND
1610 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1611 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1612 'Num Sales Credits Logs=' || l_log_ids.COUNT);
1613 END IF;
1614
1615 l_update_count := 0;
1616
1617 IF l_log_ids.COUNT > 0 THEN
1618 l_prev_sales_credit_id := -37;
1619 FOR i IN l_log_ids.FIRST..l_log_ids.LAST LOOP
1620 IF l_sales_credit_ids(i) <> l_prev_sales_credit_id THEN
1621 l_prev_sales_credit_id := l_sales_credit_ids(i);
1622 l_prev_last_update_date := l_future_date;
1623 END IF;
1624
1625 l_endday_log_flag := 'Y';
1626 l_last_update_date := trunc(l_last_update_dates(i));
1627 IF l_prev_last_update_date = l_last_update_date THEN
1628 l_endday_log_flag := 'N';
1629 END IF;
1630
1631 l_endday_log_flags(i) := l_endday_log_flag;
1632 l_prev_last_update_date := l_last_update_date;
1633 END LOOP;
1634
1635 FORALL i IN l_log_ids.FIRST..l_log_ids.LAST
1636 UPDATE as_sales_credits_log -- @@
1637 SET endday_log_flag = l_endday_log_flags(i)
1638 WHERE log_id = l_log_ids(i) AND endday_log_flag IS NULL;
1639
1640 l_update_count := SQL%ROWCOUNT;
1641 END IF;
1642
1643
1644 IF (p_debug_flag = 'Y' AND
1645 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1646 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1647 'End fixlog for Lead id: ' || l_lead_id(i) ||
1648 ' Num Sales Credits Updated=' || l_update_count);
1649 END IF;
1650
1651 l_uncommitted_opps := l_uncommitted_opps + 1;
1652
1653 END LOOP;
1654
1655 --Customer Id updation
1656 --Customer Id updation
1657 FORALL I IN l_lead_id.first..l_lead_id.last
1658 -- Fix Customer Id in Opp Sales Team
1659 UPDATE AS_ACCESSES_ALL
1660 SET CUSTOMER_ID = l_customer_id(i),
1661 LAST_UPDATED_BY = l_user_id,
1662 LAST_UPDATE_DATE = sysdate,
1663 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
1664 WHERE LEAD_ID = l_lead_id(i) AND
1665 nvl(CUSTOMER_ID, -37) <> l_customer_id(i);
1666 l_custfix_count := SQL%ROWCOUNT;
1667
1668 IF l_uncommitted_opps >= p_batch_size THEN
1669 IF p_commit_flag = 'Y' THEN
1670 IF (p_debug_flag = 'Y' AND
1671 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1672 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1673 'Calling Commit after processing ' || l_uncommitted_opps || ' Opportunities');
1674 END IF;
1675 COMMIT;
1676 ELSE
1677 ROLLBACK;
1678 END IF;
1679 l_uncommitted_opps := 0;
1680 END IF;
1681
1682
1683 END;
1684 END LOOP;
1685 CLOSE c_opps_in_range;
1686
1687 EXCEPTION
1688
1689 WHEN OTHERS THEN
1690 ROLLBACK;
1691
1692 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1693
1694 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1695 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1696 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1697 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1698 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
1699 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1700 l_api_name||':'||sqlcode||':'||sqlerrm);
1701 END IF;
1702
1703 END Mig_Customerid_Enddaylog_Sub;
1704 --Newly added for concurrent program ASN Post Upgrade Log and Customer Update -- End
1705
1706 END asn_mig_sales_team_pvt;