[Home] [Help]
PACKAGE BODY: APPS.JTY_ASSIGN_BULK_PUB
Source
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 -- ---------------------------------------------------
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.
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is publicly available for use
17 --
18 -- HISTORY
19 -- 06/13/2005 ACHANDA CREATED
20 --
21
22 -- End of Comments
23
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;
33 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
34 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
35 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
36 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
37
38 NO_TAE_DATA_FOUND EXCEPTION;
39
40 -- ***************************************************
41 -- API Body Definitions
42 -- ***************************************************
43
44 -- ***************************************************
45 -- API Specifications
46 -- ***************************************************
47 -- api name : insert_nm_trans_data
48 -- type : private.
49 -- function : inserts the transaction objects into NM_TRANS table
50 -- pre-reqs :
51 -- notes:
52 --
53 PROCEDURE Insert_NM_Trans_Data
54 ( p_source_id IN NUMBER,
55 p_trans_id IN NUMBER,
56 p_program_name IN VARCHAR2,
57 p_request_id IN NUMBER,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2,
61 ERRBUF OUT NOCOPY VARCHAR2,
62 RETCODE OUT NOCOPY VARCHAR2
63 )
64 AS
65 l_trans_target VARCHAR2(30);
66 l_insert_stmt VARCHAR2(3000);
67 l_select_stmt VARCHAR2(3000);
68 first_time BOOLEAN;
69 l_owner VARCHAR2(30);
70 l_indent VARCHAR2(30);
71 l_status VARCHAR2(30);
72 l_industry VARCHAR2(30);
73 l_seeded_sql VARCHAR2(32767);
74 l_final_sql VARCHAR2(32767);
75 l_sysdate DATE;
76
77
78 CURSOR c1(p_table_name IN VARCHAR2, p_owner IN VARCHAR2) is
79 SELECT column_name
80 FROM all_tab_columns
81 WHERE table_name = p_table_name
82 AND owner = p_owner
83 AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
84 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
85 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
86
87 L_SCHEMA_NOTFOUND EXCEPTION;
88 L_SEED_DATA_NOTFOUND EXCEPTION;
89
90 BEGIN
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;
100 l_sysdate := SYSDATE;
101
102 /* Get the NM_TRANS table name */
103 BEGIN
104 SELECT tup.batch_nm_trans_table_name
105 INTO l_trans_target
106 FROM jty_trans_usg_pgm_details tup
107 WHERE tup.source_id = p_source_id
108 AND tup.trans_type_id = p_trans_id
109 AND tup.program_name = p_program_name;
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
113 ' program name : ' || p_program_name;
114 RAISE;
115 END;
116
117 BEGIN
118 SELECT incr_reassign_sql
119 INTO l_seeded_sql
120 FROM jty_trans_usg_pgm_sql tus
121 WHERE tus.source_id = p_source_id
122 AND tus.trans_type_id = p_trans_id
123 AND tus.program_name = p_program_name
124 AND tus.enabled_flag = 'Y';
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 x_msg_data := 'No active row in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
128 ' program name : ' || p_program_name;
129 RAISE;
130 END;
131
132 IF (l_trans_target IS NULL) THEN
133 x_msg_data := 'No trans table name in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' ||
134 p_trans_id || ' program name : ' || p_program_name;
135 RAISE L_SEED_DATA_NOTFOUND;
136 END IF;
137
138 IF (l_seeded_sql IS NULL) THEN
139 x_msg_data := 'No active transaction sql in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' ||
140 p_trans_id || ' program name : ' || p_program_name;
141 RAISE L_SEED_DATA_NOTFOUND;
142 END IF;
143
144 /* Initialize local variables */
145 first_time := TRUE;
146 l_indent := ' ';
147
148 /* Get the schema name corresponding to JTF application */
149 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
150 NULL;
151 END IF;
152
153 IF (l_owner IS NULL) THEN
154 RAISE L_SCHEMA_NOTFOUND;
155 END IF;
156
157 /* Form the insert statement to insert transaction objects into TRANS table */
158 l_insert_stmt := 'INSERT /*+ APPEND PARALLEL(' || l_trans_target || ') */ INTO ' || l_trans_target || '(';
159 l_select_stmt := '(SELECT ';
160
161 FOR column_names in c1(l_trans_target, l_owner) LOOP
162 IF (first_time) THEN
163 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
164 l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
165 first_time := FALSE;
166 ELSE
167 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
168 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
169 END IF;
170 END LOOP;
171
172 /* Standard WHO columns */
173 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
174 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
175 g_new_line || l_indent || ',CREATION_DATE ' ||
176 g_new_line || l_indent || ',CREATED_BY ' ||
177 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
178 g_new_line || l_indent || ',REQUEST_ID ' ||
179 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
180 g_new_line || l_indent || ',PROGRAM_ID ' ||
181 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
182 g_new_line || l_indent || ',WORKER_ID ' ||
183 g_new_line || l_indent || ',TXN_DATE ' ||
184 g_new_line || ')';
185
186 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
187 g_new_line || l_indent || ',''' || g_user_id || '''' ||
188 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
189 g_new_line || l_indent || ',''' || g_user_id || '''' ||
190 g_new_line || l_indent || ',''' || g_login_id || '''' ||
191 g_new_line || l_indent || ',''' || p_request_id || '''' ||
192 g_new_line || l_indent || ',''' || g_appl_id || '''' ||
193 g_new_line || l_indent || ',''' || g_program_id || '''' ||
194 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
195 g_new_line || l_indent || ',1' ||
196 g_new_line || l_indent || ',''' || l_sysdate || '''';
197
198 l_final_sql := l_insert_stmt || l_select_stmt ||
199 g_new_line || 'FROM ( ' ||
200 g_new_line || l_seeded_sql ||
201 g_new_line || ' ) ) ';
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 */
211 commit;
212 EXECUTE IMMEDIATE 'alter session enable parallel dml';
213 EXECUTE IMMEDIATE l_final_sql;
214 commit;
215 EXECUTE IMMEDIATE 'alter session disable parallel dml';
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
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
233 x_return_status := FND_API.G_RET_STS_ERROR ;
234 RETCODE := 2;
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
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',
251 x_msg_data);
252 END IF;
253
254 WHEN NO_DATA_FOUND THEN
255 x_return_status := FND_API.G_RET_STS_ERROR ;
256 RETCODE := 2;
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
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267 x_msg_data := SQLCODE || ' : ' || SQLERRM;
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;
277
278
279 -- ***************************************************
280 -- API Specifications
281 -- ***************************************************
282 -- api name : insert_trans_data
283 -- type : private.
284 -- function : inserts the transaction objects into TRANS table
285 -- pre-reqs :
286 -- notes:
287 --
288 PROCEDURE Insert_Trans_Data
289 ( p_source_id IN NUMBER,
290 p_trans_id IN NUMBER,
291 p_program_name IN VARCHAR2,
292 p_mode IN VARCHAR2,
293 p_where IN VARCHAR2,
294 p_no_of_workers IN NUMBER,
295 p_request_id IN NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2,
299 ERRBUF OUT NOCOPY VARCHAR2,
300 RETCODE OUT NOCOPY VARCHAR2
301 )
302 AS
303 l_trans_target VARCHAR2(30);
304 l_insert_stmt VARCHAR2(3000);
305 l_select_stmt VARCHAR2(3000);
306 first_time BOOLEAN;
307 l_owner VARCHAR2(30);
308 l_indent VARCHAR2(30);
309 l_status VARCHAR2(30);
310 l_industry VARCHAR2(30);
311 l_seeded_sql VARCHAR2(32767);
312 l_final_sql VARCHAR2(32767);
313 l_plsql_block VARCHAR2(32767);
314 l_sysdate DATE;
315
316
320 WHERE table_name = p_table_name
317 CURSOR c1(p_table_name IN VARCHAR2, p_owner IN VARCHAR2) is
318 SELECT column_name
319 FROM all_tab_columns
321 AND owner = p_owner
322 AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
323 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
324 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
325
326 L_SCHEMA_NOTFOUND EXCEPTION;
327 L_SEED_DATA_NOTFOUND EXCEPTION;
328 L_INVALID_WORKERS EXCEPTION;
329
330 BEGIN
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;
340 l_sysdate := SYSDATE;
341
342 /* Number of workers is restricted from 1 to 10 */
343 IF ((p_no_of_workers > 10) OR (p_no_of_workers < 1)) THEN
344 RAISE L_INVALID_WORKERS;
345 END IF;
346
347 /* Get the TRANS table name and active transaction type batch SQL */
348 BEGIN
349 SELECT decode(p_mode, 'TOTAL', tup.batch_trans_table_name
350 , 'INCREMENTAL', tup.batch_nm_trans_table_name
351 , 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
352 INTO l_trans_target
353 FROM jty_trans_usg_pgm_details tup
354 WHERE tup.source_id = p_source_id
355 AND tup.trans_type_id = p_trans_id
356 AND tup.program_name = p_program_name;
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
360 ' program name : ' || p_program_name;
361 RAISE;
362 END;
363
364 BEGIN
365 SELECT decode(p_mode, 'TOTAL', tus.batch_total_sql
366 , 'INCREMENTAL', tus.batch_incr_sql
367 , 'DATE EFFECTIVE', decode(tus.use_total_for_dea_flag, 'Y', tus.batch_total_sql, tus.batch_dea_sql))
368 INTO l_seeded_sql
369 FROM jty_trans_usg_pgm_sql tus
370 WHERE tus.source_id = p_source_id
371 AND tus.trans_type_id = p_trans_id
372 AND tus.program_name = p_program_name
373 AND tus.enabled_flag = 'Y';
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 x_msg_data := 'No active row in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
377 ' program name : ' || p_program_name;
378 RAISE;
379 END;
380
381 IF (l_trans_target IS NULL) THEN
382 x_msg_data := 'No trans table name in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' ||
383 p_trans_id || ' program name : ' || p_program_name;
384 RAISE L_SEED_DATA_NOTFOUND;
385 END IF;
386
387 IF (l_seeded_sql IS NULL) THEN
388 x_msg_data := 'No active transaction sql in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' ||
389 p_trans_id || ' program name : ' || p_program_name;
390 RAISE L_SEED_DATA_NOTFOUND;
391 END IF;
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',
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
408 /* Initialize local variables */
409 first_time := TRUE;
410 l_indent := ' ';
411
412 /* Get the schema name corresponding to JTF application */
413 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
414 NULL;
415 END IF;
416
417 IF (l_owner IS NULL) THEN
418 RAISE L_SCHEMA_NOTFOUND;
419 END IF;
420
421 /* Form the insert statement to insert transaction objects into TRANS table */
422 l_insert_stmt := 'INSERT /*+ APPEND PARALLEL(' || l_trans_target || ') */ INTO ' || l_trans_target || '(';
423 l_select_stmt := '(SELECT /*+ PARALLEL */';
424
425 FOR column_names in c1(l_trans_target, l_owner) LOOP
426 IF (first_time) THEN
427 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
428 l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
429 first_time := FALSE;
430 ELSE
431 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
432 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
433 END IF;
434 END LOOP;
435
439 g_new_line || l_indent || ',CREATION_DATE ' ||
436 /* Standard WHO columns */
437 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
438 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
440 g_new_line || l_indent || ',CREATED_BY ' ||
441 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
442 g_new_line || l_indent || ',REQUEST_ID ' ||
443 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
444 g_new_line || l_indent || ',PROGRAM_ID ' ||
445 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
446 g_new_line || l_indent || ',WORKER_ID ' ||
447 g_new_line || l_indent || ',TXN_DATE ' ||
448 g_new_line || ')';
449
450 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
451 g_new_line || l_indent || ',''' || g_user_id || '''' ||
452 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
453 g_new_line || l_indent || ',''' || g_user_id || '''' ||
454 g_new_line || l_indent || ',''' || g_login_id || '''' ||
455 g_new_line || l_indent || ',''' || p_request_id || '''' ||
456 g_new_line || l_indent || ',''' || g_appl_id || '''' ||
457 g_new_line || l_indent || ',''' || g_program_id || '''' ||
458 g_new_line || l_indent || ',''' || l_sysdate || '''';
459
460 IF (p_mode = 'INCREMENTAL') THEN
461 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',1';
462 ELSE
463 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',mod(trans_object_id ,' || p_no_of_workers || ') + 1';
464 END IF;
465
466 IF (p_mode = 'DATE EFFECTIVE') THEN
467 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',txn_date';
468 ELSE
469 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''';
470 END IF;
471
472 l_final_sql := l_insert_stmt || l_select_stmt ||
473 g_new_line || 'FROM ( ' ||
474 g_new_line || l_seeded_sql ||
475 g_new_line || ' ) ';
476
477 /* Append the where clause , passed by the customer, to the seeded SQL */
478 IF (p_where IS NOT NULL) THEN
479 l_final_sql := l_final_sql || ' ' || p_where;
480 END IF;
481 l_final_sql := l_final_sql || ' ) ';
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 */
491 commit;
492 EXECUTE IMMEDIATE 'alter session enable parallel dml';
493 /* Insert all the transaction objects into the TRANS table */
494 EXECUTE IMMEDIATE l_final_sql;
495 commit;
496 EXECUTE IMMEDIATE 'alter session disable parallel dml';
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
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
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',
521 x_msg_data);
522 END IF;
523
524 WHEN L_SEED_DATA_NOTFOUND THEN
525 x_return_status := FND_API.G_RET_STS_ERROR ;
526 RETCODE := 2;
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
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',
543 x_msg_data);
544 END IF;
545
546 WHEN NO_DATA_FOUND THEN
547 x_return_status := FND_API.G_RET_STS_ERROR ;
551 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
548 RETCODE := 2;
549 x_msg_count := 1;
550 ERRBUF := x_msg_data;
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
558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
559 x_msg_data := SQLCODE || ' : ' || SQLERRM;
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;
569
570
571 -- ***************************************************
572 -- API Specifications
573 -- ***************************************************
574 -- api name : Clear_trans_data
575 -- type : public.
576 -- function : Truncate Trans Table, and Drop_TAE_TRANS_Indexes
577 -- pre-reqs :
578 -- notes:
579 --
580 PROCEDURE Clear_Trans_Data
581 ( p_source_id IN NUMBER,
582 p_trans_id IN NUMBER,
583 p_program_name IN VARCHAR2,
584 p_mode IN VARCHAR2,
585 p_request_id IN NUMBER,
586 x_return_status OUT NOCOPY VARCHAR2,
587 x_msg_count OUT NOCOPY NUMBER,
588 x_msg_data OUT NOCOPY VARCHAR2,
589 ERRBUF OUT NOCOPY VARCHAR2,
590 RETCODE OUT NOCOPY VARCHAR2
591 )
592 AS
593 l_trans_target VARCHAR2(30);
594 l_match_target VARCHAR2(30);
595 l_umatch_target VARCHAR2(30);
596 l_winner_target VARCHAR2(30);
597 l_uwinner_target VARCHAR2(30);
598 l_L1_target VARCHAR2(30);
599 l_L2_target VARCHAR2(30);
600 l_L3_target VARCHAR2(30);
601 l_L4_target VARCHAR2(30);
602 l_L5_target VARCHAR2(30);
603 l_WT_target VARCHAR2(30);
604
605 l_dummy NUMBER;
606 BEGIN
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 */
616 SELECT
617 decode(p_mode, 'TOTAL', tup.batch_trans_table_name
618 , 'INCREMENTAL', tup.batch_nm_trans_table_name
619 , 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
620 ,tup.batch_match_table_name
621 ,tup.batch_unique_match_table_name
622 ,tup.batch_winner_table_name
623 ,tup.batch_unique_winner_table_name
624 ,tup.batch_l1_winner_table_name
625 ,tup.batch_l2_winner_table_name
626 ,tup.batch_l3_winner_table_name
627 ,tup.batch_l4_winner_table_name
628 ,tup.batch_l5_winner_table_name
629 ,tup.batch_wt_winner_table_name
630 INTO
631 l_trans_target
632 ,l_match_target
633 ,l_umatch_target
634 ,l_winner_target
635 ,l_uwinner_target
636 ,l_L1_target
637 ,l_L2_target
638 ,l_L3_target
639 ,l_L4_target
640 ,l_L5_target
641 ,l_WT_target
642 FROM
643 jty_trans_usg_pgm_details tup
644 WHERE tup.source_id = p_source_id
645 AND tup.trans_type_id = p_trans_id
646 AND tup.program_name = p_program_name;
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 ||
656 ' WT table name : ' || l_WT_target);
657 END IF;
658
659 /* Truncate and drop indexes on the TRANS table */
660 IF (l_trans_target IS NOT NULL) THEN
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 */
670 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
671 p_table_name => l_trans_target
672 ,x_return_status => x_return_status );
673
674 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
675 -- debug message
679 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.trans_clear',
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,
680 x_msg_data);
681 END IF;
682
683 RAISE FND_API.G_EXC_ERROR;
684 END IF;
685
686 /* Truncate the TRANS table */
687 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
688 p_table_name => l_trans_target
689 ,x_return_status => x_return_status );
690
691 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
701 END IF;
702
703 END IF; /* end IF (l_trans_target IS NOT NULL) */
704
705 /* Truncate and drop indexes on the MATCH table */
706 IF (l_match_target IS NOT NULL) THEN
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 */
716 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
717 p_table_name => l_match_target
718 ,x_return_status => x_return_status );
719
720 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
721
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;
731 END IF;
732
733 /* Truncate the MATCH table */
734 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
735 p_table_name => l_match_target
736 ,x_return_status => x_return_status );
737
738 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
748 END IF;
749
750 END IF; /* end (l_match_target IS NOT NULL) */
751
752 /* Truncate and drop indexes on the UMATCH table */
753 IF (l_umatch_target IS NOT NULL) THEN
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 */
763 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
764 p_table_name => l_umatch_target
765 ,x_return_status => x_return_status );
766
767 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
768
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;
778 END IF;
779
780 /* Truncate the UMATCH table */
781 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
782 p_table_name => l_umatch_target
783 ,x_return_status => x_return_status );
784
785 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
795 END IF;
796
797 END IF; /* end (l_umatch_target IS NOT NULL) */
798
799 /* Truncate and drop indexes on the WINNER table */
803 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
800 IF (l_winner_target IS NOT NULL) THEN
801
802 -- debug message
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 */
810 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
811 p_table_name => l_winner_target
812 ,x_return_status => x_return_status );
813
814 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
815
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;
825 END IF;
826
827 /* Truncate the WINNER table */
828 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
829 p_table_name => l_winner_target
830 ,x_return_status => x_return_status );
831
832 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
842 END IF;
843
844 END IF; /* end (l_winner_target IS NOT NULL) */
845
846 /* Truncate and drop indexes on the UWINNER table */
847 IF (l_uwinner_target IS NOT NULL) THEN
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 */
857 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
858 p_table_name => l_uwinner_target
859 ,x_return_status => x_return_status );
860
861 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
862
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;
872 END IF;
873
874 /* Truncate the UWINNER table */
875 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
876 p_table_name => l_uwinner_target
877 ,x_return_status => x_return_status );
878
879 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
889 END IF;
890
891 END IF; /* end (l_uwinner_target IS NOT NULL) */
892
893 /* Truncate and drop indexes on the L1 table */
894 IF (l_l1_target IS NOT NULL) THEN
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 */
904 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
905 p_table_name => l_l1_target
906 ,x_return_status => x_return_status );
907
908 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
909
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;
919 END IF;
920
921 /* Truncate the L1 table */
922 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
923 p_table_name => l_l1_target
927 -- debug message
924 ,x_return_status => x_return_status );
925
926 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
936 END IF;
937
938 END IF; /* end (l_l1_target IS NOT NULL) */
939
940 /* Truncate and drop indexes on the L2 table */
941 IF (l_l2_target IS NOT NULL) THEN
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 */
951 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
952 p_table_name => l_l2_target
953 ,x_return_status => x_return_status );
954
955 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
956
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;
966 END IF;
967
968 /* Truncate the L2 table */
969 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
970 p_table_name => l_l2_target
971 ,x_return_status => x_return_status );
972
973 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
983 END IF;
984
985 END IF; /* end (l_l2_target IS NOT NULL) */
986
987 /* Truncate and drop indexes on the L3 table */
988 IF (l_l3_target IS NOT NULL) THEN
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 */
998 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
999 p_table_name => l_l3_target
1000 ,x_return_status => x_return_status );
1001
1002 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1003
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;
1013 END IF;
1014
1015 /* Truncate the L3 table */
1016 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1017 p_table_name => l_l3_target
1018 ,x_return_status => x_return_status );
1019
1020 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
1030 END IF;
1031
1032 END IF; /* end (l_l3_target IS NOT NULL) */
1033
1034 /* Truncate and drop indexes on the L4 table */
1035 IF (l_l4_target IS NOT NULL) THEN
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 */
1045 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1046 p_table_name => l_l4_target
1047 ,x_return_status => x_return_status );
1048
1049 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1050
1054 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
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
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;
1060 END IF;
1061
1062 /* Truncate the L4 table */
1063 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1064 p_table_name => l_l4_target
1065 ,x_return_status => x_return_status );
1066
1067 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
1077 END IF;
1078
1079 END IF; /* end (l_l4_target IS NOT NULL) */
1080
1081 /* Truncate and drop indexes on the L5 table */
1082 IF (l_l5_target IS NOT NULL) THEN
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 */
1092 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1093 p_table_name => l_l5_target
1094 ,x_return_status => x_return_status );
1095
1096 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1097
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;
1107 END IF;
1108
1109 /* Truncate the L5 table */
1110 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1111 p_table_name => l_l5_target
1112 ,x_return_status => x_return_status );
1113
1114 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
1124 END IF;
1125
1126 END IF; /* end (l_l5_target IS NOT NULL) */
1127
1128 /* Truncate and drop indexes on the WT table */
1129 IF (l_wt_target IS NOT NULL) THEN
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 */
1139 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1140 p_table_name => l_wt_target
1141 ,x_return_status => x_return_status );
1142
1143 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1144
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;
1154 END IF;
1155
1156 /* Truncate the WT table */
1157 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1158 p_table_name => l_wt_target
1159 ,x_return_status => x_return_status );
1160
1161 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
1171 END IF;
1172
1173 END IF; /* end (l_wt_target IS NOT NULL) */
1174
1175 IF (p_mode = 'TOTAL') THEN
1176 DELETE jty_changed_terrs
1177 WHERE source_id = p_source_id
1178 AND star_request_id IS NOT NULL;
1182 AND tap_request_id IS NOT NULL
1179 ELSIF (p_mode = 'INCREMENTAL') THEN
1180 DELETE jty_changed_terrs
1181 WHERE source_id = p_source_id
1183 AND tap_request_id <> p_request_id;
1184
1185 BEGIN
1186 SELECT 1
1187 INTO l_dummy
1188 FROM jty_changed_terrs
1189 WHERE source_id = p_source_id
1190 AND tap_request_id = p_request_id
1191 AND rownum <= 1;
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 UPDATE jty_changed_terrs
1195 SET tap_request_id = p_request_id
1196 WHERE source_id = p_source_id
1197 AND star_request_id IS NOT NULL;
1198 END;
1199 END IF;
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
1209 WHEN NO_DATA_FOUND THEN
1210 x_return_status := FND_API.G_RET_STS_ERROR ;
1211 RETCODE := 2;
1212 x_msg_count := 1;
1213 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
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
1223 RETCODE := 2;
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
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1234 x_msg_data := SQLCODE || ' : ' || SQLERRM;
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;
1244
1245
1246 -- ***************************************************
1247 -- API Specifications
1248 -- ***************************************************
1249 -- api name : GET_WINNERS_PARALLEL_SETUP
1250 -- type : public.
1251 -- function :
1252 -- pre-reqs :
1253 -- notes:
1254 --
1255 PROCEDURE get_winners_parallel_setup
1256 ( p_source_id IN NUMBER,
1257 p_trans_id IN NUMBER,
1258 p_program_name IN VARCHAR2,
1259 p_mode IN VARCHAR2,
1260 p_no_of_workers IN NUMBER,
1261 p_percent_analyzed IN NUMBER,
1262 p_request_id IN NUMBER,
1263 x_return_status OUT NOCOPY VARCHAR2,
1264 x_msg_count OUT NOCOPY NUMBER,
1265 x_msg_data OUT NOCOPY VARCHAR2,
1266 ERRBUF OUT NOCOPY VARCHAR2,
1267 RETCODE OUT NOCOPY VARCHAR2
1268 )
1269 AS
1270
1271 l_sysdate DATE;
1272 num_of_terr NUMBER;
1273 num_of_trans NUMBER;
1274 d_statement VARCHAR2(2000);
1275
1276 l_trans_target VARCHAR2(30);
1277 l_match_target VARCHAR2(30);
1278 l_umatch_target VARCHAR2(30);
1279 l_winner_target VARCHAR2(30);
1280 l_uwinner_target VARCHAR2(30);
1281 l_L1_target VARCHAR2(30);
1282 l_L2_target VARCHAR2(30);
1283 l_L3_target VARCHAR2(30);
1284 l_L4_target VARCHAR2(30);
1285 l_L5_target VARCHAR2(30);
1286 l_WT_target VARCHAR2(30);
1287
1288 BEGIN
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;
1298 l_sysdate := SYSDATE;
1299
1300 /* Corresponding to the usage, transaction type and program name */
1301 /* get all the interface table names */
1302 SELECT
1303 decode(p_mode, 'TOTAL', tup.batch_trans_table_name,
1304 'INCREMENTAL', tup.batch_nm_trans_table_name,
1305 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
1306 ,tup.batch_match_table_name
1307 ,tup.batch_unique_match_table_name
1308 ,tup.batch_winner_table_name
1309 ,tup.batch_unique_winner_table_name
1310 ,tup.batch_l1_winner_table_name
1311 ,tup.batch_l2_winner_table_name
1312 ,tup.batch_l3_winner_table_name
1313 ,tup.batch_l4_winner_table_name
1314 ,tup.batch_l5_winner_table_name
1318 ,l_umatch_target
1315 ,tup.batch_wt_winner_table_name
1316 INTO
1317 l_trans_target
1319 ,l_match_target
1320 ,l_winner_target
1321 ,l_uwinner_target
1322 ,l_L1_target
1323 ,l_L2_target
1324 ,l_L3_target
1325 ,l_L4_target
1326 ,l_L5_target
1327 ,l_WT_target
1328 FROM
1329 jty_trans_usg_pgm_details tup
1330 WHERE tup.source_id = p_source_id
1331 AND tup.trans_type_id = p_trans_id
1332 AND tup.program_name = p_program_name;
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 ||
1342 ' WT table name : ' || l_WT_target);
1343 END IF;
1344
1345 /* set NOLOGGING on JTF_TAE_..._MATCHES and JTF_TAE_..._WINNERS tables */
1346 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_trans_target);
1347 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_match_target);
1348 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_umatch_target);
1349 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_winner_target);
1350 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_uwinner_target);
1351 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L1_target);
1352 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L2_target);
1353 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L3_target);
1354 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L4_target);
1355 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L5_target);
1356 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_WT_target);
1357
1358 /* Check for territories for this Usage/Transaction Type */
1359 /* This check is not done in date effective mode as inactive territories */
1360 /* can also win depending on the date of the transaction object */
1361 IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
1362 SELECT COUNT(*)
1363 INTO num_of_terr
1364 FROM jtf_terr_qtype_usgs_all jtqu
1365 , jtf_terr_all jt1
1366 , jtf_qual_type_usgs jqtu
1367 WHERE jtqu.terr_id = jt1.terr_id
1368 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1369 AND jqtu.qual_type_id = p_trans_id
1370 AND jqtu.source_id = p_source_id
1371 AND jt1.end_date_active >= l_sysdate
1372 AND jt1.start_date_active <= l_sysdate
1373 AND EXISTS (
1374 SELECT 1
1375 FROM jtf_terr_rsc_all jtr,
1376 jtf_terr_rsc_access_all jtra,
1377 jtf_qual_types_all jqta
1378 WHERE jtr.terr_id = jt1.terr_id
1379 AND jtr.end_date_active >= l_sysdate
1380 AND jtr.start_date_active <= l_sysdate
1381 AND jtr.resource_type <> 'RS_ROLE'
1382 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1383 AND jtra.access_type = jqta.name
1384 AND jqta.qual_type_id = p_trans_id
1385 AND jtra.trans_access_code <> 'NONE')
1386 AND NOT EXISTS (
1387 SELECT jt.terr_id
1388 FROM jtf_terr_all jt
1389 WHERE jt.end_date_active < l_sysdate
1390 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
1391 START WITH jt.terr_id = jt1.terr_id)
1392 AND jqtu.qual_type_id <> -1001;
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
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;
1407 END IF;
1408 END IF; /* END IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) */
1409
1410 d_statement := ' SELECT COUNT(*) FROM ' || l_trans_target || ' WHERE rownum < 2 ';
1411 EXECUTE IMMEDIATE d_statement INTO num_of_trans;
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
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
1426 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1431
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;
1432 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
1433 p_table_name => l_trans_target
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
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
1451 (p_Api_Version_Number => 1.0,
1452 p_Init_Msg_List => FND_API.G_FALSE,
1453 p_trans_target => l_trans_target,
1454 p_classify_terr_comb => 'N',
1455 p_process_tx_oin_sel => 'Y',
1456 p_generate_indexes => 'Y',
1457 p_source_id => p_source_id,
1458 p_trans_id => p_trans_id,
1459 p_program_name => p_program_name,
1460 p_mode => p_mode,
1461 x_Return_Status => x_return_status,
1462 x_Msg_Count => x_msg_count,
1463 x_Msg_Data => x_msg_data,
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
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 */
1481 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1482 ( p_table_name => l_match_target,
1483 p_trans_id => p_trans_id,
1484 p_source_id => p_source_id,
1485 p_program_name => p_program_name,
1486 p_mode => p_mode,
1487 x_Return_Status => x_return_status,
1488 p_run_mode => 'MATCH');
1489
1490 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1491 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_match_target;
1492 RAISE FND_API.G_EXC_ERROR;
1493 END IF;
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 */
1503 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1504 ( p_table_name => l_winner_target,
1505 p_trans_id => p_trans_id,
1506 p_source_id => p_source_id,
1507 p_program_name => p_program_name,
1508 p_mode => p_mode,
1509 x_Return_Status => x_return_status,
1510 p_run_mode => 'WINNER');
1511
1512 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1513 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_winner_target;
1514 RAISE FND_API.G_EXC_ERROR;
1515 END IF;
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 */
1525 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1526 ( p_table_name => l_l1_target,
1527 p_trans_id => p_trans_id,
1528 p_source_id => p_source_id,
1529 p_program_name => p_program_name,
1530 p_mode => p_mode,
1531 x_Return_Status => x_return_status,
1532 p_run_mode => 'TEMP_WINNER');
1533
1534 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1535 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l1_target;
1536 RAISE FND_API.G_EXC_ERROR;
1537 END IF;
1538
1542 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l2',
1539 -- debug message
1540 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1541 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
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 */
1547 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1548 ( p_table_name => l_l2_target,
1549 p_trans_id => p_trans_id,
1550 p_source_id => p_source_id,
1551 p_program_name => p_program_name,
1552 p_mode => p_mode,
1553 x_Return_Status => x_return_status,
1554 p_run_mode => 'TEMP_WINNER');
1555
1556 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1557 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l2_target;
1558 RAISE FND_API.G_EXC_ERROR;
1559 END IF;
1560
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 */
1570 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1571 ( p_table_name => l_l3_target,
1572 p_trans_id => p_trans_id,
1573 p_source_id => p_source_id,
1574 p_program_name => p_program_name,
1575 p_mode => p_mode,
1576 x_Return_Status => x_return_status,
1577 p_run_mode => 'TEMP_WINNER');
1578
1579 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1580 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l3_target;
1581 RAISE FND_API.G_EXC_ERROR;
1582 END IF;
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 */
1592 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1593 ( p_table_name => l_l4_target,
1594 p_trans_id => p_trans_id,
1595 p_source_id => p_source_id,
1596 p_program_name => p_program_name,
1597 p_mode => p_mode,
1598 x_Return_Status => x_return_status,
1599 p_run_mode => 'TEMP_WINNER');
1600
1601 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1602 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l4_target;
1603 RAISE FND_API.G_EXC_ERROR;
1604 END IF;
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 */
1614 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1615 ( p_table_name => l_l5_target,
1616 p_trans_id => p_trans_id,
1617 p_source_id => p_source_id,
1618 p_program_name => p_program_name,
1619 p_mode => p_mode,
1620 x_Return_Status => x_return_status,
1621 p_run_mode => 'TEMP_WINNER');
1622
1623 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1624 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l5_target;
1625 RAISE FND_API.G_EXC_ERROR;
1626 END IF;
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 */
1636 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1637 ( p_table_name => l_wt_target,
1638 p_trans_id => p_trans_id,
1639 p_source_id => p_source_id,
1640 p_program_name => p_program_name,
1641 p_mode => p_mode,
1642 x_Return_Status => x_return_status,
1643 p_run_mode => 'TEMP_WINNER');
1644
1645 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1646 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_wt_target;
1647 RAISE FND_API.G_EXC_ERROR;
1648 END IF;
1649
1650 /* if mode is incremental, update the worker_id column for the TRANS table */
1651 IF (p_mode = 'INCREMENTAL') THEN
1652 d_statement := 'UPDATE ' || l_trans_target ||
1653 ' SET worker_id = mod(trans_object_id, :no_of_workers) + 1';
1654 EXECUTE IMMEDIATE d_statement USING p_no_of_workers;
1655 END IF;
1659 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1656
1657 -- debug message
1658 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
1666 x_return_status := FND_API.G_RET_STS_SUCCESS;
1667 x_msg_count := 1;
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
1677 x_return_status := FND_API.G_RET_STS_ERROR;
1678 x_msg_count := 1;
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
1688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1689 x_msg_count := 1;
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
1699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1700 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1701 x_msg_count := 1;
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;
1711
1712 -- ***************************************************
1713 -- API Specifications
1714 -- ***************************************************
1715 PROCEDURE Process_Level_Winners (
1716 p_terr_LEVEL_target_tbl IN VARCHAR2,
1717 p_terr_PARENT_LEVEL_tbl IN VARCHAR2,
1718 p_UPPER_LEVEL_FROM_ROOT IN NUMBER,
1719 p_LOWER_LEVEL_FROM_ROOT IN NUMBER,
1720 p_matches_target IN VARCHAR2,
1721 p_source_id IN NUMBER,
1722 p_run_mode IN VARCHAR2,
1723 p_date_effective IN BOOLEAN,
1724 x_return_status OUT NOCOPY VARCHAR2,
1725 p_worker_id IN NUMBER
1726 )
1727 AS
1728
1729 l_denorm_table_name VARCHAR2(60);
1730 l_dyn_str VARCHAR2(32767);
1731
1732 BEGIN
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;
1742
1743 IF (p_date_effective) THEN
1744 l_denorm_table_name := 'JTY_DENORM_DEA_RULES_ALL';
1745 ELSE
1746 l_denorm_table_name := 'JTF_TERR_DENORM_RULES_ALL';
1747 END IF;
1748
1749 IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 1) THEN
1750
1751 l_dyn_str := ' ' ||
1752 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
1753 ' ( ' ||
1754 ' trans_object_id ' ||
1755 ' , trans_detail_object_id ' ||
1756 ' , txn_date ' ||
1757 ' , WIN_TERR_ID ' ||
1758 ' , UL_TERR_ID ' ||
1759 ' , LL_TERR_ID ' ||
1760 ' , LL_NUM_WINNERS ' ||
1761 ' , WORKER_ID ' ||
1762 ' ) ' ||
1763 ' (SELECT ' ||
1764 ' TL.trans_object_id ' ||
1765 ' , TL.trans_detail_object_id ' ||
1766 ' , TL.txn_date ' ||
1767 ' , TL.CL_WIN_TERR_ID ' ||
1768 ' , TL.UL_terr_id ' ||
1769 ' , TL.LL_terr_id ' ||
1770 ' , TL.LL_num_winners ' ||
1771 ' , :B_WORKER_ID ' ||
1772 ' FROM ( ' ||
1773 ' SELECT ';
1774
1775 IF (p_run_mode = 'BATCH') THEN
1776 /* Batch TAE */
1777 l_dyn_str := l_dyn_str || ' /*+ FULL(M) */ ';
1778 ELSE
1779 /* Real-time TAE */
1780 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
1781 END IF;
1782
1783 l_dyn_str := l_dyn_str ||
1784 ' DISTINCT ' ||
1785 ' m.trans_object_id ' ||
1786 ' , m.trans_detail_object_id ' ||
1787 ' , m.txn_date ' ||
1788 ' , LL.RELATED_TERR_ID CL_WIN_TERR_ID ' ||
1789 ' , UL.related_terr_id UL_TERR_ID ' ||
1793 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
1790 ' , NVL(UL.num_winners, 1) UL_NUM_WINNERS ' ||
1791 ' , LL.related_terr_id LL_TERR_ID ' ||
1792 ' , NVL(LL.num_winners, 1) LL_NUM_WINNERS ' ||
1794 ' m.trans_object_id ' ||
1795 ' , m.trans_detail_object_id ' ||
1796 ' , UL.related_terr_id ' ||
1797 ' ORDER BY LL.absolute_rank DESC ' ||
1798 ' , LL.related_terr_id ) AS LL_TERR_RANK ' ||
1799 ' FROM ' || p_matches_target || ' M ' ||
1800 ' , ' || l_denorm_table_name || ' UL ' ||
1801 ' , ' || l_denorm_table_name || ' LL ' ||
1802 ' WHERE UL.level_from_root = :b1_UPPER_LEVEL +1 ' || /* UPPER level territory */
1803 ' AND UL.source_id = :b1_source_id ' ||
1804 ' AND UL.terr_id = M.TERR_ID ' ||
1805 ' AND LL.level_from_root = :b1_LOWER_LEVEL +1 ' || /* LOWER level territory */
1806 ' AND LL.source_id = :b2_source_id ' ||
1807 ' AND LL.terr_id = M.TERR_ID ' ||
1808 ' AND M.worker_id = :B_WORKER_ID ' ||
1809 ' ) TL ' ||
1810 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
1811 ' ) ';
1812
1813 BEGIN
1814 EXECUTE IMMEDIATE l_dyn_str USING
1815 p_worker_id
1816 , p_UPPER_LEVEL_FROM_ROOT
1817 , p_source_id
1818 , p_LOWER_LEVEL_FROM_ROOT
1819 , p_source_id
1820 , p_worker_id;
1821 EXCEPTION
1822 WHEN NO_DATA_FOUND THEN
1823 NULL;
1824 END;
1825
1826 ELSE
1827
1828 l_dyn_str := ' ' ||
1829 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
1830 ' ( ' ||
1831 ' trans_object_id ' ||
1832 ' , trans_detail_object_id ' ||
1833 ' , txn_date ' ||
1834 ' , WIN_TERR_ID ' ||
1835 ' , UL_TERR_ID ' ||
1836 ' , LL_TERR_ID ' ||
1837 ' , LL_NUM_WINNERS ' ||
1838 ' , WORKER_ID ' ||
1839 ' ) ' ||
1840 ' (SELECT ' ||
1841 ' TL.trans_object_id ' ||
1842 ' , TL.trans_detail_object_id ' ||
1843 ' , TL.txn_date ' ||
1844 ' , TL.CL_WIN_TERR_ID ' ||
1845 ' , TL.UL_terr_id ' ||
1846 ' , TL.LL_terr_id ' ||
1847 ' , TL.LL_num_winners ' ||
1848 ' , :B_WORKER_ID ' ||
1849 ' FROM ( ' || /* NL */
1850 ' SELECT ' ||
1851 ' CL.trans_object_id ' ||
1852 ' , CL.trans_detail_object_id ' ||
1853 ' , CL.txn_date ' ||
1854 ' , CL.CL_WIN_TERR_ID ' ||
1855 ' , CL.UL_terr_id ';
1856
1857 IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 2) THEN
1858 l_dyn_str := l_dyn_str || ' , NVL(CL.UL_NUM_WINNERS, 1) UL_NUM_WINNERS ';
1859 ELSE
1860 l_dyn_str := l_dyn_str || ' , CL.UL_NUM_WINNERS UL_NUM_WINNERS ';
1861 END IF;
1862
1863 l_dyn_str := l_dyn_str ||
1864 ' , CL.LL_TERR_ID ' ||
1865 ' , CL.LL_NUM_WINNERS ' ||
1866 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
1867 ' CL.trans_object_id ' ||
1868 ' , CL.trans_detail_object_id ' ||
1869 ' , CL.UL_TERR_ID ' ||
1870 ' ORDER BY ' ||
1871 ' CL.M_ABS_RANK DESC ' ||
1872 ' , CL.CL_WIN_TERR_ID ) AS LL_TERR_RANK ' ||
1873 ' FROM ( ' ||
1874 ' SELECT ';
1875
1876 IF (p_run_mode = 'BATCH') THEN
1877 /* Batch TAE */
1878 l_dyn_str := l_dyn_str || ' /*+ USE_HASH(ML) USE_HASH(LL) USE_HASH(UL) USE_HASH(M) ORDERED */ ';
1879 ELSE
1880 /* Real-time TAE */
1881 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
1882 END IF;
1883
1884 l_dyn_str := l_dyn_str ||
1885 ' m.trans_object_id ' ||
1886 ' , m.trans_detail_object_id ' ||
1887 ' , m.txn_date ' ||
1888 ' , LL.related_terr_id CL_WIN_TERR_ID ' ||
1889 ' , UL.related_terr_id UL_TERR_ID ' ||
1890 ' , UL.num_winners UL_NUM_WINNERS ' ||
1891 ' , LL.related_terr_id LL_TERR_ID ' ||
1892 ' , LL.num_winners LL_NUM_WINNERS ' ||
1893 ' , max(m.absolute_rank) M_ABS_RANK ' ||
1894 ' FROM ';
1895
1896 IF (p_run_mode = 'BATCH') THEN
1897 l_dyn_str := l_dyn_str ||
1898 ' ' || l_denorm_table_name || ' UL ' ||
1899 ' , ' || p_matches_target || ' M ' ||
1900 ' , ' || l_denorm_table_name || ' LL ' ||
1901 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ';
1902 ELSE
1903 l_dyn_str := l_dyn_str ||
1904 ' ' || l_denorm_table_name || ' LL ' ||
1905 ' , ' || p_matches_target || ' M ' ||
1906 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
1907 ' , ' || l_denorm_table_name || ' UL ';
1908 END IF;
1909
1910 l_dyn_str := l_dyn_str ||
1914 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
1911 ' WHERE UL.level_from_root = :b1_UPPER_LEVEL +1 ' || /* UPPER level territory */
1912 ' AND UL.source_id = :b2_source_id ' ||
1913 ' AND UL.terr_id = M.TERR_ID ' ||
1915 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
1916 ' M.trans_detail_object_id IS NULL ) ' ||
1917 ' AND M.trans_object_id = ML.trans_object_id ' ||
1918 ' AND M.worker_id = ML.WORKER_ID' ||
1919 ' AND M.worker_id = :B_WORKER_ID ' ||
1920 ' AND ML.worker_id = :B_WORKER_ID ' ||
1921 ' AND LL.level_from_root = :b4_LOWER_LEVEL +1 ' || /* LOWER level territory */
1922 ' AND LL.source_id = :b5_source_id ' ||
1923 ' AND ML.LL_NUM_WINNERS IS NOT NULL ' ||
1924 ' AND LL.NUM_WINNERS IS NOT NULL ' ||
1925 ' AND LL.terr_id = M.TERR_ID ' ||
1926 ' GROUP BY ' ||
1927 ' m.trans_object_id ' ||
1928 ' , m.trans_detail_object_id ' ||
1929 ' , m.txn_date ' ||
1930 ' , LL.related_terr_id ' ||
1931 ' , UL.related_terr_id ' ||
1932 ' , UL.num_winners ' ||
1933 ' , LL.related_terr_id ' ||
1934 ' , LL.num_winners ' ||
1935
1936 ' UNION ALL ' ||
1937
1938 ' SELECT ';
1939
1940 IF (p_run_mode = 'BATCH') THEN
1941 /* Batch TAE */
1942 l_dyn_str := l_dyn_str || ' /*+ ORDERED USE_HASH(ML) USE_HASH(M) USE_HASH(UL) USE_HASH(LL) */ ';
1943 ELSE
1944 /* Real-time TAE */
1945 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
1946 END IF;
1947
1948 l_dyn_str := l_dyn_str ||
1949 ' DISTINCT ' ||
1950 ' m.trans_object_id ' ||
1951 ' , m.trans_detail_object_id ' ||
1952 ' , m.txn_date ' ||
1953 ' , m.terr_id CL_WIN_TERR_ID ' ||
1954 ' , UL.related_terr_id UL_TERR_ID ' ||
1955 ' , UL.num_winners UL_NUM_WINNERS ' ||
1956 ' , LL.related_terr_id LL_TERR_ID ' ||
1957 ' , LL.num_winners LL_NUM_WINNERS ' ||
1958 ' , m.absolute_rank M_ABS_RANK ' ||
1959 ' FROM ';
1960
1961 IF (p_run_mode = 'BATCH') THEN
1962 l_dyn_str := l_dyn_str ||
1963 ' ' || l_denorm_table_name || ' UL ' ||
1964 ' , ' || p_matches_target || ' M ' ||
1965 ' , ' || l_denorm_table_name || ' LL ' ||
1966 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ';
1967 ELSE
1968 l_dyn_str := l_dyn_str ||
1969 ' ' || l_denorm_table_name || ' LL ' ||
1970 ' , ' || p_matches_target || ' M ' ||
1971 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
1972 ' , ' || l_denorm_table_name || ' UL ';
1973 END IF;
1974
1975 l_dyn_str := l_dyn_str ||
1976 ' WHERE UL.level_from_root = :b9_UPPER_LEVEL +1 ' || /* UPPER level territory */
1977 ' AND UL.source_id = :b10_source_id ' ||
1978 ' AND UL.terr_id = M.TERR_ID ' ||
1979 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
1980 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
1981 ' M.trans_detail_object_id IS NULL ) ' ||
1982 ' AND M.trans_object_id = ML.trans_object_id ' ||
1983 ' AND M.worker_id = ML.WORKER_ID' ||
1984 ' AND M.worker_id = :B_WORKER_ID ' ||
1985 ' AND ML.worker_id = :B_WORKER_ID ' ||
1986 ' AND LL.level_from_root = :b12_LOWER_LEVEL +1 ' || /* LOWER level territory */
1987 ' AND LL.source_id = :b13_source_id ' ||
1988 ' AND ML.LL_NUM_WINNERS IS NOT NULL ' ||
1989 ' AND LL.NUM_WINNERS IS NULL ' ||
1990 ' AND LL.terr_id = M.TERR_ID ' ||
1991 ' ) CL ' ||
1992 ' ) TL ' ||
1993 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
1994 ' ) ';
1995
1996 BEGIN
1997 EXECUTE IMMEDIATE l_dyn_str USING
1998 p_worker_id
1999 , p_UPPER_LEVEL_FROM_ROOT
2000 , p_source_id
2001 , p_worker_id
2002 , p_worker_id
2003 , p_LOWER_LEVEL_FROM_ROOT
2004 , p_source_id
2005 , p_UPPER_LEVEL_FROM_ROOT
2006 , p_source_id
2007 , p_worker_id
2008 , p_worker_id
2009 , p_LOWER_LEVEL_FROM_ROOT
2010 , p_source_id;
2011
2012 EXCEPTION
2013 WHEN NO_DATA_FOUND THEN
2014 NULL;
2015 END;
2016
2017 END IF; /* end IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 1) */
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',
2026 -- debug message
2023 'Number of rows inserted into ' || p_terr_LEVEL_target_tbl || ' : ' || SQL%ROWCOUNT);
2024 END IF;
2025
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
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;
2053
2054
2055 -- ***************************************************
2056 -- API Specifications
2057 -- ***************************************************
2058 PROCEDURE Process_Final_Level_Winners (
2059 p_terr_LEVEL_target_tbl IN VARCHAR2,
2060 p_terr_L5_target_tbl IN VARCHAR2,
2061 p_matches_target IN VARCHAR2,
2062 p_source_id IN NUMBER,
2063 p_run_mode IN VARCHAR2,
2064 p_date_effective IN BOOLEAN,
2065 x_return_status OUT NOCOPY VARCHAR2,
2066 p_worker_id IN NUMBER
2067 )
2068 AS
2069
2070 l_dyn_str VARCHAR2(32767);
2071 l_denorm_table_name VARCHAR2(30);
2072
2073 BEGIN
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;
2083
2084 IF (p_date_effective) THEN
2085 l_denorm_table_name := 'JTY_DENORM_DEA_RULES_ALL';
2086 ELSE
2087 l_denorm_table_name := 'JTF_TERR_DENORM_RULES_ALL';
2088 END IF;
2089
2090 l_dyn_str := ' ' ||
2091 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
2092 ' ( ' ||
2093 ' trans_object_id ' ||
2094 ' , trans_detail_object_id ' ||
2095 ' , txn_date ' ||
2096 ' , WIN_TERR_ID ' ||
2097 ' , UL_TERR_ID ' ||
2098 ' , LL_TERR_ID ' ||
2099 ' , worker_id ' ||
2100 ' ) ' ||
2101 ' (SELECT ' ||
2102 ' TL.trans_object_id ' ||
2103 ' , TL.trans_detail_object_id ' ||
2104 ' , TL.txn_date ' ||
2105 ' , TL.WIN_TERR_ID ' ||
2106 ' , TL.UL_terr_id ' ||
2107 ' , TL.terr_id ' ||
2108 ' , :B_WORKER_ID ' || --p_worker_id ||
2109 ' FROM ( ' ||
2110 ' SELECT ';
2111
2112 IF (p_run_mode = 'BATCH') THEN
2113 /* Batch TAE */
2114 NULL;
2115 ELSE
2116 /* Real-time TAE */
2117 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
2118 END IF;
2119
2120 l_dyn_str := l_dyn_str ||
2121 ' DISTINCT ' ||
2122 ' m.trans_object_id ' ||
2123 ' , m.trans_detail_object_id ' ||
2124 ' , m.txn_date ' ||
2125 ' , M.TERR_ID WIN_TERR_ID ' ||
2126 ' , UL.related_terr_id UL_TERR_ID ' ||
2127 ' , UL.num_winners UL_NUM_WINNERS ' ||
2128 ' , M.terr_id TERR_ID ' ||
2129 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
2130 ' m.trans_object_id ' ||
2131 ' , m.trans_detail_object_id ' ||
2132 ' , UL.related_terr_id ' ||
2133 ' ORDER BY M.absolute_rank DESC, M.TERR_ID ) AS LL_TERR_RANK ' ||
2134 ' FROM ' || p_matches_target || ' M ' ||
2135 ' , ' || l_denorm_table_name || ' UL ' ||
2136 ' , ' || p_terr_L5_target_tbl || ' ML ' || /* FINAL LEVEL TABLE */
2137 ' , jtf_terr_all jt ' ||
2138 ' , ' || l_denorm_table_name || ' LL ' ||
2139 ' WHERE UL.level_from_root = 6 ' || /* UPPER level */
2140 ' AND UL.source_id = :b1_source_id ' ||
2141 ' AND UL.terr_id = M.TERR_ID ' ||
2142 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
2143 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
2144 ' M.trans_detail_object_id IS NULL ) ' ||
2145 ' AND M.trans_object_id = ML.trans_object_id ' ||
2146 ' AND M.worker_id = ML.WORKER_ID' ||
2150 ' AND LL.level_from_root >= 6 ' || /* FINAL LEVEL(S) */
2147 ' AND M.worker_id = :B_WORKER_ID ' ||
2148 ' AND ML.worker_id = :B_WORKER_ID ' ||
2149 ' AND jt.terr_id = LL.related_terr_id ' ||
2151 ' AND LL.source_id = :b2_source_id ' ||
2152 ' AND LL.terr_id = M.TERR_ID ' ||
2153 ' ) TL ' ||
2154 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
2155 ' ) ';
2156
2157 BEGIN
2158 EXECUTE IMMEDIATE l_dyn_str USING
2159 p_worker_id
2160 , p_source_id
2161 , p_worker_id
2162 , p_worker_id
2163 , p_source_id;
2164
2165 EXCEPTION
2166 WHEN NO_DATA_FOUND THEN
2167 NULL;
2168 END;
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
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
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;
2194
2195
2196 -- ***************************************************
2197 -- API Specifications
2198 -- ***************************************************
2199 -- api name : process_nmc_match
2200 -- type : private.
2201 -- function :
2202 -- pre-reqs :
2203 -- notes: API designed to get the transaction objs that satisfy changed terr defn.
2204 --
2205 PROCEDURE process_nmc_match
2206 ( p_source_id IN NUMBER,
2207 p_trans_id IN NUMBER,
2208 p_program_name IN VARCHAR2,
2209 p_request_id IN NUMBER,
2210 x_return_status OUT NOCOPY VARCHAR2,
2211 x_msg_count OUT NOCOPY NUMBER,
2212 x_msg_data OUT NOCOPY VARCHAR2,
2213 ERRBUF OUT NOCOPY VARCHAR2,
2214 RETCODE OUT NOCOPY VARCHAR2
2215 ) AS
2216
2217 l_match_sql VARCHAR2(32767);
2218 l_status VARCHAR2(30);
2219 l_industry VARCHAR2(30);
2220 l_owner VARCHAR2(30);
2221 l_trans_target VARCHAR2(30);
2222 l_nm_trans_target VARCHAR2(30);
2223 l_insert_stmt VARCHAR2(3000);
2224 l_select_stmt VARCHAR2(3000);
2225 first_time BOOLEAN;
2226 l_indent VARCHAR2(30);
2227 l_final_sql VARCHAR2(32767);
2228 l_sysdate DATE;
2229 l_delete_sql VARCHAR2(3000);
2230
2231
2232 CURSOR c_get_qualrel_prod(cl_source_id number, cl_trans_id number) IS
2233 SELECT jtqp.relation_product
2234 FROM jtf_tae_qual_products jtqp
2235 WHERE jtqp.source_id = cl_source_id
2236 AND jtqp.trans_object_type_id = cl_trans_id
2237 ORDER BY jtqp.relation_product DESC;
2238
2239 CURSOR c1(p_table_name IN VARCHAR2, p_owner IN VARCHAR2) is
2240 SELECT column_name
2241 FROM all_tab_columns
2242 WHERE table_name = p_table_name
2243 AND owner = p_owner
2244 AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
2245 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
2246 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
2247
2248 L_SCHEMA_NOTFOUND EXCEPTION;
2249 BEGIN
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;
2259
2260 /* Get the schema name corresponding to JTF application */
2261 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
2262 NULL;
2263 END IF;
2264
2265 IF (l_owner IS NULL) THEN
2266 RAISE L_SCHEMA_NOTFOUND;
2267 END IF;
2268
2269 /* Get the TRANS and NM_TRANS table names and active transaction type batch SQL */
2270 BEGIN
2271 SELECT tup.batch_trans_table_name
2272 ,tup.batch_nm_trans_table_name
2273 INTO l_trans_target
2274 ,l_nm_trans_target
2275 FROM jty_trans_usg_pgm_details tup
2276 WHERE tup.source_id = p_source_id
2277 AND tup.trans_type_id = p_trans_id
2278 AND tup.program_name = p_program_name;
2282 ' program name : ' || p_program_name;
2279 EXCEPTION
2280 WHEN NO_DATA_FOUND THEN
2281 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
2283 RAISE;
2284 END;
2285
2286 /* Delete from TRANS the txn objs present in NM_TRANS table */
2287 commit;
2288 EXECUTE IMMEDIATE 'alter session enable parallel dml';
2289
2290 l_delete_sql :=
2291 'DELETE FROM ' || l_trans_target || ' A' || g_new_LINE ||
2292 'WHERE EXISTS ( ' || g_new_line ||
2293 ' SELECT 1 ' || g_new_line ||
2294 ' FROM ' || l_nm_trans_target || ' B' || g_new_line ||
2295 ' WHERE A.trans_object_id = B.trans_object_id )';
2296 EXECUTE IMMEDIATE l_delete_sql;
2297
2298 commit;
2299 EXECUTE IMMEDIATE 'alter session disable parallel dml';
2300
2301 /* Initialize local variables */
2302 first_time := TRUE;
2303 l_indent := ' ';
2304 l_sysdate := SYSDATE;
2305
2306 /* Form the insert statement to insert transaction objects into TRANS table */
2307 l_insert_stmt := 'INSERT INTO ' || l_trans_target || '(';
2308 l_select_stmt := '(SELECT ';
2309
2310 FOR column_names in c1(l_trans_target, l_owner) LOOP
2311 IF (first_time) THEN
2312 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
2313 l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
2314 first_time := FALSE;
2315 ELSE
2316 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
2317 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
2318 END IF;
2319 END LOOP;
2320
2321 /* Standard WHO columns */
2322 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
2323 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
2324 g_new_line || l_indent || ',CREATION_DATE ' ||
2325 g_new_line || l_indent || ',CREATED_BY ' ||
2326 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
2327 g_new_line || l_indent || ',REQUEST_ID ' ||
2328 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
2329 g_new_line || l_indent || ',PROGRAM_ID ' ||
2330 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
2331 g_new_line || l_indent || ',TXN_DATE ' ||
2332 g_new_line || l_indent || ',WORKER_ID ' ||
2333 g_new_line || ')';
2334
2335 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2336 g_new_line || l_indent || ',''' || g_user_id || '''' ||
2337 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2338 g_new_line || l_indent || ',''' || g_user_id || '''' ||
2339 g_new_line || l_indent || ',''' || g_login_id || '''' ||
2340 g_new_line || l_indent || ',''' || p_request_id || '''' ||
2341 g_new_line || l_indent || ',''' || g_appl_id || '''' ||
2342 g_new_line || l_indent || ',''' || g_program_id || '''' ||
2343 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2344 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2345 g_new_line || l_indent || ', 1 ';
2346
2347 l_final_sql := l_insert_stmt || g_new_line ||
2348 l_select_stmt || g_new_line || 'FROM ' || l_nm_trans_target || g_new_line || ' ) ';
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 */
2358 EXECUTE IMMEDIATE l_final_sql;
2359
2360 FOR jtf_csr IN c_get_qualrel_prod(p_source_id, p_trans_id) LOOP
2361 BEGIN
2362 SELECT batch_nmc_match_sql
2363 INTO l_match_sql
2364 FROM jty_tae_attr_products_sql
2365 WHERE source_id = p_source_id
2366 AND trans_type_id = p_trans_id
2367 AND program_name = p_program_name
2368 AND attr_relation_product = jtf_csr.relation_product;
2369 EXCEPTION
2370 WHEN NO_DATA_FOUND THEN
2371 x_msg_data := 'No matching SQL found corresponding to source : ' || p_source_id || ' trans : ' || p_trans_id ||
2372 ' Program name : ' || p_program_name || ' relation product : ' || jtf_csr.relation_product;
2373 RAISE;
2374 END;
2375
2376 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
2377 g_appl_id, g_program_id, l_sysdate, p_request_id, l_sysdate;
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
2387 -- debug message
2391 'End of the procedure JTY_ASSIGN_BULK_PUB.process_nmc_match');
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',
2392 END IF;
2393
2394 EXCEPTION
2395 WHEN L_SCHEMA_NOTFOUND THEN
2396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2397 x_msg_data := 'Schema name corresponding to JTF application not found';
2398 RETCODE := 2;
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
2408 x_return_status := FND_API.G_RET_STS_ERROR ;
2409 RETCODE := 2;
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
2419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2420 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2421 x_msg_count := 1;
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;
2431
2432
2433 -- ***************************************************
2434 -- API Specifications
2435 -- ***************************************************
2436 -- api name : collect_trans_data
2437 -- type : public.
2438 -- function :
2439 -- pre-reqs :
2440 -- notes: API designed to insert transaction objects into TRANS table
2441 -- for "TOTAL", "INCREMENTAL" and "DATE EFFECTIVE" mode.
2442 --
2443 PROCEDURE collect_trans_data
2444 ( p_api_version_number IN NUMBER,
2445 p_init_msg_list IN VARCHAR2,
2446 p_source_id IN NUMBER,
2447 p_trans_id IN NUMBER,
2448 p_program_name IN VARCHAR2,
2449 p_mode IN VARCHAR2,
2450 p_where IN VARCHAR2,
2451 p_no_of_workers IN NUMBER,
2452 p_percent_analyzed IN NUMBER,
2453 p_request_id IN NUMBER,
2454 x_return_status OUT NOCOPY VARCHAR2,
2455 x_msg_count OUT NOCOPY NUMBER,
2456 x_msg_data OUT NOCOPY VARCHAR2,
2457 ERRBUF OUT NOCOPY VARCHAR2,
2458 RETCODE OUT NOCOPY VARCHAR2
2459 ) AS
2460
2461 l_api_name CONSTANT VARCHAR2(30) := 'collect_trans_data';
2462 l_api_version_number CONSTANT NUMBER := 1.0;
2463
2464 BEGIN
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. */
2474 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2475 p_api_version_number,
2476 l_api_name,
2477 G_PKG_NAME) THEN
2478
2479 x_msg_data := 'API FND_API.Compatible_API_Call has failed';
2480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2481 END IF;
2482
2483 /* Initialize message list if p_init_msg_list is set to TRUE. */
2484 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2485 FND_MSG_PUB.initialize;
2486 END IF;
2487
2488 x_return_status := FND_API.G_RET_STS_SUCCESS;
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;
2498
2499 /* Clear the interface tables */
2500 clear_trans_data (
2501 p_source_id => p_source_id,
2502 p_trans_id => p_trans_id,
2503 p_program_name => p_program_name,
2504 p_mode => p_mode,
2505 p_request_id => p_request_id,
2506 x_return_status => x_return_status,
2507 x_msg_count => x_msg_count,
2508 x_msg_data => x_msg_data,
2509 errbuf => errbuf,
2513 -- debug message
2510 retcode => retcode);
2511
2512 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
2522 END IF;
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 */
2532 insert_trans_data (
2533 p_source_id => p_source_id,
2534 p_trans_id => p_trans_id,
2535 p_program_name => p_program_name,
2536 p_mode => p_mode,
2537 p_where => p_where,
2538 p_no_of_workers => p_no_of_workers,
2539 p_request_id => p_request_id,
2540 x_return_status => x_return_status,
2541 x_msg_count => x_msg_count,
2542 x_msg_data => x_msg_data,
2543 errbuf => errbuf,
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);
2553 END IF;
2554
2555 RAISE FND_API.G_EXC_ERROR;
2556 END IF;
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
2566
2567 /* Synchronize trans and nm_trans table and insert objects */
2568 /* that satisfy the modified territory definition */
2569 process_nmc_match (
2570 p_source_id => p_source_id,
2571 p_trans_id => p_trans_id,
2572 p_program_name => p_program_name,
2573 p_request_id => p_request_id,
2574 x_return_status => x_return_status,
2575 x_msg_count => x_msg_count,
2576 x_msg_data => x_msg_data,
2577 errbuf => errbuf,
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);
2587 END IF;
2588
2589 RAISE FND_API.G_EXC_ERROR;
2590 END IF;
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 */
2600 insert_nm_trans_data (
2601 p_source_id => p_source_id,
2602 p_trans_id => p_trans_id,
2603 p_program_name => p_program_name,
2604 p_request_id => p_request_id,
2605 x_return_status => x_return_status,
2606 x_msg_count => x_msg_count,
2607 x_msg_data => x_msg_data,
2608 errbuf => errbuf,
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);
2618 END IF;
2619
2620 RAISE FND_API.G_EXC_ERROR;
2621 END IF;
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') */
2631
2632 get_winners_parallel_setup (
2633 p_source_id => p_source_id,
2634 p_trans_id => p_trans_id,
2635 p_program_name => p_program_name,
2639 p_request_id => p_request_id,
2636 p_mode => p_mode,
2637 p_no_of_workers => p_no_of_workers,
2638 p_percent_analyzed => p_percent_analyzed,
2640 x_return_status => x_return_status,
2641 x_msg_count => x_msg_count,
2642 x_msg_data => x_msg_data,
2643 ERRBUF => ERRBUF,
2644 RETCODE => RETCODE
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);
2654 END IF;
2655
2656 RAISE FND_API.G_EXC_ERROR;
2657 END IF;
2658
2659 retcode := 0;
2660 errbuf := null;
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
2670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2672 x_msg_count := 1;
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
2682 x_return_status := FND_API.G_RET_STS_ERROR ;
2683 RETCODE := 2;
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
2693 RETCODE := 2;
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
2703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2704 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2705 x_msg_count := 1;
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;
2715
2716
2717 -- ***************************************************
2718 -- API Specifications
2719 -- ***************************************************
2720 -- api name : process_winners
2721 -- type : private.
2722 -- function :
2723 -- pre-reqs :
2724 -- notes: API designed to get the winning territories for the
2725 -- transaction objs, it supports multiple worker architecture
2726 --
2727 PROCEDURE process_winners
2728 ( p_source_id IN NUMBER,
2729 p_trans_id IN NUMBER,
2730 p_program_name IN VARCHAR2,
2731 p_mode IN VARCHAR2,
2732 p_percent_analyzed IN NUMBER,
2733 p_worker_id IN NUMBER,
2734 x_return_status OUT NOCOPY VARCHAR2,
2735 x_msg_count OUT NOCOPY NUMBER,
2736 x_msg_data OUT NOCOPY VARCHAR2,
2737 ERRBUF OUT NOCOPY VARCHAR2,
2738 RETCODE OUT NOCOPY VARCHAR2
2739 ) AS
2740
2741 l_match_target VARCHAR2(40);
2742 l_umatch_target VARCHAR2(40);
2743 l_l1_target VARCHAR2(40);
2744 l_l2_target VARCHAR2(40);
2745 l_l3_target VARCHAR2(40);
2746 l_l4_target VARCHAR2(40);
2747 l_l5_target VARCHAR2(40);
2748 l_wt_target VARCHAR2(40);
2749 l_winner_target VARCHAR2(40);
2750 l_uwinner_target VARCHAR2(40);
2751 l_mp_winner_target VARCHAR2(25);
2752 l_dmc_winner_target VARCHAR2(25);
2753 lp_sysdate DATE;
2754 l_multi_level_winning_flag VARCHAR2(1);
2755 l_date_effective BOOLEAN;
2756 l_status VARCHAR2(30);
2757 l_industry VARCHAR2(30);
2758 l_fnd_schema VARCHAR2(30);
2759 l_max_terr NUMBER;
2760 l_no_ind_cols NUMBER;
2761
2762
2763 l_dyn_str LONG;
2764
2765 L_SCHEMA_NOTFOUND EXCEPTION;
2766 L_NO_MATCH_TERR EXCEPTION;
2767 BEGIN
2768
2772 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.start',
2769 -- debug message
2770 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2771 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
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;
2778
2779 IF (p_mode = 'DATE EFFECTIVE') THEN
2780 l_date_effective := true;
2781 ELSE
2782 l_date_effective := false;
2783 END IF;
2784
2785 BEGIN
2786 SELECT batch_match_table_name
2787 ,batch_unique_match_table_name
2788 ,batch_l1_winner_table_name
2789 ,batch_l2_winner_table_name
2790 ,batch_l3_winner_table_name
2791 ,batch_l4_winner_table_name
2792 ,batch_l5_winner_table_name
2793 ,batch_wt_winner_table_name
2794 ,batch_winner_table_name
2795 ,batch_unique_winner_table_name
2796 ,batch_mp_winner_table_name || p_worker_id
2797 ,batch_dmc_winner_table_name || p_worker_id
2798 ,multi_level_winning_flag
2799 INTO l_match_target
2800 ,l_umatch_target
2801 ,l_l1_target
2802 ,l_l2_target
2803 ,l_l3_target
2804 ,l_l4_target
2805 ,l_l5_target
2806 ,l_wt_target
2807 ,l_winner_target
2808 ,l_uwinner_target
2809 ,l_mp_winner_target
2810 ,l_dmc_winner_target
2811 ,l_multi_level_winning_flag
2812 FROM jty_trans_usg_pgm_details tup
2813 WHERE tup.source_id = p_source_id
2814 AND tup.trans_type_id = p_trans_id
2815 AND tup.program_name = p_program_name;
2816 EXCEPTION
2817 WHEN NO_DATA_FOUND THEN
2818 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
2819 ' program name : ' || p_program_name;
2820 RAISE;
2821 END;
2822
2823 COMMIT;
2824
2825 IF (l_multi_level_winning_flag <> 'Y') THEN
2826 l_dyn_str :=
2827 ' INSERT INTO ' || l_winner_target || ' i ' ||
2828 ' ( ' ||
2829 ' TRANS_OBJECT_ID ' ||
2830 ' ,TRANS_DETAIL_OBJECT_ID ' ||
2831 ' ,WORKER_ID ' ||
2832 ' ,SOURCE_ID ' ||
2833 ' ,TRANS_OBJECT_TYPE_ID ' ||
2834 ' ,LAST_UPDATE_DATE ' ||
2835 ' ,LAST_UPDATED_BY ' ||
2836 ' ,CREATION_DATE ' ||
2837 ' ,CREATED_BY ' ||
2838 ' ,LAST_UPDATE_LOGIN ' ||
2839 ' ,REQUEST_ID ' ||
2840 ' ,PROGRAM_APPLICATION_ID ' ||
2841 ' ,PROGRAM_ID ' ||
2842 ' ,PROGRAM_UPDATE_DATE ' ||
2843 ' ,TERR_ID ' ||
2844 ' ,ABSOLUTE_RANK ' ||
2845 ' ,TOP_LEVEL_TERR_ID ' ||
2846 ' ,RESOURCE_ID ' ||
2847 ' ,RESOURCE_TYPE ' ||
2848 ' ,GROUP_ID ' ||
2849 ' ,ROLE_ID ' ||
2850 ' ,ROLE ' ||
2851 ' ,PRIMARY_CONTACT_FLAG ' ||
2852 ' ,PERSON_ID ' ||
2853 ' ,ORG_ID ' ||
2854 ' ,TERR_RSC_ID ' ||
2855 ' ,FULL_ACCESS_FLAG ' ||
2856 ' ) ' ||
2857 ' ( ' ||
2858
2859 ' SELECT ' || -- DISTINCT ' ||
2860 ' WT.trans_object_id ' ||
2861 ' , WT.trans_detail_object_id ' ||
2862 ' , :bv_worker_id ' || --p_worker_id ||
2863 ' , :BV1_SOURCE_ID ' ||
2864 ' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
2865 ' , :BV1_LAST_UPDATE_DATE ' ||
2866 ' , :BV1_LAST_UPDATED_BY ' ||
2867 ' , :BV1_CREATION_DATE ' ||
2868 ' , :BV1_CREATED_BY ' ||
2869 ' , :BV1_LAST_UPDATE_LOGIN ' ||
2870 ' , :BV1_REQUEST_ID ' ||
2871 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
2872 ' , :BV1_PROGRAM_ID ' ||
2873 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
2874 ' , WT.terr_id ' ||
2875 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
2876 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
2877 ' , jtr.resource_id ' ||
2878 ' , jtr.resource_type ' ||
2879 ' , jtr.group_id ' ||
2880 ' , inv.role_id ' ||
2881 ' , jtr.role ' ||
2882 ' , jtr.primary_contact_flag ' ||
2883 ' , jtr.PERSON_ID ' ||
2884 ' , jtr.org_id ' ||
2885 ' , jtr.terr_rsc_id ' ||
2886 ' , decode(jtra.trans_access_code, ''FULL_ACCESS'', ''Y'', ''N'') ' ||
2887 ' FROM ( /* WINNERS ILV */ ' ||
2888
2889 ' SELECT ' ||
2890 ' o.trans_object_id ' ||
2891 ' ,o.trans_detail_object_id ' ||
2895 ' ( SELECT ' ||
2892 ' ,o.terr_id ' ||
2893 ' ,o.txn_date ' ||
2894 ' FROM ' ||
2896 ' i.trans_id ' ||
2897 ' ,i.trans_object_id ' ||
2898 ' ,i.trans_detail_object_id ' ||
2899 ' ,i.terr_id ' ||
2900 ' ,i.top_level_terr_id ' ||
2901 ' ,i.txn_date ' ||
2902 ' ,RANK() OVER ( PARTITION BY ' ||
2903 ' i.trans_id ' ||
2904 ' , i.trans_object_id ' ||
2905 ' , i.trans_detail_object_id ' ||
2906 ' , i.top_level_terr_id ' ||
2907 ' ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK ' ||
2908 ' FROM ' || l_match_target || ' i ' ||
2909 ' WHERE i.worker_id = :bv_worker_id ) o ' ||
2910 ' WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id) ' ||
2911 ' ) WT ' ||
2912 ' , jtf_terr_rsc_all jtr ' ||
2913 ' , jtf_terr_rsc_access_all jtra ' ||
2914 ' , jtf_qual_types_all jqta ' ||
2915 ' , (SELECT ' ||
2916 ' max(role_id) role_id ' ||
2917 ' ,role_code role_code ' ||
2918 ' FROM jtf_rs_roles_b ' ||
2919 ' GROUP BY role_code ) inv ' ||
2920 ' WHERE WT.terr_id = jtr.terr_id ' ||
2921 ' AND jtr.end_date_active >= WT.txn_date ' ||
2922 ' AND jtr.start_date_active <= WT.txn_date ' ||
2923 ' AND jtr.resource_type <> ''RS_ROLE'' ' ||
2924 ' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
2925 ' AND jtr.role = inv.role_code(+) ' ||
2926 ' AND jtra.access_type = jqta.name ' ||
2927 ' AND jtra.trans_access_code <> ''NONE'' ' ||
2928 ' AND jqta.qual_type_id = :bv_trans_id ';
2929
2930 BEGIN
2931
2932 EXECUTE IMMEDIATE l_dyn_str USING
2933 p_worker_id /* :bv_worker_id */
2934 , p_source_id /* :BV1_SOURCE_ID */
2935 , p_trans_id /* :BV1_TRANS_OBJECT_TYPE_ID */
2936 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
2937 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
2938 , lp_sysdate /* :BV1_CREATION_DATE */
2939 , G_USER_ID /* :BV1_CREATED_BY */
2940 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
2941 , G_REQUEST_ID /* :BV1_REQUEST_ID */
2942 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
2943 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
2944 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
2945 , p_worker_id /* :bv_worker_id */
2946 , p_trans_id;
2947
2948 COMMIT; -- after modifying table in parallel
2949
2953 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
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',
2954 END IF;
2955 EXCEPTION
2956 WHEN NO_DATA_FOUND THEN
2957 NULL;
2958 END;
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
2968 NULL;
2969 END IF;
2970
2971 IF (l_fnd_schema IS NULL) THEN
2972 RAISE L_SCHEMA_NOTFOUND;
2973 END IF;
2974
2975 /* Get the maximun number of territories in the matching combinations */
2976 l_dyn_str :=
2977 'SELECT max(count(terr_id)) ' ||
2978 'FROM ' || l_match_target || ' ' ||
2979 'WHERE worker_id = :worker_id ' ||
2980 'GROUP BY trans_object_id, trans_detail_object_id';
2981
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
2991 x_msg_data := 'No row in ' || l_match_target || ' for worker_id = ' || p_worker_id;
2992 x_return_status := FND_API.G_RET_STS_SUCCESS;
2993 RETCODE := 0;
2994 x_msg_count := 1;
2995 ERRBUF := null;
2996 RAISE L_NO_MATCH_TERR;
2997 END IF;
2998
2999 /* create a temp table that will contain all the transaction objects */
3000 /* and their matching territories in a single row */
3001 /* drop the table if it alreday exists */
3002 BEGIN
3003 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.drop_table, 'drop table ' || l_mp_winner_target, l_mp_winner_target);
3004 EXCEPTION
3005 when others then
3006 null;
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 */
3016 /* add the columns trans_object_id, trans_details_object_id, txn_date */
3017 l_dyn_str :=
3018 'create table ' || l_mp_winner_target || ' as ( ' ||
3019 ' select trans_object_id, trans_detail_object_id, txn_date, max(rownum) link ';
3020
3021 /* create column for each of the matching territories */
3022 for i IN 1..l_max_terr loop
3023 l_dyn_str := l_dyn_str || ' ,nvl(max(decode(trank, ' || i || ', terr_id, null)), 0) terr_id' || i;
3024 end loop;
3025
3026 /* get the data from match table */
3027 l_dyn_str := l_dyn_str ||
3028 ' from ' ||
3029 ' (select trans_object_id, trans_detail_object_id, terr_id, txn_date, ' ||
3030 ' dense_rank() over(partition by trans_object_id, trans_detail_object_id order by terr_id) trank ' ||
3031 ' from ' || l_match_target ||
3032 ' where worker_id = ' || p_worker_id || ' ) ' ||
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 */
3051 /* combination of matching territories in a single row */
3052 /* drop the table if it alreday exists */
3053 BEGIN
3054 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.drop_table, 'drop table ' || l_dmc_winner_target, l_dmc_winner_target);
3055 EXCEPTION
3056 when others then
3057 null;
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 */
3070 for i in 1..l_max_terr loop
3067 l_dyn_str :=
3068 'create table ' || l_dmc_winner_target || ' as ( ' ||
3069 ' select max(rownum) link ';
3071 l_dyn_str := l_dyn_str ||
3072 ' ,terr_id' || i;
3073 end loop;
3074 l_dyn_str := l_dyn_str || ' from ' || l_mp_winner_target || ' group by terr_id1 ';
3075 for i in 2..l_max_terr loop
3076 l_dyn_str := l_dyn_str ||
3077 ' ,terr_id' || i;
3078 end loop;
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 */
3097 l_dyn_str :=
3098 'insert into ' || l_umatch_target ||
3099 ' (trans_object_id, ' ||
3100 ' trans_detail_object_id, ' ||
3101 ' terr_id, ' ||
3102 ' absolute_rank, ' ||
3103 ' txn_date, ' ||
3104 -- ' LAST_UPDATE_DATE, ' ||
3105 -- ' LAST_UPDATED_BY, ' ||
3106 -- ' CREATION_DATE, ' ||
3107 -- ' CREATED_BY, ' ||
3108 -- ' LAST_UPDATE_LOGIN, ' ||
3109 -- ' REQUEST_ID, ' ||
3110 -- ' PROGRAM_APPLICATION_ID, ' ||
3111 -- ' PROGRAM_ID, ' ||
3112 -- ' PROGRAM_UPDATE_DATE, ' ||
3113 ' worker_id) ( ';
3114
3115 -- SOLIN, bug 5633062
3116 -- move jtf_terr_all out of inline view to avoid
3117 -- database bug
3118 l_dyn_str := l_dyn_str ||
3119 'select ' ||
3120 ' ilv.trans_object_id, ' ||
3121 ' -1, ' || --trans_detail_object_id
3122 ' ilv.terr_id, ' ||
3123 ' jt.absolute_rank, ' ||
3124 ' null, ' ||
3125 p_worker_id ||
3126 ' from (' ;
3127
3128 for i IN 1..l_max_terr loop
3129 l_dyn_str := l_dyn_str ||
3130 'select ' ||
3131 ' a.link trans_object_id, ' ||
3132 -- ' -1 trans_detail_object_id, ' ||
3133 ' a.terr_id' || i || ' terr_id ' ||
3134 -- ' b.absolute_rank, ' ||
3135 'from ' || l_dmc_winner_target || ' a ';
3136 if (i < l_max_terr) then
3137 l_dyn_str := l_dyn_str || ' union ';
3138 end if;
3139 end loop;
3140 l_dyn_str := l_dyn_str || ' ) ilv, ' ||
3141 ' jtf_terr_all jt ' ||
3142 'where ilv.terr_id = jt.terr_id) ';
3143
3144 /* SOLIN, the oritinal code follows: 5633062
3145 for i IN 1..l_max_terr loop
3146 l_dyn_str := l_dyn_str ||
3147 'select ' ||
3148 ' a.link trans_object_id, ' ||
3149 ' -1 trans_detail_object_id, ' ||
3150 ' a.terr_id' || i || ' terr_id, ' ||
3151 ' b.absolute_rank, ' ||
3152 ' null, ' ||
3153 -- ' ''' || lp_sysdate || ''', ' ||
3154 -- ' ' || g_user_id || ', ' ||
3155 -- ' ''' || lp_sysdate || ''', ' ||
3156 -- ' ' || g_user_id || ', ' ||
3157 -- ' ' || g_login_id || ', ' ||
3158 -- ' ' || g_request_id || ', ' ||
3159 -- ' ' || g_appl_id || ', ' ||
3160 -- ' ' || g_program_id || ', ' ||
3161 -- ' ''' || lp_sysdate || ''', ' ||
3162 ' ' || p_worker_id || ' ' ||
3163 'from ' || l_dmc_winner_target || ' a, jtf_terr_all b ' ||
3164 'where a.terr_id' || i || ' = b.terr_id ';
3165 if (i < l_max_terr) then
3166 l_dyn_str := l_dyn_str || ' union ';
3167 end if;
3168 end loop;
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;
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 */
3186 IF (l_max_terr > 32) THEN
3187 l_no_ind_cols := 32;
3188 ELSE
3189 l_no_ind_cols := l_max_terr;
3190 END IF;
3191 l_dyn_str :=
3192 'create index ' || l_dmc_winner_target || '_N1 on ' || l_dmc_winner_target || ' (terr_id1 ';
3193 for i in 2..l_no_ind_cols loop
3194 l_dyn_str := l_dyn_str ||
3195 ' , terr_id' || i;
3196 end loop;
3197 l_dyn_str := l_dyn_str || ' )';
3198
3199 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3203 END IF;
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));
3204
3205 /* Create the index in JTF schema */
3206 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.create_index, l_dyn_str, l_dmc_winner_target || 'N');
3207
3208 /* update the temporary table to maintain the link */
3209 /* between txn objects and unique terr matching combinations */
3210 l_dyn_str :=
3211 'update ' || l_mp_winner_target || ' a ' ||
3212 ' set link = ( ' ||
3213 ' select /*+ use_index(' || l_dmc_winner_target || '_N1) */ link from ' || l_dmc_winner_target || ' b ' ||
3214 ' where a.terr_id1 = b.terr_id1 ';
3215 for i in 2..l_max_terr loop
3216 l_dyn_str := l_dyn_str ||
3217 ' and a.terr_id' || i || ' = b.terr_id' || i || ' ';
3218 end loop;
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;
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 */
3236 Process_Level_Winners (
3237 p_terr_LEVEL_target_tbl => l_L1_target,
3238 p_terr_PARENT_LEVEL_tbl => l_L1_target,
3239 p_UPPER_LEVEL_FROM_ROOT => 1,
3240 p_LOWER_LEVEL_FROM_ROOT => 1,
3241 p_matches_target => l_umatch_target,
3242 p_source_id => p_source_id,
3243 p_run_mode => 'BATCH',
3244 p_date_effective => l_date_effective,
3245 x_return_status => x_return_status,
3246 p_worker_id => p_worker_id
3247 );
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;
3257
3258 COMMIT;
3259
3260 /*
3261 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3262 p_table_name => l_L1_target
3263 , p_percent => p_percent_analyzed
3264 , x_return_status => x_return_status );
3265
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;
3275 */
3276
3277 /* Process second level */
3278 Process_Level_Winners (
3279 p_terr_LEVEL_target_tbl => l_L2_target,
3280 p_terr_PARENT_LEVEL_tbl => l_L1_target,
3281 p_UPPER_LEVEL_FROM_ROOT => 1,
3282 p_LOWER_LEVEL_FROM_ROOT => 2,
3283 p_matches_target => l_umatch_target,
3284 p_source_id => p_source_id,
3285 p_run_mode => 'BATCH',
3286 p_date_effective => l_date_effective,
3287 x_return_status => x_return_status,
3288 p_worker_id => p_worker_id
3289 );
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;
3299
3300 COMMIT;
3301
3302 /*
3303 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3304 p_table_name => l_L2_target
3305 , p_percent => p_percent_analyzed
3306 , x_return_status => x_return_status );
3307
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;
3317 */
3318
3319 /* Process third level */
3320 Process_Level_Winners (
3321 p_terr_LEVEL_target_tbl => l_L3_target,
3322 p_terr_PARENT_LEVEL_tbl => l_L2_target,
3323 p_UPPER_LEVEL_FROM_ROOT => 2,
3327 p_run_mode => 'BATCH',
3324 p_LOWER_LEVEL_FROM_ROOT => 3,
3325 p_matches_target => l_umatch_target,
3326 p_source_id => p_source_id,
3328 p_date_effective => l_date_effective,
3329 x_return_status => x_return_status,
3330 p_worker_id => p_worker_id
3331 );
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;
3341
3342 COMMIT;
3343
3344 /*
3345 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3346 p_table_name => l_L3_target
3347 , p_percent => p_percent_analyzed
3348 , x_return_status => x_return_status );
3349
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;
3359 */
3360
3361 /* Process fourth level */
3362 Process_Level_Winners (
3363 p_terr_LEVEL_target_tbl => l_L4_target,
3364 p_terr_PARENT_LEVEL_tbl => l_L3_target,
3365 p_UPPER_LEVEL_FROM_ROOT => 3,
3366 p_LOWER_LEVEL_FROM_ROOT => 4,
3367 p_matches_target => l_umatch_target,
3368 p_source_id => p_source_id,
3369 p_run_mode => 'BATCH',
3370 p_date_effective => l_date_effective,
3371 x_return_status => x_return_status,
3372 p_worker_id => p_worker_id
3373 );
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;
3383
3384 COMMIT;
3385
3386 /*
3387 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3388 p_table_name => l_L4_target
3389 , p_percent => p_percent_analyzed
3390 , x_return_status => x_return_status );
3391
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;
3401 */
3402
3403 /* Process fifth level */
3404 Process_Level_Winners (
3405 p_terr_LEVEL_target_tbl => l_L5_target,
3406 p_terr_PARENT_LEVEL_tbl => l_L4_target,
3407 p_UPPER_LEVEL_FROM_ROOT => 4,
3408 p_LOWER_LEVEL_FROM_ROOT => 5,
3409 p_matches_target => l_umatch_target,
3410 p_source_id => p_source_id,
3411 p_run_mode => 'BATCH',
3412 p_date_effective => l_date_effective,
3413 x_return_status => x_return_status,
3414 p_worker_id => p_worker_id
3415 );
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;
3425
3426 COMMIT;
3427
3428 /*
3429 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3430 p_table_name => l_L5_target
3431 , p_percent => p_percent_analyzed
3432 , x_return_status => x_return_status );
3433
3434 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3438 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l5_analyze',
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,
3439 x_msg_data);
3440 END IF;
3441 RAISE FND_API.G_EXC_ERROR;
3442 END IF;
3443 */
3444
3445 /* Process final level */
3446 Process_Final_Level_Winners (
3447 p_terr_LEVEL_target_tbl => l_wt_target,
3448 p_terr_L5_target_tbl => l_L5_target,
3449 p_matches_target => l_umatch_target,
3450 p_source_id => p_source_id,
3451 p_run_mode => 'BATCH',
3452 p_date_effective => l_date_effective,
3453 x_return_status => x_return_status,
3454 p_worker_id => p_worker_id
3455 );
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;
3465
3466 COMMIT;
3467
3468 /*
3469 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3470 p_table_name => l_wt_target
3471 , p_percent => p_percent_analyzed
3472 , x_return_status => x_return_status );
3473
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 :=
3492 ' INSERT INTO ' || l_uwinner_target || ' i ' ||
3493 ' ( ' ||
3494 ' TRANS_OBJECT_ID ' ||
3495 ' ,TRANS_DETAIL_OBJECT_ID ' ||
3496 ' ,WORKER_ID ' ||
3497 ' ,LAST_UPDATE_DATE ' ||
3498 ' ,LAST_UPDATED_BY ' ||
3499 ' ,CREATION_DATE ' ||
3500 ' ,CREATED_BY ' ||
3501 ' ,LAST_UPDATE_LOGIN ' ||
3502 ' ,REQUEST_ID ' ||
3503 ' ,PROGRAM_APPLICATION_ID ' ||
3504 ' ,PROGRAM_ID ' ||
3505 ' ,PROGRAM_UPDATE_DATE ' ||
3506 ' ,TERR_ID ' ||
3507 ' ,ABSOLUTE_RANK ' ||
3508 ' ,TOP_LEVEL_TERR_ID ' ||
3509 ' ) ' ||
3510 ' ( ' ||
3511 ' SELECT ' || -- DISTINCT ' ||
3512 ' WINNERS.trans_object_id ' ||
3513 ' , WINNERS.trans_detail_object_id ' ||
3514 ' , :bv_worker_id ' || --p_worker_id ||
3515 ' , :BV1_LAST_UPDATE_DATE ' ||
3516 ' , :BV1_LAST_UPDATED_BY ' ||
3517 ' , :BV1_CREATION_DATE ' ||
3518 ' , :BV1_CREATED_BY ' ||
3519 ' , :BV1_LAST_UPDATE_LOGIN ' ||
3520 ' , :BV1_REQUEST_ID ' ||
3521 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
3522 ' , :BV1_PROGRAM_ID ' ||
3523 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
3524 ' , WINNERS.WIN_terr_id ' ||
3525 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
3526 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
3527 ' FROM ( /* WINNERS ILV */ ' ||
3528 ' SELECT ILV.trans_object_id ' ||
3529 ' , ILV.trans_detail_object_id ' ||
3530 ' , ILV.WIN_TERR_ID ' ||
3531 ' FROM ( SELECT trans_object_id ' ||
3532 ' , trans_detail_object_id ' ||
3533 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3534 ' FROM ' || l_L1_target ||
3535 ' WHERE WORKER_ID = :bv_worker_id ' ||
3536 ' MINUS ' ||
3537 ' SELECT trans_object_id ' ||
3538 ' , trans_detail_object_id ' ||
3539 ' , ul_terr_id WIN_TERR_ID ' ||
3540 ' FROM ' || l_L2_target ||
3541 ' WHERE WORKER_ID = :bv_worker_id ' ||
3542 ' ) ILV ' ||
3543
3544 ' UNION ALL ' ||
3545
3546 ' SELECT ILV.trans_object_id ' ||
3547 ' , ILV.trans_detail_object_id ' ||
3551 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3548 ' , ILV.WIN_TERR_ID ' ||
3549 ' FROM ( SELECT trans_object_id ' ||
3550 ' , trans_detail_object_id ' ||
3552 ' FROM ' || l_L2_target ||
3553 ' WHERE WORKER_ID = :bv_worker_id ' ||
3554 ' MINUS ' ||
3555 ' SELECT trans_object_id ' ||
3556 ' , trans_detail_object_id ' ||
3557 ' , ul_terr_id WIN_TERR_ID ' ||
3558 ' FROM ' || l_L3_target ||
3559 ' WHERE WORKER_ID = :bv_worker_id ' ||
3560 ' ) ILV ' ||
3561
3562 ' UNION ALL ' ||
3563
3564 ' SELECT ILV.trans_object_id ' ||
3565 ' , ILV.trans_detail_object_id ' ||
3566 ' , ILV.WIN_TERR_ID ' ||
3567 ' FROM ( SELECT trans_object_id ' ||
3568 ' , trans_detail_object_id ' ||
3569 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3570 ' FROM ' || l_L3_target ||
3571 ' WHERE WORKER_ID = :bv_worker_id ' ||
3572 ' MINUS ' ||
3573 ' SELECT trans_object_id ' ||
3574 ' , trans_detail_object_id ' ||
3575 ' , ul_terr_id WIN_TERR_ID ' ||
3576 ' FROM ' || l_L4_target ||
3577 ' WHERE WORKER_ID = :bv_worker_id ' ||
3578 ' ) ILV ' ||
3579
3580 ' UNION ALL ' ||
3581
3582 ' SELECT ILV.trans_object_id ' ||
3583 ' , ILV.trans_detail_object_id ' ||
3584 ' , ILV.WIN_TERR_ID ' ||
3585 ' FROM ( SELECT trans_object_id ' ||
3586 ' , trans_detail_object_id ' ||
3587 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3588 ' FROM ' || l_L4_target ||
3589 ' WHERE WORKER_ID = :bv_worker_id ' ||
3590 ' MINUS ' ||
3591 ' SELECT trans_object_id ' ||
3592 ' , trans_detail_object_id ' ||
3593 ' , ul_terr_id WIN_TERR_ID ' ||
3594 ' FROM ' || l_L5_target ||
3595 ' WHERE WORKER_ID = :bv_worker_id ' ||
3596 ' ) ILV ' ||
3597
3598 ' UNION ALL ' ||
3599
3600 ' SELECT ILV.trans_object_id ' ||
3601 ' , ILV.trans_detail_object_id ' ||
3602 ' , ILV.WIN_TERR_ID ' ||
3603 ' FROM ( SELECT trans_object_id ' ||
3604 ' , trans_detail_object_id ' ||
3605 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3606 ' FROM ' || l_L5_target ||
3607 ' WHERE WORKER_ID = :bv_worker_id ' ||
3608 ' MINUS ' ||
3609 ' SELECT trans_object_id ' ||
3610 ' , trans_detail_object_id ' ||
3611 ' , ul_terr_id WIN_TERR_ID ' ||
3612 ' FROM ' || l_WT_target ||
3613 ' WHERE WORKER_ID = :bv_worker_id ' ||
3614 ' ) ILV ' ||
3615
3616 ' UNION ALL ' ||
3617
3618 ' SELECT trans_object_id ' ||
3619 ' , trans_detail_object_id ' ||
3620 ' , WIN_TERR_ID ' ||
3621 ' FROM ' || l_WT_target ||
3622 ' WHERE WORKER_ID = :bv_worker_id ' ||
3623
3624 ' ) WINNERS ' ||
3625 ' ) ';
3626
3627 BEGIN
3628
3629 EXECUTE IMMEDIATE l_dyn_str USING
3630 p_worker_id /* :bv_worker_id */
3631 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
3632 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
3633 , lp_sysdate /* :BV1_CREATION_DATE */
3634 , G_USER_ID /* :BV1_CREATED_BY */
3635 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
3636 , G_REQUEST_ID /* :BV1_REQUEST_ID */
3637 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
3638 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
3639 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
3640 , p_worker_id /* :bv_worker_id */ --1
3641 , p_worker_id /* :bv_worker_id */
3642 , p_worker_id /* :bv_worker_id */
3643 , p_worker_id /* :bv_worker_id */
3644 , p_worker_id /* :bv_worker_id */ --5
3645 , p_worker_id /* :bv_worker_id */
3646 , p_worker_id /* :bv_worker_id */
3647 , p_worker_id /* :bv_worker_id */
3648 , p_worker_id /* :bv_worker_id */
3649 , p_worker_id /* :bv_worker_id */ --10
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',
3658 COMMIT; -- after modifying table in parallel
3655 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
3656 END IF;
3657
3659
3660 EXCEPTION
3661 WHEN NO_DATA_FOUND THEN
3662 NULL;
3663 END;
3664
3665 l_dyn_str :=
3666 ' INSERT INTO ' || l_winner_target || ' i ' ||
3667 ' ( ' ||
3668 ' TRANS_OBJECT_ID ' ||
3669 ' ,TRANS_DETAIL_OBJECT_ID ' ||
3670 ' ,WORKER_ID ' ||
3671 ' ,SOURCE_ID ' ||
3672 ' ,TRANS_OBJECT_TYPE_ID ' ||
3673 ' ,LAST_UPDATE_DATE ' ||
3674 ' ,LAST_UPDATED_BY ' ||
3675 ' ,CREATION_DATE ' ||
3676 ' ,CREATED_BY ' ||
3677 ' ,LAST_UPDATE_LOGIN ' ||
3678 ' ,REQUEST_ID ' ||
3679 ' ,PROGRAM_APPLICATION_ID ' ||
3680 ' ,PROGRAM_ID ' ||
3681 ' ,PROGRAM_UPDATE_DATE ' ||
3682 ' ,TERR_ID ' ||
3683 ' ,ABSOLUTE_RANK ' ||
3684 ' ,TOP_LEVEL_TERR_ID ' ||
3685 ' ,RESOURCE_ID ' ||
3686 ' ,RESOURCE_TYPE ' ||
3687 ' ,GROUP_ID ' ||
3688 ' ,ROLE_ID ' ||
3689 ' ,ROLE ' ||
3690 ' ,PRIMARY_CONTACT_FLAG ' ||
3691 ' ,PERSON_ID ' ||
3692 ' ,ORG_ID ' ||
3693 ' ,TERR_RSC_ID ' ||
3694 ' ,FULL_ACCESS_FLAG ' ||
3695 ' ) ' ||
3696 ' ( ' ||
3697 ' SELECT ' || -- DISTINCT ' ||
3698 ' WINNERS.trans_object_id ' ||
3699 ' , WINNERS.trans_detail_object_id ' ||
3700 ' , :bv_worker_id ' || --p_worker_id ||
3701 ' , :BV1_SOURCE_ID ' ||
3702 ' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
3703 ' , :BV1_LAST_UPDATE_DATE ' ||
3704 ' , :BV1_LAST_UPDATED_BY ' ||
3705 ' , :BV1_CREATION_DATE ' ||
3706 ' , :BV1_CREATED_BY ' ||
3707 ' , :BV1_LAST_UPDATE_LOGIN ' ||
3708 ' , :BV1_REQUEST_ID ' ||
3709 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
3710 ' , :BV1_PROGRAM_ID ' ||
3711 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
3712 ' , WINNERS.terr_id ' ||
3713 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
3714 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
3715 ' , jtr.resource_id ' ||
3716 ' , jtr.resource_type ' ||
3717 ' , jtr.group_id ' ||
3718 ' , inv.role_id ' ||
3719 ' , jtr.role ' ||
3720 ' , jtr.primary_contact_flag ' ||
3721 ' , jtr.PERSON_ID ' ||
3722 ' , jtr.org_id ' ||
3723 ' , jtr.terr_rsc_id ' ||
3724 ' , decode(jtra.trans_access_code, ''FULL_ACCESS'', ''Y'', ''N'') ' ||
3725 ' FROM ( /* WINNERS ILV */ ' ||
3726 ' SELECT a.trans_object_id ' ||
3727 ' , a.trans_detail_object_id ' ||
3728 ' , b.TERR_ID ' ||
3729 ' , a.txn_date ' ||
3730 ' FROM ' || l_mp_winner_target || ' a, ' || l_uwinner_target || ' b ' ||
3734 ' , jtf_terr_rsc_all jtr ' ||
3731 ' WHERE b.WORKER_ID = :bv_worker_id ' ||
3732 ' AND a.link = b.trans_object_id ' ||
3733 ' ) WINNERS ' ||
3735 ' , jtf_terr_rsc_access_all jtra ' ||
3736 ' , jtf_qual_types_all jqta ' ||
3737 ' , (SELECT ' ||
3738 ' max(role_id) role_id ' ||
3739 ' ,role_code role_code ' ||
3740 ' FROM jtf_rs_roles_b ' ||
3741 ' GROUP BY role_code ) inv ' ||
3742 ' WHERE WINNERS.terr_id = jtr.terr_id ' ||
3743 ' AND jtr.end_date_active >= WINNERS.txn_date ' ||
3744 ' AND jtr.start_date_active <= WINNERS.txn_date ' ||
3745 ' AND jtr.resource_type <> ''RS_ROLE'' ' ||
3746 ' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
3747 ' AND jtr.role = inv.role_code(+) ' ||
3748 ' AND jtra.access_type = jqta.name ' ||
3749 ' AND jtra.trans_access_code <> ''NONE'' ' ||
3750 ' AND jqta.qual_type_id = :bv_trans_id ' ||
3751 ' ) ';
3752
3753 BEGIN
3754
3755 EXECUTE IMMEDIATE l_dyn_str USING
3756 p_worker_id /* :bv_worker_id */
3757 , p_source_id /* :BV1_SOURCE_ID */
3758 , p_trans_id /* :BV1_TRANS_OBJECT_TYPE_ID */
3759 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
3760 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
3761 , lp_sysdate /* :BV1_CREATION_DATE */
3762 , G_USER_ID /* :BV1_CREATED_BY */
3763 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
3764 , G_REQUEST_ID /* :BV1_REQUEST_ID */
3765 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
3766 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
3767 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
3768 , p_worker_id /* :bv_worker_id */ --1
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
3778
3779 EXCEPTION
3780 WHEN NO_DATA_FOUND THEN
3781 NULL;
3782 END;
3783 END IF; /* end IF (l_multi_level_winning_flag <> 'Y') */
3784
3785 /* Analyze Winners table */
3786 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3787 p_table_name => l_winner_target
3788 , p_percent => p_percent_analyzed
3789 , x_return_status => x_return_status );
3790
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;
3800
3801 /* Program completed successfully */
3802 ERRBUF := null;
3803 x_return_status := FND_API.G_RET_STS_SUCCESS;
3804 RETCODE := 0;
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
3814 WHEN L_SCHEMA_NOTFOUND THEN
3815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3816 x_msg_data := 'Schema name corresponding to the FND application not found';
3817 RETCODE := 2;
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
3827 x_return_status := FND_API.G_RET_STS_ERROR ;
3828 RETCODE := 2;
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
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
3845 x_return_status := FND_API.G_RET_STS_ERROR ;
3846 RETCODE := 2;
3847 x_msg_count := 1;
3848 ERRBUF := x_msg_data;
3849 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3853 END IF;
3850 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3851 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.g_exc_error',
3852 x_msg_data);
3854
3855 WHEN OTHERS THEN
3856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3857 x_msg_data := SQLCODE || ' : ' || SQLERRM;
3858 x_msg_count := 1;
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;
3868
3869
3870 -- ***************************************************
3871 -- API Specifications
3872 -- ***************************************************
3873 -- api name : process_match
3874 -- type : private.
3875 -- function :
3876 -- pre-reqs :
3877 -- notes: API designed to get the matching territories for the
3878 -- transaction objs, it supports multiple worker architecture
3879 --
3880 PROCEDURE process_match
3881 ( p_source_id IN NUMBER,
3882 p_trans_id IN NUMBER,
3883 p_program_name IN VARCHAR2,
3884 p_mode IN VARCHAR2,
3885 p_percent_analyzed IN NUMBER,
3886 p_worker_id IN NUMBER,
3887 x_return_status OUT NOCOPY VARCHAR2,
3888 x_msg_count OUT NOCOPY NUMBER,
3889 x_msg_data OUT NOCOPY VARCHAR2,
3890 ERRBUF OUT NOCOPY VARCHAR2,
3891 RETCODE OUT NOCOPY VARCHAR2
3892 ) AS
3893
3894 l_trans_target VARCHAR2(40);
3895 l_match_target VARCHAR2(40);
3896 l_sql_stmt VARCHAR2(200);
3897 l_no_of_records NUMBER;
3898 l_match_sql VARCHAR2(32767);
3899 l_sysdate DATE;
3900
3901 CURSOR c_get_qualrel_prod(cl_source_id number, cl_trans_id number) IS
3902 SELECT jtqp.relation_product
3903 FROM jtf_tae_qual_products jtqp
3904 WHERE jtqp.source_id = cl_source_id
3905 AND jtqp.trans_object_type_id = cl_trans_id
3906 ORDER BY jtqp.relation_product DESC;
3907
3908 CURSOR c_dea_get_qualrel_prod(cl_source_id number, cl_trans_id number) IS
3909 SELECT jtqp.attr_relation_product
3910 FROM jty_dea_attr_products jtqp
3911 WHERE jtqp.source_id = cl_source_id
3912 AND jtqp.trans_type_id = cl_trans_id
3913 ORDER BY jtqp.attr_relation_product DESC;
3914
3915 BEGIN
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;
3925 l_sysdate := SYSDATE;
3926
3927 BEGIN
3928 SELECT decode(p_mode, 'TOTAL', tup.batch_trans_table_name
3929 , 'INCREMENTAL', tup.batch_nm_trans_table_name
3930 , 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
3931 ,batch_match_table_name
3932 INTO l_trans_target
3933 ,l_match_target
3934 FROM jty_trans_usg_pgm_details tup
3935 WHERE tup.source_id = p_source_id
3936 AND tup.trans_type_id = p_trans_id
3937 AND tup.program_name = p_program_name;
3938 EXCEPTION
3939 WHEN NO_DATA_FOUND THEN
3940 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
3941 ' program name : ' || p_program_name;
3942 RAISE;
3943 END;
3944
3945 l_sql_stmt := 'SELECT COUNT(*) FROM ' || l_trans_target || ' WHERE worker_id = :bv_worker_id';
3946 EXECUTE IMMEDIATE l_sql_stmt INTO l_no_of_records USING p_worker_id;
3947
3948 IF (l_no_of_records <= 0) THEN
3949 x_msg_data := 'No row in ' || l_trans_target || ' for worker_id = ' || p_worker_id;
3950 x_return_status := FND_API.G_RET_STS_SUCCESS;
3951 RETCODE := 0;
3952 x_msg_count := 1;
3953 ERRBUF := null;
3954 RAISE FND_API.G_EXC_ERROR;
3955 END IF;
3956
3957 IF (p_mode = 'DATE EFFECTIVE') THEN
3958
3959 FOR jtf_csr IN c_dea_get_qualrel_prod(p_source_id, p_trans_id) LOOP
3960 BEGIN
3961 SELECT batch_dea_match_sql
3962 INTO l_match_sql
3963 FROM jty_dea_attr_products_sql
3964 WHERE source_id = p_source_id
3965 AND trans_type_id = p_trans_id
3966 AND program_name = p_program_name
3967 AND attr_relation_product = jtf_csr.attr_relation_product;
3968 EXCEPTION
3969 WHEN NO_DATA_FOUND THEN
3970 x_msg_data := 'No matching SQL found corresponding to source : ' || p_source_id || ' trans : ' || p_trans_id ||
3971 ' Program name : ' || p_program_name || ' relation product : ' || jtf_csr.attr_relation_product;
3972 RAISE;
3973 END;
3974
3975 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
3979 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3976 g_appl_id, g_program_id, l_sysdate, p_worker_id;
3977
3978 -- debug message
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
3986 ELSIF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
3987
3988 FOR jtf_csr IN c_get_qualrel_prod(p_source_id, p_trans_id) LOOP
3989 BEGIN
3990 SELECT decode(p_mode, 'TOTAL', batch_match_sql, 'INCREMENTAL', batch_nm_match_sql)
3991 INTO l_match_sql
3992 FROM jty_tae_attr_products_sql
3993 WHERE source_id = p_source_id
3994 AND trans_type_id = p_trans_id
3995 AND program_name = p_program_name
3996 AND attr_relation_product = jtf_csr.relation_product;
3997 EXCEPTION
3998 WHEN NO_DATA_FOUND THEN
3999 x_msg_data := 'No matching SQL found corresponding to source : ' || p_source_id || ' trans : ' || p_trans_id ||
4000 ' Program name : ' || p_program_name || ' relation product : ' || jtf_csr.relation_product;
4004 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
4001 RAISE;
4002 END;
4003
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
4014 END IF;
4015
4016 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
4017 p_table_name => l_match_target
4018 , p_percent => p_percent_analyzed
4019 , x_return_status => x_return_status );
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;
4029 RETCODE := 2;
4030 x_msg_count := 1;
4031 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for table : ' || l_match_target;
4032 errbuf := x_msg_data;
4033 END IF;
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
4043 WHEN NO_DATA_FOUND THEN
4044 x_return_status := FND_API.G_RET_STS_ERROR ;
4045 RETCODE := 2;
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
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
4062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4063 x_msg_data := SQLCODE || ' : ' || SQLERRM;
4064 x_msg_count := 1;
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;
4074
4075 -- ***************************************************
4076 -- API Specifications
4077 -- ***************************************************
4078 -- api name : get_winners
4079 -- type : public.
4080 -- function :
4081 -- pre-reqs :
4082 -- notes: API designed to get the winning territories for the
4083 -- transaction objs, it supports multiple worker architecture
4084 --
4085 PROCEDURE get_winners
4086 ( p_api_version_number IN NUMBER,
4087 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4088 p_source_id IN NUMBER,
4089 p_trans_id IN NUMBER,
4090 p_program_name IN VARCHAR2,
4091 p_mode IN VARCHAR2,
4092 p_percent_analyzed IN NUMBER,
4093 p_worker_id IN NUMBER,
4094 x_return_status OUT NOCOPY VARCHAR2,
4095 x_msg_count OUT NOCOPY NUMBER,
4096 x_msg_data OUT NOCOPY VARCHAR2,
4097 ERRBUF OUT NOCOPY VARCHAR2,
4098 RETCODE OUT NOCOPY VARCHAR2
4099 ) AS
4100
4101 l_api_name CONSTANT VARCHAR2(30) := 'get_winners';
4102 l_api_version_number CONSTANT NUMBER := 1.0;
4103
4104 BEGIN
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. */
4114 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4115 p_api_version_number,
4116 l_api_name,
4117 G_PKG_NAME) THEN
4118
4119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4120 END IF;
4121
4122 /* Initialize message list if p_init_msg_list is set to TRUE. */
4123 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4124 FND_MSG_PUB.initialize;
4125 END IF;
4126
4127 x_return_status := FND_API.G_RET_STS_SUCCESS;
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 ||
4137 /* Find out the matching territories corresponding to the txn objects */
4134 ' Mode : ' || p_mode || ' Worker : ' || p_worker_id);
4135 END IF;
4136
4138 process_match (
4139 p_source_id => p_source_id,
4140 p_trans_id => p_trans_id,
4141 p_program_name => p_program_name,
4142 p_mode => p_mode,
4143 p_percent_analyzed => p_percent_analyzed,
4144 p_worker_id => p_worker_id,
4145 x_return_status => x_return_status,
4146 x_msg_count => x_msg_count,
4147 x_msg_data => x_msg_data,
4148 errbuf => errbuf,
4149 retcode => retcode);
4150
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
4160 RAISE FND_API.G_EXC_ERROR;
4161 END IF;
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 */
4171 process_winners (
4172 p_source_id => p_source_id,
4173 p_trans_id => p_trans_id,
4174 p_program_name => p_program_name,
4175 p_mode => p_mode,
4176 p_percent_analyzed => p_percent_analyzed,
4177 p_worker_id => p_worker_id,
4178 x_return_status => x_return_status,
4179 x_msg_count => x_msg_count,
4180 x_msg_data => x_msg_data,
4181 errbuf => errbuf,
4182 retcode => retcode);
4183
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
4193 RAISE FND_API.G_EXC_ERROR;
4194 END IF;
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
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
4212 x_return_status := FND_API.G_RET_STS_ERROR ;
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
4222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4223 x_msg_data := SQLCODE || ' : ' || SQLERRM;
4224 x_msg_count := 1;
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;
4234
4235 END JTY_ASSIGN_BULK_PUB;