[Home] [Help]
1: Package Body JTY_ASSIGN_BULK_PUB AS
2: /* $Header: jtfyaeab.pls 120.16.12010000.2 2008/10/10 10:51:46 rajukum ship $ */
3: ---------------------------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------
2: /* $Header: jtfyaeab.pls 120.16.12010000.2 2008/10/10 10:51:46 rajukum ship $ */
3: ---------------------------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------
6: -- PACKAGE NAME: JTY_ASSIGN_BULK_PUB
7: -- ---------------------------------------------------
8: -- PURPOSE
9: -- This package is a public API for getting winning territories
10: -- or territory resourcesi in bulk mode.
24: -- ***************************************************
25: -- GLOBAL VARIABLES and RECORD TYPE DEFINITIONS
26: -- ***************************************************
27:
28: G_PKG_NAME CONSTANT VARCHAR2(30):='JTY_ASSIGN_BULK_PUB';
29:
30: G_NEW_LINE VARCHAR2(02) := fnd_global.local_chr(10);
31: G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
32: G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
91:
92: -- debug message
93: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
94: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
95: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.begin',
96: 'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data');
97: END IF;
98:
99: x_return_status := FND_API.G_RET_STS_SUCCESS;
92: -- debug message
93: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
94: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
95: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.begin',
96: 'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data');
97: END IF;
98:
99: x_return_status := FND_API.G_RET_STS_SUCCESS;
100: l_sysdate := SYSDATE;
202:
203: -- debug message
204: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
205: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
206: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.final_sql',
207: substr('Insert satement : ' || l_final_sql, 1, 4000));
208: END IF;
209:
210: /* commit is executed to execute parallel dml in single transaction */
216:
217: -- debug message
218: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
219: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
220: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.num_rows',
221: 'Number of rows inserted : ' || SQL%ROWCOUNT);
222: END IF;
223:
224: -- debug message
223:
224: -- debug message
225: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
227: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.end',
228: 'End of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data');
229: END IF;
230:
231: EXCEPTION
224: -- debug message
225: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
227: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.end',
228: 'End of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data');
229: END IF;
230:
231: EXCEPTION
232: WHEN L_SEED_DATA_NOTFOUND THEN
235: x_msg_count := 1;
236: ERRBUF := x_msg_data;
237: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
238: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
239: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.l_seed_data_notfound',
240: x_msg_data);
241: END IF;
242:
243: WHEN L_SCHEMA_NOTFOUND THEN
242:
243: WHEN L_SCHEMA_NOTFOUND THEN
244: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245: RETCODE := 2;
246: x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_nm_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
247: ERRBUF := x_msg_data;
248: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
250: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.l_schema_notfound',
246: x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_nm_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
247: ERRBUF := x_msg_data;
248: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
250: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.l_schema_notfound',
251: x_msg_data);
252: END IF;
253:
254: WHEN NO_DATA_FOUND THEN
257: x_msg_count := 1;
258: ERRBUF := x_msg_data;
259: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
260: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
261: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.no_data_found',
262: x_msg_data);
263: END IF;
264:
265: WHEN OTHERS THEN
268: x_msg_count := 1;
269: ERRBUF := x_msg_data;
270: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
271: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
272: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.other',
273: substr(x_msg_data, 1, 4000));
274: END IF;
275:
276: END Insert_NM_Trans_Data;
331:
332: -- debug message
333: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
334: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
335: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.begin',
336: 'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data');
337: END IF;
338:
339: x_return_status := FND_API.G_RET_STS_SUCCESS;
332: -- debug message
333: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
334: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
335: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.begin',
336: 'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data');
337: END IF;
338:
339: x_return_status := FND_API.G_RET_STS_SUCCESS;
340: l_sysdate := SYSDATE;
392:
393: -- debug message
394: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
395: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
396: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
397: 'TRANS table name : ' || l_trans_target);
398: /*
399: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
400: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
396: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
397: 'TRANS table name : ' || l_trans_target);
398: /*
399: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
400: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
401: ' Seeded SQL : ' || substr(l_seeded_sql, 1, 4000));
402: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
403: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
404: ' Where clause : ' || substr(p_where, 1, 4000));
399: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
400: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
401: ' Seeded SQL : ' || substr(l_seeded_sql, 1, 4000));
402: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
403: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
404: ' Where clause : ' || substr(p_where, 1, 4000));
405: */
406: END IF;
407:
482:
483: -- debug message
484: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
485: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
486: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.final_sql',
487: substr('Insert satement : ' || l_final_sql, 1, 4000));
488: END IF;
489:
490: /* commit is executed to execute parallel dml in single transaction */
497:
498: -- debug message
499: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
500: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
501: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.num_rows',
502: 'Number of rows inserted : ' || SQL%ROWCOUNT);
503: END IF;
504:
505: -- debug message
504:
505: -- debug message
506: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
507: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
508: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.end',
509: 'End of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data');
510: END IF;
511:
512: EXCEPTION
505: -- debug message
506: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
507: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
508: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.end',
509: 'End of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data');
510: END IF;
511:
512: EXCEPTION
513: WHEN L_INVALID_WORKERS THEN
512: EXCEPTION
513: WHEN L_INVALID_WORKERS THEN
514: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
515: RETCODE := 2;
516: x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: Invalid number of workers : Valid range from 1 - 10';
517: ERRBUF := x_msg_data;
518: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
520: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_invalid_workers',
516: x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: Invalid number of workers : Valid range from 1 - 10';
517: ERRBUF := x_msg_data;
518: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
520: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_invalid_workers',
521: x_msg_data);
522: END IF;
523:
524: WHEN L_SEED_DATA_NOTFOUND THEN
527: x_msg_count := 1;
528: ERRBUF := x_msg_data;
529: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
530: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
531: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_seed_data_notfound',
532: x_msg_data);
533: END IF;
534:
535: WHEN L_SCHEMA_NOTFOUND THEN
534:
535: WHEN L_SCHEMA_NOTFOUND THEN
536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537: RETCODE := 2;
538: x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
539: ERRBUF := x_msg_data;
540: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
541: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
542: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_schema_notfound',
538: x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
539: ERRBUF := x_msg_data;
540: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
541: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
542: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_schema_notfound',
543: x_msg_data);
544: END IF;
545:
546: WHEN NO_DATA_FOUND THEN
549: x_msg_count := 1;
550: ERRBUF := x_msg_data;
551: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
552: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
553: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.no_data_found',
554: x_msg_data);
555: END IF;
556:
557: WHEN OTHERS THEN
560: x_msg_count := 1;
561: ERRBUF := x_msg_data;
562: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
563: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
564: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.other',
565: substr(x_msg_data, 1, 4000));
566: END IF;
567:
568: END Insert_Trans_Data;
607:
608: -- debug message
609: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
611: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.begin',
612: 'Start of the procedure JTY_ASSIGN_BULK_PUB.clear_trans_data');
613: END IF;
614:
615: /* Get the temp tables names corresponding to the usage, transaction type and program name */
608: -- debug message
609: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
611: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.begin',
612: 'Start of the procedure JTY_ASSIGN_BULK_PUB.clear_trans_data');
613: END IF;
614:
615: /* Get the temp tables names corresponding to the usage, transaction type and program name */
616: SELECT
647:
648: -- debug message
649: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
650: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
651: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.temp_table_names',
652: 'TRANS table name : ' || l_trans_target || ' MATCH table name : ' || l_match_target ||
653: ' WINNER table name : ' || l_winner_target || ' L1 table name : ' || l_L1_target ||
654: ' L2 table name : ' || l_L2_target || ' L3 table name : ' || l_L3_target ||
655: ' L4 table name : ' || l_L4_target || ' L5 table name : ' || l_L5_target ||
661:
662: -- debug message
663: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
665: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.trans_clear',
666: 'Deleting data and dropping indexes from ' || l_trans_target);
667: END IF;
668:
669: /* Drop the indexes on the TRANS table */
675: -- debug message
676: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_trans_target;
677: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
678: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
679: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.trans_clear',
680: x_msg_data);
681: END IF;
682:
683: RAISE FND_API.G_EXC_ERROR;
692: -- debug message
693: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_trans_target;
694: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
695: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
696: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.trans_clear',
697: x_msg_data);
698: END IF;
699:
700: RAISE FND_API.G_EXC_ERROR;
707:
708: -- debug message
709: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
710: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
711: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.match_clear',
712: 'Deleting data and dropping indexes from ' || l_match_target);
713: END IF;
714:
715: /* Drop the indexes on the MATCH table */
722: -- debug message
723: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_match_target;
724: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
725: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
726: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.match_clear',
727: x_msg_data);
728: END IF;
729:
730: RAISE FND_API.G_EXC_ERROR;
739: -- debug message
740: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_match_target;
741: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
743: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.match_clear',
744: x_msg_data);
745: END IF;
746:
747: RAISE FND_API.G_EXC_ERROR;
754:
755: -- debug message
756: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
757: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
758: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.umatch_clear',
759: 'Deleting data and dropping indexes from ' || l_umatch_target);
760: END IF;
761:
762: /* Drop the indexes on the UMATCH table */
769: -- debug message
770: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_umatch_target;
771: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
773: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.umatch_clear',
774: x_msg_data);
775: END IF;
776:
777: RAISE FND_API.G_EXC_ERROR;
786: -- debug message
787: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_umatch_target;
788: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
789: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
790: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.umatch_clear',
791: x_msg_data);
792: END IF;
793:
794: RAISE FND_API.G_EXC_ERROR;
801:
802: -- debug message
803: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
804: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
805: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.winner_clear',
806: 'Deleting data and dropping indexes from ' || l_winner_target);
807: END IF;
808:
809: /* Drop the indexes on the WINNER table */
816: -- debug message
817: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_winner_target;
818: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
819: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
820: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.winner_clear',
821: x_msg_data);
822: END IF;
823:
824: RAISE FND_API.G_EXC_ERROR;
833: -- debug message
834: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_winner_target;
835: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
836: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
837: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.winner_clear',
838: x_msg_data);
839: END IF;
840:
841: RAISE FND_API.G_EXC_ERROR;
848:
849: -- debug message
850: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
851: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
852: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.uwinner_clear',
853: 'Deleting data and dropping indexes from ' || l_uwinner_target);
854: END IF;
855:
856: /* Drop the indexes on the UWINNER table */
863: -- debug message
864: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_uwinner_target;
865: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
866: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
867: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.uwinner_clear',
868: x_msg_data);
869: END IF;
870:
871: RAISE FND_API.G_EXC_ERROR;
880: -- debug message
881: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_uwinner_target;
882: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
883: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
884: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.uwinner_clear',
885: x_msg_data);
886: END IF;
887:
888: RAISE FND_API.G_EXC_ERROR;
895:
896: -- debug message
897: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
899: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l1_clear',
900: 'Deleting data and dropping indexes from ' || l_l1_target);
901: END IF;
902:
903: /* Drop the indexes on the L1 table */
910: -- debug message
911: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l1_target;
912: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
913: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
914: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l1_clear',
915: x_msg_data);
916: END IF;
917:
918: RAISE FND_API.G_EXC_ERROR;
927: -- debug message
928: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l1_target;
929: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
930: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
931: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l1_clear',
932: x_msg_data);
933: END IF;
934:
935: RAISE FND_API.G_EXC_ERROR;
942:
943: -- debug message
944: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
946: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l2_clear',
947: 'Deleting data and dropping indexes from ' || l_l2_target);
948: END IF;
949:
950: /* Drop the indexes on the L2 table */
957: -- debug message
958: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l2_target;
959: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
960: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
961: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l2_clear',
962: x_msg_data);
963: END IF;
964:
965: RAISE FND_API.G_EXC_ERROR;
974: -- debug message
975: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l2_target;
976: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
977: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
978: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l2_clear',
979: x_msg_data);
980: END IF;
981:
982: RAISE FND_API.G_EXC_ERROR;
989:
990: -- debug message
991: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
992: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
993: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l3_clear',
994: 'Deleting data and dropping indexes from ' || l_l3_target);
995: END IF;
996:
997: /* Drop the indexes on the L3 table */
1004: -- debug message
1005: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l3_target;
1006: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1007: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1008: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l3_clear',
1009: x_msg_data);
1010: END IF;
1011:
1012: RAISE FND_API.G_EXC_ERROR;
1021: -- debug message
1022: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l3_target;
1023: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1024: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1025: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l3_clear',
1026: x_msg_data);
1027: END IF;
1028:
1029: RAISE FND_API.G_EXC_ERROR;
1036:
1037: -- debug message
1038: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1039: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1040: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l4_clear',
1041: 'Deleting data and dropping indexes from ' || l_l4_target);
1042: END IF;
1043:
1044: /* Drop the indexes on the L4 table */
1051: -- debug message
1052: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l4_target;
1053: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1054: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1055: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l4_clear',
1056: x_msg_data);
1057: END IF;
1058:
1059: RAISE FND_API.G_EXC_ERROR;
1068: -- debug message
1069: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l4_target;
1070: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1071: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1072: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l4_clear',
1073: x_msg_data);
1074: END IF;
1075:
1076: RAISE FND_API.G_EXC_ERROR;
1083:
1084: -- debug message
1085: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1086: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1087: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l5_clear',
1088: 'Deleting data and dropping indexes from ' || l_l5_target);
1089: END IF;
1090:
1091: /* Drop the indexes on the L5 table */
1098: -- debug message
1099: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l5_target;
1100: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1101: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1102: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l5_clear',
1103: x_msg_data);
1104: END IF;
1105:
1106: RAISE FND_API.G_EXC_ERROR;
1115: -- debug message
1116: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l5_target;
1117: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1118: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1119: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l5_clear',
1120: x_msg_data);
1121: END IF;
1122:
1123: RAISE FND_API.G_EXC_ERROR;
1130:
1131: -- debug message
1132: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1133: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1134: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.wt_clear',
1135: 'Deleting data and dropping indexes from ' || l_wt_target);
1136: END IF;
1137:
1138: /* Drop the indexes on the WT table */
1145: -- debug message
1146: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_wt_target;
1147: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1149: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.wt_clear',
1150: x_msg_data);
1151: END IF;
1152:
1153: RAISE FND_API.G_EXC_ERROR;
1162: -- debug message
1163: x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_wt_target;
1164: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1165: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1166: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.wt_clear',
1167: x_msg_data);
1168: END IF;
1169:
1170: RAISE FND_API.G_EXC_ERROR;
1200:
1201: -- debug message
1202: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1204: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.end',
1205: 'End of the procedure JTY_ASSIGN_BULK_PUB.clear_trans_data');
1206: END IF;
1207:
1208: EXCEPTION
1201: -- debug message
1202: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1204: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.end',
1205: 'End of the procedure JTY_ASSIGN_BULK_PUB.clear_trans_data');
1206: END IF;
1207:
1208: EXCEPTION
1209: WHEN NO_DATA_FOUND THEN
1214: ' program name : ' || p_program_name;
1215: ERRBUF := x_msg_data;
1216: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1217: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1218: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Clear_Trans_Data.no_data_found',
1219: x_msg_data);
1220: END IF;
1221:
1222: WHEN FND_API.G_EXC_ERROR THEN
1224: x_msg_count := 1;
1225: ERRBUF := x_msg_data;
1226: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1227: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1228: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Clear_Trans_Data.g_exc_error',
1229: x_msg_data);
1230: END IF;
1231:
1232: WHEN OTHERS THEN
1235: x_msg_count := 1;
1236: ERRBUF := x_msg_data;
1237: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1238: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1239: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.other',
1240: substr(x_msg_data, 1, 4000));
1241: END IF;
1242:
1243: END Clear_Trans_Data;
1289:
1290: -- debug message
1291: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1292: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1293: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.begin',
1294: 'Start of the procedure JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup');
1295: END IF;
1296:
1297: x_return_status := FND_API.G_RET_STS_SUCCESS;
1290: -- debug message
1291: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1292: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1293: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.begin',
1294: 'Start of the procedure JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup');
1295: END IF;
1296:
1297: x_return_status := FND_API.G_RET_STS_SUCCESS;
1298: l_sysdate := SYSDATE;
1333:
1334: -- debug message
1335: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1336: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1337: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.temp_table_names',
1338: 'TRANS table name : ' || l_trans_target || ' MATCH table name : ' || l_match_target ||
1339: ' WINNER table name : ' || l_winner_target || ' L1 table name : ' || l_L1_target ||
1340: ' L2 table name : ' || l_L2_target || ' L3 table name : ' || l_L3_target ||
1341: ' L4 table name : ' || l_L4_target || ' L5 table name : ' || l_L5_target ||
1393:
1394: -- debug message
1395: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1396: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1397: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.num_of_terr',
1398: 'Number of valid territories with resources for this transaction : ' || num_of_terr);
1399: END IF;
1400:
1401: IF (num_of_terr = 0) THEN
1398: 'Number of valid territories with resources for this transaction : ' || num_of_terr);
1399: END IF;
1400:
1401: IF (num_of_terr = 0) THEN
1402: x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP: There are NO Active Territories with Active ' ||
1403: 'Resources existing for this Usage/Transaction combination, so no assignments ' ||
1404: 'can take place.';
1405:
1406: RAISE NO_TAE_DATA_FOUND;
1412:
1413: -- debug message
1414: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1415: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1416: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.num_of_trans',
1417: 'Number of valid transaction objects : ' || num_of_trans);
1418: END IF;
1419:
1420: IF (num_of_trans = 0) THEN
1417: 'Number of valid transaction objects : ' || num_of_trans);
1418: END IF;
1419:
1420: IF (num_of_trans = 0) THEN
1421: x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP : There are NO valid Transaction Objects to assign.';
1422: RAISE NO_TAE_DATA_FOUND;
1423: END IF;
1424:
1425: -- debug message
1424:
1425: -- debug message
1426: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1428: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_trans',
1429: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_trans_target);
1430: END IF;
1431:
1432: JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
1434: , p_percent => p_percent_analyzed
1435: , x_return_status => x_return_status );
1436:
1437: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1438: x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP: Call to ' ||
1439: 'JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' || l_trans_target;
1440: RAISE FND_API.G_EXC_ERROR;
1441: END IF;
1442:
1442:
1443: -- debug message
1444: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1445: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1446: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.decompose_terr_defns',
1447: 'Call to JTY_TAE_CONTROL_PVT.Decompose_Terr_Defns API begins for : ' || l_trans_target);
1448: END IF;
1449:
1450: JTY_TAE_CONTROL_PVT.Decompose_Terr_Defns
1464: errbuf => ERRBUF,
1465: retcode => RETCODE );
1466:
1467: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1468: x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP: Call to ' ||
1469: 'JTY_TAE_CONTROL_PVT.Decompose_Terr_Defns API has failed for ' || l_trans_target;
1470: RAISE FND_API.G_EXC_ERROR;
1471: END IF;
1472:
1472:
1473: -- debug message
1474: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1475: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1476: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_matches',
1477: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_match_target);
1478: END IF;
1479:
1480: /* Build Index on Matches table */
1494:
1495: -- debug message
1496: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1497: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1498: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_winners',
1499: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_winner_target);
1500: END IF;
1501:
1502: /* Build Index on Winners table */
1516:
1517: -- debug message
1518: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1519: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1520: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l1',
1521: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l1_target);
1522: END IF;
1523:
1524: /* Build Index on L1 table */
1538:
1539: -- debug message
1540: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1541: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1542: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l2',
1543: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l2_target);
1544: END IF;
1545:
1546: /* Build Index on L2 table */
1561:
1562: -- debug message
1563: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1564: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1565: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l3',
1566: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l3_target);
1567: END IF;
1568:
1569: /* Build Index on L3 table */
1583:
1584: -- debug message
1585: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1586: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1587: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l4',
1588: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l4_target);
1589: END IF;
1590:
1591: /* Build Index on L4 table */
1605:
1606: -- debug message
1607: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1608: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1609: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l5',
1610: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l5_target);
1611: END IF;
1612:
1613: /* Build Index on L5 table */
1627:
1628: -- debug message
1629: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1630: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1631: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_wt',
1632: 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_wt_target);
1633: END IF;
1634:
1635: /* Build Index on WT table */
1656:
1657: -- debug message
1658: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1659: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1660: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.end',
1661: 'End of the procedure JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup');
1662: END IF;
1663:
1664: EXCEPTION
1657: -- debug message
1658: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1659: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1660: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.end',
1661: 'End of the procedure JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup');
1662: END IF;
1663:
1664: EXCEPTION
1665: WHEN NO_TAE_DATA_FOUND THEN
1668: RETCODE := 0;
1669: ERRBUF := null;
1670: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1671: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1672: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.no_tae_data_found',
1673: x_msg_data);
1674: END IF;
1675:
1676: WHEN FND_API.G_EXC_ERROR THEN
1679: RETCODE := 2;
1680: ERRBUF := x_msg_data;
1681: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1682: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1683: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.g_exc_error',
1684: x_msg_data);
1685: END IF;
1686:
1687: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1690: RETCODE := 2;
1691: ERRBUF := x_msg_data;
1692: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1693: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1694: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.g_exc_unexpected_error',
1695: x_msg_data);
1696: END IF;
1697:
1698: WHEN OTHERS THEN
1702: RETCODE := 2;
1703: ERRBUF := x_msg_data;
1704: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1705: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1706: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.other',
1707: substr(x_msg_data, 1, 4000));
1708: END IF;
1709:
1710: END get_winners_parallel_setup;
1733:
1734: -- debug message
1735: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1736: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1737: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.start',
1738: 'Start of the procedure JTY_ASSIGN_BULK_PUB.Process_Level_Winners');
1739: END IF;
1740:
1741: x_return_status := FND_API.G_RET_STS_SUCCESS;
1734: -- debug message
1735: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1736: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1737: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.start',
1738: 'Start of the procedure JTY_ASSIGN_BULK_PUB.Process_Level_Winners');
1739: END IF;
1740:
1741: x_return_status := FND_API.G_RET_STS_SUCCESS;
1742:
2018:
2019: -- debug message
2020: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2021: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2022: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.num_rows',
2023: 'Number of rows inserted into ' || p_terr_LEVEL_target_tbl || ' : ' || SQL%ROWCOUNT);
2024: END IF;
2025:
2026: -- debug message
2025:
2026: -- debug message
2027: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2029: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.end',
2030: 'End of the procedure JTY_ASSIGN_BULK_PUB.Process_Level_Winners');
2031: END IF;
2032:
2033: EXCEPTION
2026: -- debug message
2027: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2029: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.end',
2030: 'End of the procedure JTY_ASSIGN_BULK_PUB.Process_Level_Winners');
2031: END IF;
2032:
2033: EXCEPTION
2034:
2035: WHEN FND_API.G_EXC_ERROR THEN
2036: x_return_status := FND_API.G_RET_STS_ERROR;
2037: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2038: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2039: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.other',
2040: 'JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed with FND_API.G_EXC_ERROR exception for ' ||
2041: 'UPPER LEVEL : ' || p_UPPER_LEVEL_FROM_ROOT || ' LOWER LEVEL : ' || p_LOWER_LEVEL_FROM_ROOT);
2042: END IF;
2043:
2036: x_return_status := FND_API.G_RET_STS_ERROR;
2037: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2038: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2039: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.other',
2040: 'JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed with FND_API.G_EXC_ERROR exception for ' ||
2041: 'UPPER LEVEL : ' || p_UPPER_LEVEL_FROM_ROOT || ' LOWER LEVEL : ' || p_LOWER_LEVEL_FROM_ROOT);
2042: END IF;
2043:
2044: WHEN OTHERS THEN
2044: WHEN OTHERS THEN
2045: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2046: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2047: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2048: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.other',
2049: substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
2050: END IF;
2051:
2052: END Process_Level_Winners;
2074:
2075: -- debug message
2076: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2077: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2078: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.start',
2079: 'Start of the procedure JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners');
2080: END IF;
2081:
2082: x_return_status := FND_API.G_RET_STS_SUCCESS;
2075: -- debug message
2076: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2077: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2078: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.start',
2079: 'Start of the procedure JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners');
2080: END IF;
2081:
2082: x_return_status := FND_API.G_RET_STS_SUCCESS;
2083:
2169:
2170: -- debug message
2171: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2172: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2173: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.num_rows',
2174: 'Number of rows inserted into ' || p_terr_LEVEL_target_tbl || ' : ' || SQL%ROWCOUNT);
2175: END IF;
2176:
2177: -- debug message
2176:
2177: -- debug message
2178: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2179: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2180: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.end',
2181: 'End of the procedure JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners');
2182: END IF;
2183:
2184: EXCEPTION
2177: -- debug message
2178: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2179: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2180: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.end',
2181: 'End of the procedure JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners');
2182: END IF;
2183:
2184: EXCEPTION
2185: WHEN OTHERS THEN
2185: WHEN OTHERS THEN
2186: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2187: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2188: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2189: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.other',
2190: substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
2191: END IF;
2192:
2193: END Process_Final_Level_Winners;
2250:
2251: -- debug message
2252: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2253: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2254: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.start',
2255: 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_nmc_match');
2256: END IF;
2257:
2258: x_return_status := FND_API.G_RET_STS_SUCCESS;
2251: -- debug message
2252: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2253: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2254: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.start',
2255: 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_nmc_match');
2256: END IF;
2257:
2258: x_return_status := FND_API.G_RET_STS_SUCCESS;
2259:
2349:
2350: -- debug message
2351: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2352: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2353: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.final_sql',
2354: substr('Insert satement : ' || l_final_sql, 1, 4000));
2355: END IF;
2356:
2357: /* Insert all the transaction objects into the TRANS table */
2378:
2379: -- debug message
2380: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2381: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2382: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.match',
2383: 'Number of records inserted for qualifier combination ' || jtf_csr.relation_product || ' : ' || SQL%ROWCOUNT);
2384: END IF;
2385: END LOOP;
2386:
2386:
2387: -- debug message
2388: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2389: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2390: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.end',
2391: 'End of the procedure JTY_ASSIGN_BULK_PUB.process_nmc_match');
2392: END IF;
2393:
2394: EXCEPTION
2387: -- debug message
2388: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2389: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2390: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.end',
2391: 'End of the procedure JTY_ASSIGN_BULK_PUB.process_nmc_match');
2392: END IF;
2393:
2394: EXCEPTION
2395: WHEN L_SCHEMA_NOTFOUND THEN
2399: x_msg_count := 1;
2400: ERRBUF := x_msg_data;
2401: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2402: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2403: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.l_schema_notfound',
2404: 'Schema name corresponding to the JTF application not found');
2405: END IF;
2406:
2407: WHEN NO_DATA_FOUND THEN
2410: x_msg_count := 1;
2411: ERRBUF := x_msg_data;
2412: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2413: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2414: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.no_data_found',
2415: x_msg_data);
2416: END IF;
2417:
2418: WHEN OTHERS THEN
2422: RETCODE := 2;
2423: ERRBUF := x_msg_data;
2424: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2425: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2426: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.other',
2427: substr(x_msg_data, 1, 4000));
2428: END IF;
2429:
2430: END process_nmc_match;
2465:
2466: -- debug message
2467: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2468: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2469: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.start',
2470: 'Start of the procedure JTY_ASSIGN_BULK_PUB.collect_trans_data');
2471: END IF;
2472:
2473: /* Standard call to check for call compatibility. */
2466: -- debug message
2467: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2468: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2469: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.start',
2470: 'Start of the procedure JTY_ASSIGN_BULK_PUB.collect_trans_data');
2471: END IF;
2472:
2473: /* Standard call to check for call compatibility. */
2474: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2489:
2490: -- debug message
2491: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2492: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2493: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.param_value',
2494: 'Source : ' || p_source_id || ' Trans : ' || p_trans_id || ' Program Name : ' || p_program_name ||
2495: ' Mode : ' || p_mode || ' Where clause : ' || p_where || ' Number of workers : ' || p_no_of_workers ||
2496: ' Percent Analyzed : ' || p_percent_analyzed);
2497: END IF;
2510: retcode => retcode);
2511:
2512: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2513: -- debug message
2514: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.clear_trans_data has failed';
2515: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2516: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2517: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.clear_trans_data',
2518: x_msg_data);
2513: -- debug message
2514: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.clear_trans_data has failed';
2515: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2516: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2517: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.clear_trans_data',
2518: x_msg_data);
2519: END IF;
2520:
2521: RAISE FND_API.G_EXC_ERROR;
2523:
2524: -- debug message
2525: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2526: FND_LOG.string(FND_LOG.LEVEL_EVENT,
2527: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_clear_trans_data',
2528: 'API clear_trans_data completed successfully');
2529: END IF;
2530:
2531: /* Insert the txn objects into TRANS table */
2544: retcode => retcode);
2545:
2546: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2547: -- debug message
2548: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_trans_data has failed';
2549: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2550: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2551: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_trans_data',
2552: x_msg_data);
2547: -- debug message
2548: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_trans_data has failed';
2549: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2550: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2551: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_trans_data',
2552: x_msg_data);
2553: END IF;
2554:
2555: RAISE FND_API.G_EXC_ERROR;
2557:
2558: -- debug message
2559: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2560: FND_LOG.string(FND_LOG.LEVEL_EVENT,
2561: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_insert_trans_data',
2562: 'API insert_trans_data completed successfully');
2563: END IF;
2564:
2565: IF (p_mode = 'INCREMENTAL') THEN
2578: retcode => retcode);
2579:
2580: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2581: -- debug message
2582: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.process_nmc_match has failed';
2583: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2584: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2585: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.process_nmc_match',
2586: x_msg_data);
2581: -- debug message
2582: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.process_nmc_match has failed';
2583: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2584: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2585: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.process_nmc_match',
2586: x_msg_data);
2587: END IF;
2588:
2589: RAISE FND_API.G_EXC_ERROR;
2591:
2592: -- debug message
2593: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594: FND_LOG.string(FND_LOG.LEVEL_EVENT,
2595: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_process_nmc_match',
2596: 'API process_nmc_match completed successfully');
2597: END IF;
2598:
2599: /* insert into NM_TRANS txn objs that are assigned to changed territories */
2609: retcode => retcode);
2610:
2611: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2612: -- debug message
2613: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_nm_trans_data has failed';
2614: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2615: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2616: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_nm_trans_data',
2617: x_msg_data);
2612: -- debug message
2613: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_nm_trans_data has failed';
2614: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2615: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2616: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_nm_trans_data',
2617: x_msg_data);
2618: END IF;
2619:
2620: RAISE FND_API.G_EXC_ERROR;
2622:
2623: -- debug message
2624: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2625: FND_LOG.string(FND_LOG.LEVEL_EVENT,
2626: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_insert_nm_trans_data',
2627: 'API insert_nm_trans_data completed successfully');
2628: END IF;
2629:
2630: END IF; /* end IF (p_mode = 'INCREMENTAL') */
2645: );
2646:
2647: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2648: -- debug message
2649: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup has failed';
2650: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2651: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2652: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.get_winners_parallel_setup',
2653: x_msg_data);
2648: -- debug message
2649: x_msg_data := 'API JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup has failed';
2650: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2651: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2652: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.get_winners_parallel_setup',
2653: x_msg_data);
2654: END IF;
2655:
2656: RAISE FND_API.G_EXC_ERROR;
2661:
2662: -- debug message
2663: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2664: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2665: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end',
2666: 'End of the procedure JTY_ASSIGN_BULK_PUB.collect_trans_data');
2667: END IF;
2668:
2669: EXCEPTION
2662: -- debug message
2663: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2664: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2665: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end',
2666: 'End of the procedure JTY_ASSIGN_BULK_PUB.collect_trans_data');
2667: END IF;
2668:
2669: EXCEPTION
2670: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2673: RETCODE := 2;
2674: ERRBUF := x_msg_data;
2675: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2676: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2677: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.g_exc_unexpected_error',
2678: x_msg_data);
2679: END IF;
2680:
2681: WHEN NO_DATA_FOUND THEN
2684: x_msg_count := 1;
2685: ERRBUF := x_msg_data;
2686: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2687: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2688: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.no_data_found',
2689: x_msg_data);
2690: END IF;
2691:
2692: WHEN FND_API.G_EXC_ERROR THEN
2694: x_msg_count := 1;
2695: ERRBUF := x_msg_data;
2696: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2697: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2698: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.g_exc_error',
2699: x_msg_data);
2700: END IF;
2701:
2702: WHEN OTHERS THEN
2706: RETCODE := 2;
2707: ERRBUF := x_msg_data;
2708: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2709: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2710: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.other',
2711: substr(x_msg_data, 1, 4000));
2712: END IF;
2713:
2714: END collect_trans_data;
2768:
2769: -- debug message
2770: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2771: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2772: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.start',
2773: 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_winner');
2774: END IF;
2775:
2776: x_return_status := FND_API.G_RET_STS_SUCCESS;
2769: -- debug message
2770: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2771: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2772: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.start',
2773: 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_winner');
2774: END IF;
2775:
2776: x_return_status := FND_API.G_RET_STS_SUCCESS;
2777: lp_sysdate := SYSDATE;
2948: COMMIT; -- after modifying table in parallel
2949:
2950: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2951: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2952: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.winner_num_row',
2953: 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
2954: END IF;
2955: EXCEPTION
2956: WHEN NO_DATA_FOUND THEN
2959:
2960: ELSE
2961: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2962: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2963: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.winning_process',
2964: 'Star of winning process');
2965: END IF;
2966:
2967: IF (FND_INSTALLATION.GET_APP_INFO('FND', l_status, l_industry, l_fnd_schema)) THEN
2982: EXECUTE IMMEDIATE l_dyn_str INTO l_max_terr USING p_worker_id;
2983:
2984: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2985: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2986: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_max_terr',
2987: 'Number of maximum territories for a matching combination for the worker_id : ' || p_worker_id || ' : ' || l_max_terr);
2988: END IF;
2989:
2990: IF ((l_max_terr IS NULL) OR (l_max_terr = 0)) THEN
3007: END;
3008:
3009: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3010: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3011: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_target',
3012: 'Table ' || l_mp_winner_target || ' successfully dropped');
3013: END IF;
3014:
3015: /* create a temp table that will contain all the transaction objects */
3033: ' group by trans_object_id, trans_detail_object_id, txn_date )';
3034:
3035: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3036: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3037: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_target',
3038: substr(l_dyn_str, 1, 4000));
3039: END IF;
3040:
3041: /* create the table in jtf schema */
3042: ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.create_table, l_dyn_str, l_mp_winner_target);
3043:
3044: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3045: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3046: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_target',
3047: 'Table ' || l_mp_winner_target || ' successfully created');
3048: END IF;
3049:
3050: /* create a temp table that will contain only the distinct */
3058: END;
3059:
3060: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3061: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3062: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target',
3063: 'Table ' || l_dmc_winner_target || ' successfully dropped');
3064: END IF;
3065:
3066: /* Form the create table statement */
3079: l_dyn_str := l_dyn_str || ' )';
3080:
3081: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3082: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3083: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target',
3084: substr(l_dyn_str, 1, 4000));
3085: END IF;
3086:
3087: /* Create the table in JTF schema */
3088: ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.create_table, l_dyn_str, l_dmc_winner_target);
3089:
3090: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3091: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3092: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target',
3093: 'Table ' || l_dmc_winner_target || ' successfully created');
3094: END IF;
3095:
3096: /* Insert into umatch table the unique combination of matching territories */
3169: l_dyn_str := l_dyn_str || ' )';
3170: */
3171: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3172: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3173: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_umatch',
3174: substr(l_dyn_str, 1, 4000));
3175: END IF;
3176:
3177: execute immediate l_dyn_str;
3177: execute immediate l_dyn_str;
3178:
3179: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3180: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3181: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_umatch',
3182: 'Data successfully inserted in umatch table');
3183: END IF;
3184:
3185: /* Create in index on l_dmc_winner_target */
3197: l_dyn_str := l_dyn_str || ' )';
3198:
3199: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3200: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3201: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target_index',
3202: substr(l_dyn_str, 1, 4000));
3203: END IF;
3204:
3205: /* Create the index in JTF schema */
3219: l_dyn_str := l_dyn_str || ' )';
3220:
3221: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3222: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3223: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_update',
3224: substr(l_dyn_str, 1, 4000));
3225: END IF;
3226:
3227: execute immediate l_dyn_str;
3227: execute immediate l_dyn_str;
3228:
3229: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3230: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3231: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_update',
3232: 'Link successfully updated in ' || l_dmc_winner_target || ' table');
3233: END IF;
3234:
3235: /* Process first level */
3248:
3249: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3250: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3251: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3252: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l1',
3253: x_msg_data);
3254: END IF;
3255: RAISE FND_API.G_EXC_ERROR;
3256: END IF;
3266: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3267: x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L1_target;
3268: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3269: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3270: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l1_analyze',
3271: x_msg_data);
3272: END IF;
3273: RAISE FND_API.G_EXC_ERROR;
3274: END IF;
3290:
3291: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3292: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3293: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3294: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l2',
3295: x_msg_data);
3296: END IF;
3297: RAISE FND_API.G_EXC_ERROR;
3298: END IF;
3308: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3309: x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L2_target;
3310: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3311: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3312: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l2_analyze',
3313: x_msg_data);
3314: END IF;
3315: RAISE FND_API.G_EXC_ERROR;
3316: END IF;
3332:
3333: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3334: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3335: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3336: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l3',
3337: x_msg_data);
3338: END IF;
3339: RAISE FND_API.G_EXC_ERROR;
3340: END IF;
3350: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3351: x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L3_target;
3352: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3353: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3354: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l3_analyze',
3355: x_msg_data);
3356: END IF;
3357: RAISE FND_API.G_EXC_ERROR;
3358: END IF;
3374:
3375: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3376: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3377: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3378: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l4',
3379: x_msg_data);
3380: END IF;
3381: RAISE FND_API.G_EXC_ERROR;
3382: END IF;
3392: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3393: x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L4_target;
3394: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3395: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3396: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l4_analyze',
3397: x_msg_data);
3398: END IF;
3399: RAISE FND_API.G_EXC_ERROR;
3400: END IF;
3416:
3417: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3418: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3419: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3420: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l5',
3421: x_msg_data);
3422: END IF;
3423: RAISE FND_API.G_EXC_ERROR;
3424: END IF;
3434: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3435: x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L5_target;
3436: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3437: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3438: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l5_analyze',
3439: x_msg_data);
3440: END IF;
3441: RAISE FND_API.G_EXC_ERROR;
3442: END IF;
3456:
3457: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3458: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3459: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3460: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.wt',
3461: x_msg_data);
3462: END IF;
3463: RAISE FND_API.G_EXC_ERROR;
3464: END IF;
3474: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3475: x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_wt_target;
3476: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3477: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3478: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.wt_analyze',
3479: x_msg_data);
3480: END IF;
3481: RAISE FND_API.G_EXC_ERROR;
3482: END IF;
3483: */
3484:
3485: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3486: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3487: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.begin_populate_winners',
3488: 'Start of populating the winner table');
3489: END IF;
3490:
3491: l_dyn_str :=
3650: , p_worker_id; /* :bv_worker_id */
3651:
3652: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3653: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3654: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner_num_row',
3655: 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
3656: END IF;
3657:
3658: COMMIT; -- after modifying table in parallel
3769: , p_trans_id;
3770:
3771: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3772: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3773: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner_num_row',
3774: 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
3775: END IF;
3776:
3777: COMMIT; -- after modifying table in parallel
3791: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3792: x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_winner_target;
3793: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3794: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3795: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner_analyze',
3796: x_msg_data);
3797: END IF;
3798: RAISE FND_API.G_EXC_ERROR;
3799: END IF;
3805:
3806: -- debug message
3807: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3808: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
3809: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.end',
3810: 'End of the procedure JTY_ASSIGN_BULK_PUB.process_winner');
3811: END IF;
3812:
3813: EXCEPTION
3806: -- debug message
3807: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3808: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
3809: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.end',
3810: 'End of the procedure JTY_ASSIGN_BULK_PUB.process_winner');
3811: END IF;
3812:
3813: EXCEPTION
3814: WHEN L_SCHEMA_NOTFOUND THEN
3818: x_msg_count := 1;
3819: ERRBUF := x_msg_data;
3820: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3821: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3822: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.l_schema_notfound',
3823: x_msg_data);
3824: END IF;
3825:
3826: WHEN NO_DATA_FOUND THEN
3829: x_msg_count := 1;
3830: ERRBUF := x_msg_data;
3831: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3832: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3833: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.no_data_found',
3834: x_msg_data);
3835: END IF;
3836:
3837: WHEN L_NO_MATCH_TERR THEN
3836:
3837: WHEN L_NO_MATCH_TERR THEN
3838: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3839: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3840: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.l_no_match_terr',
3841: x_msg_data);
3842: END IF;
3843:
3844: WHEN FND_API.G_EXC_ERROR THEN
3847: x_msg_count := 1;
3848: ERRBUF := x_msg_data;
3849: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3850: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3851: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.g_exc_error',
3852: x_msg_data);
3853: END IF;
3854:
3855: WHEN OTHERS THEN
3859: RETCODE := 2;
3860: ERRBUF := x_msg_data;
3861: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3862: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3863: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.other',
3864: substr(x_msg_data, 1, 4000));
3865: END IF;
3866:
3867: END process_winners;
3916:
3917: -- debug message
3918: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3919: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
3920: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.start',
3921: 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_match');
3922: END IF;
3923:
3924: x_return_status := FND_API.G_RET_STS_SUCCESS;
3917: -- debug message
3918: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3919: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
3920: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.start',
3921: 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_match');
3922: END IF;
3923:
3924: x_return_status := FND_API.G_RET_STS_SUCCESS;
3925: l_sysdate := SYSDATE;
3977:
3978: -- debug message
3979: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3980: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3981: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.dea_match',
3982: 'Number of records inserted for qualifier combination ' || jtf_csr.attr_relation_product || ' : ' || SQL%ROWCOUNT);
3983: END IF;
3984: END LOOP;
3985:
4005: g_appl_id, g_program_id, l_sysdate, p_worker_id;
4006: -- debug message
4007: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4008: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4009: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.match',
4010: 'Number of records inserted for qualifier combination ' || jtf_csr.relation_product || ' : ' || SQL%ROWCOUNT);
4011: END IF;
4012: END LOOP;
4013:
4020:
4021: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4022: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4023: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4024: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.analyze_match',
4025: 'JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for table : ' || l_match_target);
4026: END IF;
4027:
4028: RAISE FND_API.G_EXC_ERROR;
4034:
4035: -- debug message
4036: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4037: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
4038: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.end',
4039: 'End of the procedure JTY_ASSIGN_BULK_PUB.process_match');
4040: END IF;
4041:
4042: EXCEPTION
4035: -- debug message
4036: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4037: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
4038: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.end',
4039: 'End of the procedure JTY_ASSIGN_BULK_PUB.process_match');
4040: END IF;
4041:
4042: EXCEPTION
4043: WHEN NO_DATA_FOUND THEN
4046: x_msg_count := 1;
4047: ERRBUF := x_msg_data;
4048: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4049: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4050: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.no_data_found',
4051: x_msg_data);
4052: END IF;
4053:
4054: WHEN FND_API.G_EXC_ERROR THEN
4053:
4054: WHEN FND_API.G_EXC_ERROR THEN
4055: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4056: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4057: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.g_exc_error',
4058: x_msg_data);
4059: END IF;
4060:
4061: WHEN OTHERS THEN
4065: RETCODE := 2;
4066: ERRBUF := x_msg_data;
4067: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4068: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4069: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.other',
4070: substr(x_msg_data, 1, 4000));
4071: END IF;
4072:
4073: END process_match;
4105:
4106: -- debug message
4107: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4108: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
4109: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.start',
4110: 'Start of the procedure JTY_ASSIGN_BULK_PUB.get_winners');
4111: END IF;
4112:
4113: /* Standard call to check for call compatibility. */
4106: -- debug message
4107: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4108: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
4109: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.start',
4110: 'Start of the procedure JTY_ASSIGN_BULK_PUB.get_winners');
4111: END IF;
4112:
4113: /* Standard call to check for call compatibility. */
4114: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4128:
4129: -- debug message
4130: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4131: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4132: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.param_value',
4133: 'Source : ' || p_source_id || ' Trans : ' || p_trans_id || ' Program Name : ' || p_program_name ||
4134: ' Mode : ' || p_mode || ' Worker : ' || p_worker_id);
4135: END IF;
4136:
4151: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4152: -- debug message
4153: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4154: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4155: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.match',
4156: 'process_match API has failed for source : ' || p_source_id || ' trans : ' || p_trans_id ||
4157: ' program name : ' || p_program_name);
4158: END IF;
4159:
4162:
4163: -- debug message
4164: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4165: FND_LOG.string(FND_LOG.LEVEL_EVENT,
4166: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.end_match',
4167: 'Matching process completed successfully');
4168: END IF;
4169:
4170: /* Find out the winning territories corresponding to the txn objects */
4184: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4185: -- debug message
4186: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4187: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4188: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner',
4189: 'process_winners API has failed for source : ' || p_source_id || ' trans : ' || p_trans_id ||
4190: ' program name : ' || p_program_name);
4191: END IF;
4192:
4195:
4196: -- debug message
4197: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4198: FND_LOG.string(FND_LOG.LEVEL_EVENT,
4199: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.end_winner',
4200: 'Winner process completed successfully');
4201: END IF;
4202:
4203: -- debug message
4202:
4203: -- debug message
4204: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4205: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
4206: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.end',
4207: 'End of the procedure JTY_ASSIGN_BULK_PUB.get_winners');
4208: END IF;
4209:
4210: EXCEPTION
4203: -- debug message
4204: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4205: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
4206: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.end',
4207: 'End of the procedure JTY_ASSIGN_BULK_PUB.get_winners');
4208: END IF;
4209:
4210: EXCEPTION
4211: WHEN FND_API.G_EXC_ERROR THEN
4213: RETCODE := 2;
4214: x_msg_count := 1;
4215: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4216: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4217: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.g_exc_error',
4218: x_msg_data);
4219: END IF;
4220:
4221: WHEN OTHERS THEN
4225: RETCODE := 2;
4226: ERRBUF := x_msg_data;
4227: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4228: FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4229: 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.other',
4230: substr(x_msg_data, 1, 4000));
4231: END IF;
4232:
4233: END get_winners;
4231: END IF;
4232:
4233: END get_winners;
4234:
4235: END JTY_ASSIGN_BULK_PUB;