1 PACKAGE BODY CN_UPGRADE_UTL_PKG as
2 /* $Header: cnuputlb.pls 120.10 2010/10/16 00:59:38 rnagaraj ship $ */
3
4 FUNCTION get_start_date(p_period_id NUMBER,
5 p_org_id NUMBER) RETURN DATE IS
6
7 l_start_date DATE;
8
9 CURSOR l_start_date_csr IS
10
11 SELECT start_date
12 FROM cn_period_statuses_all
13 WHERE period_id = p_period_id
14 AND ((org_id = p_org_id) OR (org_id IS NULL AND p_org_id IS NULL));
15
16 BEGIN
17
18 IF p_period_id IS NULL THEN
19
20 l_start_date := NULL;
21
22 ELSE
23
24 OPEN l_start_date_csr;
25 FETCH l_start_date_csr INTO l_start_date;
26
27 CLOSE l_start_date_csr;
28
29 END IF;
30
31 RETURN l_start_date;
32
33 END get_start_date;
34
35 FUNCTION get_end_date(p_period_id NUMBER,
36 p_org_id NUMBER) RETURN DATE IS
37
38 l_end_date DATE;
39
40 CURSOR l_end_date_csr IS
41
42 SELECT end_date
43 FROM cn_period_statuses_all
44 WHERE period_id = p_period_id
45 AND ((org_id = p_org_id) OR (org_id IS NULL AND p_org_id IS NULL));
46
47 BEGIN
48
49 IF p_period_id IS NULL THEN
50
51 l_end_date := NULL;
52
53 ELSE
54
55 OPEN l_end_date_csr;
56 FETCH l_end_date_csr INTO l_end_date;
57
58 CLOSE l_end_date_csr;
59
60 END IF;
61
62 RETURN l_end_date;
63
64 END get_end_date;
65
66
67 --| ---------------------------------------------------------------------+
68 --| Function Name : is_release_11510
69 --| Desc : Check if current release is 11.5.10
70 --| Return 1 if current release is 11.5.10
71 --| Return 0 if current release is 10.7, 3i or 11.0, 11.5
72 --| Return -1 : not valid release
73 --| ---------------------------------------------------------------------+
74
75 FUNCTION is_release_11510 RETURN NUMBER IS
76 l_result NUMBER := -1;
77 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
78 CURSOR c1 IS
79 SELECT profile_option_value
80 from fnd_profile_option_values
81 where profile_option_id =
82 (select profile_option_id
83 from fnd_profile_options
84 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
85 and application_id = 283)
86 and level_id = 10001
87 and application_id = 283;
88
89 BEGIN
90
91 OPEN c1;
92 FETCH c1 INTO v_cn_upgrading_profile;
93
94 IF c1%NOTFOUND THEN
95 l_result := -1;
96 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
97 RETURN l_result;
98 END IF;
99
100 IF c1%ISOPEN THEN
101 CLOSE c1;
102 END IF;
103
104 IF v_cn_upgrading_profile = '107' OR
105 v_cn_upgrading_profile = '110' OR
106 v_cn_upgrading_profile = '3I' OR
107 v_cn_upgrading_profile = '115' THEN
108 l_result := 0;
109 ELSIF v_cn_upgrading_profile = '11510' THEN
110 l_result := 1;
111 ELSE
112 l_result := -1;
113 END IF;
114
115 RETURN l_result;
116
117 END is_release_11510;
118
119 --| ---------------------------------------------------------------------+
120 --| Function Name : is_release_115
121 --| Desc : Check if current release is 11.5
122 --| Return 1 if current release is 11.5
123 --| Return 0 if current release is 10.7, 3i or 11.0
124 --| Return -1 : not valid release
125 --| ---------------------------------------------------------------------+
126
127 FUNCTION is_release_115 RETURN NUMBER IS
128 l_result NUMBER := -1;
129 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
130 CURSOR c1 IS
131 SELECT profile_option_value
132 from fnd_profile_option_values
133 where profile_option_id =
134 (select profile_option_id
135 from fnd_profile_options
136 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
137 and application_id = 283)
138 and level_id = 10001
139 and application_id = 283;
140
141 BEGIN
142
143 OPEN c1;
144 FETCH c1 INTO v_cn_upgrading_profile;
145
146 IF c1%NOTFOUND THEN
147 l_result := -1;
148 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
149 RETURN l_result;
150 END IF;
151
152 IF c1%ISOPEN THEN
153 CLOSE c1;
154 END IF;
155
156 IF v_cn_upgrading_profile = '107' OR
157 v_cn_upgrading_profile = '110' OR
158 v_cn_upgrading_profile = '3I' THEN
159 l_result := 0;
160 ELSIF v_cn_upgrading_profile = '115' OR
161 v_cn_upgrading_profile = '11510' THEN
162 l_result := 1;
163 ELSE
164 l_result := -1;
165 END IF;
166
167 RETURN l_result;
168
169 END is_release_115;
170
171 --| ---------------------------------------------------------------------+
172 --| Function Name : is_release_107
173 --| Desc : Check if current release is 10.7
174 --| Return 1 if current release is 10.7
175 --| Return 0 if current release is 11.5,3i or 11.0
176 --| Return -1 : not valid release
177 --| ---------------------------------------------------------------------+
178
179 FUNCTION is_release_107 RETURN NUMBER IS
180 l_result NUMBER := -1;
181 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
182 CURSOR c1 IS
183 SELECT profile_option_value
184 from fnd_profile_option_values
185 where profile_option_id =
186 (select profile_option_id
187 from fnd_profile_options
188 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
189 and application_id = 283)
190 and level_id = 10001
191 and application_id = 283;
192
193 BEGIN
194
195 OPEN c1;
196 FETCH c1 INTO v_cn_upgrading_profile;
197
198 IF c1%NOTFOUND THEN
199 l_result := -1;
200 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
201 RETURN l_result;
202 END IF;
203
204 IF c1%ISOPEN THEN
205 CLOSE c1;
206 END IF;
207
208 IF v_cn_upgrading_profile = '110' OR
209 v_cn_upgrading_profile = '3I' OR
210 v_cn_upgrading_profile = '115' OR
211 v_cn_upgrading_profile = '11510' THEN
212 l_result := 0;
213 ELSIF v_cn_upgrading_profile = '107' THEN
214 l_result := 1;
215 ELSE
216 l_result := -1;
217 END IF;
218
219 RETURN l_result;
220
221 END is_release_107;
222
223 --| ---------------------------------------------------------------------+
224 --| Function Name : is_release_110
225 --| Desc : Check if current release is 11.0
226 --| Return 1 if current release is 11.0
227 --| Return 0 if current release is 10.7,3i or 11.0 or 11.5
228 --| Return -1 : not valid release
229 --| ---------------------------------------------------------------------+
230
231 FUNCTION is_release_110 RETURN NUMBER IS
232 l_result NUMBER := -1;
233 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
234 CURSOR c1 IS
235 SELECT profile_option_value
236 from fnd_profile_option_values
237 where profile_option_id =
238 (select profile_option_id
239 from fnd_profile_options
240 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
241 and application_id = 283)
242 and level_id = 10001
243 and application_id = 283;
244
245 BEGIN
246
247 OPEN c1;
248 FETCH c1 INTO v_cn_upgrading_profile;
249
250 IF c1%NOTFOUND THEN
251 l_result := -1;
252 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
253 RETURN l_result;
254 END IF;
255
256 IF c1%ISOPEN THEN
257 CLOSE c1;
258 END IF;
259
260 IF v_cn_upgrading_profile = '107' OR
261 v_cn_upgrading_profile = '3I' OR
262 v_cn_upgrading_profile = '115' OR
263 v_cn_upgrading_profile = '11510' THEN
264 l_result := 0;
265 ELSIF v_cn_upgrading_profile = '110' THEN
266 l_result := 1;
267 ELSE
268 l_result := -1;
269 END IF;
270
271 RETURN l_result;
272
273 END is_release_110;
274
275 --| ---------------------------------------------------------------------+
276 --| Function Name : is_release_3i
277 --| Desc : Check if current release is 3i
278 --| Return 1 if current release is 3i
279 --| Return 0 if current release is 10.7,11.0 or 11.5
280 --| Return -1 : not valid release
281 --| ---------------------------------------------------------------------+
282
283 FUNCTION is_release_3i RETURN NUMBER IS
284 l_result NUMBER := -1;
285 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
286 CURSOR c1 IS
287 SELECT profile_option_value
288 from fnd_profile_option_values
289 where profile_option_id =
290 (select profile_option_id
291 from fnd_profile_options
292 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
293 and application_id = 283)
294 and level_id = 10001
295 and application_id = 283;
296
297 BEGIN
298
299 OPEN c1;
300 FETCH c1 INTO v_cn_upgrading_profile;
301
302 IF c1%NOTFOUND THEN
303 l_result := -1;
304 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
305 RETURN l_result;
306 END IF;
307
308 IF c1%ISOPEN THEN
309 CLOSE c1;
310 END IF;
311
312 IF v_cn_upgrading_profile = '107' OR
313 v_cn_upgrading_profile = '110' OR
314 v_cn_upgrading_profile = '115' OR
315 v_cn_upgrading_profile = '11510' THEN
316 l_result := 0;
317 ELSIF v_cn_upgrading_profile = '3I' THEN
318 l_result := 1;
319 ELSE
320 l_result := -1;
321 END IF;
322
323 RETURN l_result;
324
325 END is_release_3i;
326
327
328 FUNCTION is_release_120 RETURN NUMBER IS
329 l_result NUMBER := -1;
330 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
331 CURSOR c1 IS
332 SELECT profile_option_value
333 from fnd_profile_option_values
334 where profile_option_id =
335 (select profile_option_id
336 from fnd_profile_options
337 where profile_option_name = 'CN_UPGRADING_FROM_RELEASE'
338 and application_id = 283)
339 and level_id = 10001
340 and application_id = 283;
341
342 BEGIN
343
344 OPEN c1;
345 FETCH c1 INTO v_cn_upgrading_profile;
346
347 IF c1%NOTFOUND THEN
348 l_result := -1;
349 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
350 RETURN l_result;
351 END IF;
352
353 IF c1%ISOPEN THEN
354 CLOSE c1;
355 END IF;
356
357 IF v_cn_upgrading_profile = '120'
358 THEN
359 l_result := 1;
360 ELSIF v_cn_upgrading_profile = '11510' THEN
361 l_result := 1;
362 ELSE
363 l_result := -1;
364 END IF;
365
366 RETURN l_result;
367 END is_release_120;
368
369
370 FUNCTION is_release_121 RETURN NUMBER IS
371 l_result NUMBER := -1;
372 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
373 CURSOR c1 IS
374 SELECT profile_option_value
375 from fnd_profile_option_values
376 where profile_option_id =
377 (select profile_option_id
378 from fnd_profile_options
379 where profile_option_name = 'CN_UPGRADING_FROM_RELEASE'
380 and application_id = 283)
381 and level_id = 10001
382 and application_id = 283;
383
384 BEGIN
385
386 OPEN c1;
387 FETCH c1 INTO v_cn_upgrading_profile;
388
389 IF c1%NOTFOUND THEN
390 l_result := -1;
391 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
392 RETURN l_result;
393 END IF;
394
395 IF c1%ISOPEN THEN
396 CLOSE c1;
397 END IF;
398
399 IF v_cn_upgrading_profile = '121'
400 THEN
401 l_result := 1;
402 ELSE
403 l_result := -1;
404 END IF;
405
406 RETURN l_result;
407 END is_release_121;
408
409
410
411
412 PROCEDURE CNCMAUPD_R1212 (
413 x_errbuf OUT NOCOPY VARCHAR2,
414 x_retcode OUT NOCOPY VARCHAR2,
415 p_batch_size IN NUMBER,
416 p_num_workers IN NUMBER,
417 p_worker_id IN NUMBER) IS
418
419 l_table_name VARCHAR2(30) := 'CN_COMM_LINES_API_ALL';
420 -- l_update_name VARCHAR2(30) := 'CNGSICNCMAUPD1212';
421 l_update_name VARCHAR2(30) := 'CNSCNUPD12.0.9';
422
423 l_product VARCHAR2(30) := 'CN' ;
424 l_status VARCHAR2(30);
425 l_industry VARCHAR2(30);
426 l_table_owner VARCHAR2(30);
427
428 l_worker_id NUMBER;
429 l_num_workers NUMBER ;
430 l_batch_size VARCHAR2(30) ;
431
432 l_start_rowid ROWID;
433 l_end_rowid ROWID;
434 l_rows_processed NUMBER;
435 l_total_rows NUMBER;
436
437 l_any_rows_to_process BOOLEAN;
438 l_retstatus BOOLEAN;
439
440
441 BEGIN
442
443 l_batch_size := p_batch_size;
444 l_num_workers := p_num_workers;
445 l_worker_id := p_worker_id;
446
447 fnd_file.put_line(FND_FILE.LOG, 'Updating Comm Lines API => NVL(adjust_status,NEW), NVL(PRESERVE_CREDIT_OVERRIDE_FLAG,N) to improve performance for SCA process');
448 -- get schema name of the table for ROWID range processing
449 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
450
451 if ((l_retstatus = FALSE) OR (l_table_owner is null))
452 then
453 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
454 end if;
455
456 fnd_file.put_line(FND_FILE.LOG, 'Batch size : '||l_batch_size);
457 fnd_file.put_line(FND_FILE.LOG, 'Number of Workers : '||l_Num_Workers);
458 fnd_file.put_line(FND_FILE.LOG, 'Worker Id : '||l_Worker_Id);
459
460 l_rows_processed := 0;
461 l_total_rows := 0;
462
463
464 /*
465 -- the APIs use a combination of TABLE_NAME and UPDATE_NAME to track an
466 -- update. The update should be a no-op on a rerun, provided the TABLE_NAME
467 -- and UPDATE_NAME do not change.
468 --
469 -- If you have modified the script for upgrade logic and you want the
470 -- the change.
471 -- convention followed for UPDATE_NAME - scriptname suffix with version
472 */
473
474
475 ad_parallel_updates_pkg.initialize_rowid_range(
476 ad_parallel_updates_pkg.ROWID_RANGE,
477 l_table_owner,
478 l_table_name,
479 l_update_name,
480 l_worker_id,
481 l_num_workers,
482 l_batch_size, 0);
483
484 ad_parallel_updates_pkg.get_rowid_range(
485 l_start_rowid,
486 l_END_rowid,
487 l_any_rows_to_process,
488 l_batch_size,
489 TRUE);
490
491 WHILE (l_any_rows_to_process = TRUE) LOOP
492
493 BEGIN
494
495 UPDATE /*+ ROWID (clp) */ cn_comm_lines_api_all clp
496 SET clp.preserve_credit_override_flag = NVL(clp.preserve_credit_override_flag,'N'),
497 clp.adjust_status = NVL(clp.adjust_status,'NEW')
498 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
499 AND ( CLP.PRESERVE_CREDIT_OVERRIDE_FLAG IS NULL
500 OR CLP.ADJUST_STATUS IS NULL)
501 AND EXISTS ( SELECT NULL
502 FROM cn_period_statuses_all status
503 WHERE status.org_id = clp.org_id
504 AND clp.processed_date BETWEEN status.start_date AND status.end_date
505 AND status.period_status = 'O'
506 );
507
508 l_rows_processed := SQL%ROWCOUNT;
509 l_total_rows := l_total_rows + l_rows_processed;
510
511 fnd_file.put_line(FND_FILE.LOG, l_rows_processed ||' rows updated ');
512
513 END;
514
515 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_END_rowid);
516
517 COMMIT;
518
519
520 -- get new range of rowids
521 ad_parallel_updates_pkg.get_rowid_range(
522 l_start_rowid,
523 l_end_rowid,
524 l_any_rows_to_process,
525 l_batch_size,
526 FALSE);
527
528 END LOOP;
529
530 fnd_file.put_line(FND_FILE.LOG, 'Total number of Comm Lines API rows that are updated with NVL(adjust_status,NEW); NVL(PRESERVE_CREDIT_OVERRIDE_FLAG,N) = '||l_total_rows);
531 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
532
533 EXCEPTION
534 WHEN OTHERS THEN
535 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
536 raise;
537
538 END CNCMAUPD_R1212;
539
540
541
542 PROCEDURE CNCMHUPD_R1212 (
543 x_errbuf OUT NOCOPY VARCHAR2,
544 x_retcode OUT NOCOPY VARCHAR2,
545 p_batch_size IN NUMBER,
546 p_num_workers IN NUMBER,
547 p_worker_id IN NUMBER) IS
548
549 l_table_name VARCHAR2(30) := 'CN_COMMISSION_HEADERS_ALL';
550 -- l_update_name VARCHAR2(30) := 'CNGSICNCMHUPD1212';
551 l_update_name VARCHAR2(30) := 'CNADSUPD12.0.9';
552
553 l_product VARCHAR2(30) := 'CN' ;
554 l_status VARCHAR2(30);
555 l_industry VARCHAR2(30);
556 l_table_owner VARCHAR2(30);
557
558 l_worker_id NUMBER;
559 l_num_workers NUMBER ;
560 l_batch_size VARCHAR2(30) ;
561
562 l_start_rowid ROWID;
563 l_end_rowid ROWID;
564 l_rows_processed NUMBER;
565 l_total_rows NUMBER;
566
567
568 l_any_rows_to_process BOOLEAN;
569 l_retstatus BOOLEAN;
570
571
572 BEGIN
573
574 l_batch_size := p_batch_size;
575 l_num_workers := p_num_workers;
576 l_worker_id := p_worker_id;
577
578 fnd_file.put_line(FND_FILE.LOG, 'Updating Commission Headers => NVL(adjust_status,NEW) to improve performance for SCA process');
579 -- get schema name of the table for ROWID range processing
580 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
581
582 if ((l_retstatus = FALSE) OR (l_table_owner is null))
583 then
584 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
585 end if;
586
587 fnd_file.put_line(FND_FILE.LOG, 'Batch size : '||l_batch_size);
588 fnd_file.put_line(FND_FILE.LOG, 'Number of Workers : '||l_Num_Workers);
589 fnd_file.put_line(FND_FILE.LOG, 'Worker Id : '||l_Worker_Id);
590
591 l_rows_processed := 0;
592 l_total_rows := 0;
593
594 /*
595 -- the APIs use a combination of TABLE_NAME and UPDATE_NAME to track an
596 -- update. The update should be a no-op on a rerun, provided the TABLE_NAME
597 -- and UPDATE_NAME do not change.
598 --
599 -- If you have modified the script for upgrade logic and you want the
600 -- the change.
601 -- convention followed for UPDATE_NAME - scriptname suffix with version
602 */
603
604 ad_parallel_updates_pkg.initialize_rowid_range(
605 ad_parallel_updates_pkg.ROWID_RANGE,
606 l_table_owner,
607 l_table_name,
608 l_update_name,
609 l_worker_id,
610 l_num_workers,
611 l_batch_size, 0);
612
613 ad_parallel_updates_pkg.get_rowid_range(
614 l_start_rowid,
615 l_END_rowid,
616 l_any_rows_to_process,
617 l_batch_size,
618 TRUE);
619
620 WHILE (l_any_rows_to_process = TRUE) LOOP
621
622 BEGIN
623
624 UPDATE /*+ ROWID (cha) */ CN_COMMISSION_HEADERS_ALL cha
625 SET cha.adjust_status = NVL(cha.adjust_status,'NEW')
626 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
627 AND cha.adjust_status IS NULL
628 AND EXISTS (SELECT NULL
629 FROM cn_period_statuses_all status
630 WHERE status.org_id = cha.org_id
631 AND cha.processed_date BETWEEN status.start_date AND status.end_date
632 AND status.period_status = 'O'
633 );
634
635 l_rows_processed := SQL%ROWCOUNT;
636 l_total_rows := l_total_rows + l_rows_processed;
637
638 fnd_file.put_line(FND_FILE.LOG, l_rows_processed ||' rows updated ');
639
640 END;
641
642 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_END_rowid);
643
644 COMMIT;
645
646
647 -- get new range of rowids
648 ad_parallel_updates_pkg.get_rowid_range(
649 l_start_rowid,
650 l_end_rowid,
651 l_any_rows_to_process,
652 l_batch_size,
653 FALSE);
654
655 END LOOP;
656
657 fnd_file.put_line(FND_FILE.LOG, 'Total number of Commission Headers rows that are updated with NVL(adjust_status,NEW) = '||l_total_rows);
658 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
659
660 EXCEPTION
661 WHEN OTHERS THEN
662 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
663 raise;
664
665 END CNCMHUPD_R1212;
666
667
668
669 PROCEDURE CNSRPTXN_R1212 (
670 x_errbuf OUT NOCOPY VARCHAR2,
671 x_retcode OUT NOCOPY VARCHAR2,
672 p_batch_size IN NUMBER,
673 p_num_workers IN NUMBER,
674 p_worker_id IN NUMBER) IS
675
676 l_table_name VARCHAR2(30) := 'CN_SRP_PERIOD_QUOTAS_ALL';
677 l_update_name VARCHAR2(30) := 'CNSRPTXN';
678
679 l_product VARCHAR2(30) := 'CN' ;
680 l_status VARCHAR2(30);
681 l_industry VARCHAR2(30);
682 l_table_owner VARCHAR2(30);
683
684 l_worker_id NUMBER;
685 l_num_workers NUMBER ;
686 l_batch_size VARCHAR2(30) ;
687
688 l_start_rowid ROWID;
689 l_end_rowid ROWID;
690 l_rows_processed NUMBER;
691 l_total_rows NUMBER;
692
693 l_any_rows_to_process BOOLEAN;
694 l_retstatus BOOLEAN;
695
696 CURSOR get_rel IS
697 SELECT release_name
698 FROM FND_PRODUCT_GROUPS;
699
700
701 BEGIN
702
703 l_batch_size := p_batch_size;
704 l_num_workers := p_num_workers;
705 l_worker_id := p_worker_id;
706
707 fnd_file.put_line(FND_FILE.LOG, 'Updating CN_SRP_PERIOD_QUOTAS_ALL to populate transaction_amount_ptd ');
708 -- get schema name of the table for ROWID range processing
709 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
710
711 if ((l_retstatus = FALSE) OR (l_table_owner is null))
712 then
713 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
714 end if;
715
716 fnd_file.put_line(FND_FILE.LOG, 'Batch size : '||l_batch_size);
717 fnd_file.put_line(FND_FILE.LOG, 'Number of Workers : '||l_Num_Workers);
718 fnd_file.put_line(FND_FILE.LOG, 'Worker Id : '||l_Worker_Id);
719
720 l_rows_processed := 0;
721 l_total_rows := 0;
722
723 l_update_name := 'CNSRPTXN';
724
725 /*
726 This code was added for re-runability; if the CP CN_R1212_CNSRPTXN was cancelled during
727 the mulitple upgrade phase from R12.0 to R12.1.1 and then to R12.1.3 -- bug 10149831
728
729 FOR relid IN get_rel LOOP
730 IF ( ( relid.release_name = '12.1.2' ) OR ( relid.release_name = '12.1.3' )) THEN
731 l_update_name := 'CNSRPTXN_2_3';
732 ELSE
733 l_update_name := 'CNSRPTXN';
734 END IF;
735 END LOOP;
736 */
737
738
739 /*
740 -- the APIs use a combination of TABLE_NAME and UPDATE_NAME to track an
741 -- update. The update should be a no-op on a rerun, provided the TABLE_NAME
742 -- and UPDATE_NAME do not change.
743 --
744 -- If you have modified the script for upgrade logic and you want the
745 -- the change.
746 -- convention followed for UPDATE_NAME - scriptname suffix with version
747 */
748
749 ad_parallel_updates_pkg.initialize_rowid_range(
750 ad_parallel_updates_pkg.ROWID_RANGE,
751 l_table_owner,
752 l_table_name,
753 l_update_name,
754 l_worker_id,
755 l_num_workers,
756 l_batch_size, 0);
757
758 ad_parallel_updates_pkg.get_rowid_range(
759 l_start_rowid,
760 l_END_rowid,
761 l_any_rows_to_process,
762 l_batch_size,
763 TRUE);
764
765
766 /* For bug 10149831 - only OPEN periods records are filtered and updated with this dml
767 There should be another script which takes care of the CLOSED periods - this has to be
768 performed to ensure closed period when opened again has correct reference to the
769 transaction_amount_ptd
770 */
771
772 WHILE (l_any_rows_to_process = TRUE) LOOP
773
774 BEGIN
775
776 UPDATE /*+ ROWID(srp) */ cn_srp_period_quotas_all srp
777 SET srp.transaction_amount_ptd = (
778 SELECT NVL(SUM(ch.transaction_amount), 0)
779 FROM cn_commission_lines_all cl,
780 cn_commission_headers_all ch
781 WHERE cl.credited_salesrep_id = srp.salesrep_id
782 AND cl.processed_period_id = srp.period_id
783 AND cl.quota_id = srp.quota_id
784 AND cl.srp_plan_assign_id = srp.srp_plan_assign_id
785 AND cl.status = 'CALC'
786 AND cl.commission_header_id = ch.commission_header_id)
787 WHERE srp.ROWID BETWEEN l_start_rowid AND l_end_rowid
788 AND EXISTS ( SELECT null
789 FROM CN_PERIOD_STATUSES_ALL psa
790 WHERE psa.period_status = 'O'
791 AND psa.period_id = srp.period_id
792 AND psa.org_id = srp.org_id);
793
794
795 l_rows_processed := SQL%ROWCOUNT;
796 l_total_rows := l_total_rows + l_rows_processed;
797
798 fnd_file.put_line(FND_FILE.LOG, l_rows_processed ||' rows updated ');
799
800 END;
801
802 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_END_rowid);
803
804 COMMIT;
805
806
807 -- get new range of rowids
808 ad_parallel_updates_pkg.get_rowid_range(
809 l_start_rowid,
810 l_end_rowid,
811 l_any_rows_to_process,
812 l_batch_size,
813 FALSE);
814
815 END LOOP;
816
817 fnd_file.put_line(FND_FILE.LOG, 'Total number of SRP Period Quotas rows that are updated to populate transaction_amount_ptd = '||l_total_rows);
818 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
823 raise;
824
825 END CNSRPTXN_R1212;
826
827
828
829 END cn_upgrade_utl_pkg;
[Home] [Help]