[Home] [Help]
PACKAGE BODY: APPS.JTY_ASSIGN_BULK_PUB
Source
4 -- Start of Comments
1 Package Body JTY_ASSIGN_BULK_PUB AS
2 /* $Header: jtfyaeab.pls 120.36 2011/04/26 08:40:15 sseshaiy ship $ */
3 ---------------------------------------------------------------------
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 PROCEDURE jty_log(p_log_level IN NUMBER
41 ,p_module IN VARCHAR2
42 ,p_message IN VARCHAR2)
43 IS
44 pragma autonomous_transaction;
45 BEGIN
46 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
47 FND_LOG.string(p_log_level, p_module, p_message);
48 commit;
49 END IF;
50 END;
51
52 -- ***************************************************
53 -- API Body Definitions
54 -- ***************************************************
55
56 -- ***************************************************
57 -- API Specifications
58 -- ***************************************************
59 -- api name : insert_nm_trans_data
60 -- type : private.
61 -- function : inserts the transaction objects into NM_TRANS table
62 -- pre-reqs :
63 -- notes:
64 --
65 PROCEDURE Insert_NM_Trans_Data
66 ( p_source_id IN NUMBER,
67 p_trans_id IN NUMBER,
68 p_program_name IN VARCHAR2,
69 p_request_id IN NUMBER,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2,
73 ERRBUF OUT NOCOPY VARCHAR2,
74 RETCODE OUT NOCOPY VARCHAR2
75 )
76 AS
77 l_trans_target VARCHAR2(30);
78 l_insert_stmt VARCHAR2(3000);
79 l_select_stmt VARCHAR2(3000);
80 first_time BOOLEAN;
81 l_owner VARCHAR2(30);
82 l_indent VARCHAR2(30);
83 l_status VARCHAR2(30);
84 l_industry VARCHAR2(30);
85 l_seeded_sql VARCHAR2(32767);
86 l_final_sql VARCHAR2(32767);
87 l_sysdate DATE;
88
89
90 CURSOR c1(p_table_name IN VARCHAR2, p_owner IN VARCHAR2) is
91 SELECT column_name
92 FROM all_tab_columns
93 WHERE table_name = p_table_name
94 AND owner = p_owner
95 AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
96 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
97 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
98
99 L_SCHEMA_NOTFOUND EXCEPTION;
100 L_SEED_DATA_NOTFOUND EXCEPTION;
101
102 BEGIN
103
104 -- debug message
105 jty_log(FND_LOG.LEVEL_PROCEDURE,
106 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.begin',
107 'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
108
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110 l_sysdate := SYSDATE;
111
112 /* Get the NM_TRANS table name */
113 BEGIN
114 SELECT tup.batch_nm_trans_table_name
115 INTO l_trans_target
116 FROM jty_trans_usg_pgm_details tup
117 WHERE tup.source_id = p_source_id
118 AND tup.trans_type_id = p_trans_id
119 AND tup.program_name = p_program_name;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
123 ' program name : ' || p_program_name;
124 RAISE;
125 END;
126
127 BEGIN
128 SELECT incr_reassign_sql
129 INTO l_seeded_sql
130 FROM jty_trans_usg_pgm_sql tus
131 WHERE tus.source_id = p_source_id
132 AND tus.trans_type_id = p_trans_id
133 AND tus.program_name = p_program_name
134 AND tus.enabled_flag = 'Y';
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 x_msg_data := 'No active row in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
138 ' program name : ' || p_program_name;
139 RAISE;
140 END;
141
142 IF (l_trans_target IS NULL) THEN
143 x_msg_data := 'No trans table name in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' ||
144 p_trans_id || ' program name : ' || p_program_name;
145 RAISE L_SEED_DATA_NOTFOUND;
146 END IF;
147
148 IF (l_seeded_sql IS NULL) THEN
149 x_msg_data := 'No active transaction sql in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' ||
150 p_trans_id || ' program name : ' || p_program_name;
151 RAISE L_SEED_DATA_NOTFOUND;
152 END IF;
153
154 /* Initialize local variables */
155 first_time := TRUE;
156 l_indent := ' ';
157
158 /* Get the schema name corresponding to JTF application */
159 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
160 NULL;
161 END IF;
162
163 IF (l_owner IS NULL) THEN
164 RAISE L_SCHEMA_NOTFOUND;
165 END IF;
166
167 /* Form the insert statement to insert transaction objects into TRANS table */
168 l_insert_stmt := 'INSERT /*+ APPEND PARALLEL(' || l_trans_target || ') */ INTO ' || l_trans_target || '(';
169 l_select_stmt := '(SELECT ';
170
171 FOR column_names in c1(l_trans_target, l_owner) LOOP
172 IF (first_time) THEN
173 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
174 l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
175 first_time := FALSE;
176 ELSE
177 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
178 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
179 END IF;
180 END LOOP;
181
182 /* Standard WHO columns */
183 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
184 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
185 g_new_line || l_indent || ',CREATION_DATE ' ||
186 g_new_line || l_indent || ',CREATED_BY ' ||
187 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
188 g_new_line || l_indent || ',REQUEST_ID ' ||
189 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
190 g_new_line || l_indent || ',PROGRAM_ID ' ||
191 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
192 g_new_line || l_indent || ',WORKER_ID ' ||
193 g_new_line || l_indent || ',TXN_DATE ' ||
194 g_new_line || ')';
195
196 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
197 g_new_line || l_indent || ',''' || g_user_id || '''' ||
198 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
199 g_new_line || l_indent || ',''' || g_user_id || '''' ||
200 g_new_line || l_indent || ',''' || g_login_id || '''' ||
201 g_new_line || l_indent || ',''' || p_request_id || '''' ||
202 g_new_line || l_indent || ',''' || g_appl_id || '''' ||
203 g_new_line || l_indent || ',''' || g_program_id || '''' ||
204 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
205 g_new_line || l_indent || ',1' ||
206 g_new_line || l_indent || ',''' || l_sysdate || '''';
207
208 l_final_sql := l_insert_stmt || l_select_stmt ||
209 g_new_line || 'FROM ( ' ||
210 g_new_line || l_seeded_sql ||
211 g_new_line || ' ) ) ';
212
213 -- debug message
214 jty_log(FND_LOG.LEVEL_STATEMENT,
215 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.final_sql',
216 substr('Insert satement : ' || l_final_sql, 1, 4000));
217
218 /* commit is executed to execute parallel dml in single transaction */
219 commit;
220 EXECUTE IMMEDIATE 'alter session enable parallel dml';
221 EXECUTE IMMEDIATE l_final_sql;
222 commit;
223 EXECUTE IMMEDIATE 'alter session disable parallel dml';
224
225 -- debug message
226 jty_log(FND_LOG.LEVEL_STATEMENT,
227 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.num_rows',
228 'Number of rows inserted : ' || SQL%ROWCOUNT);
229
230 -- debug message
231 jty_log(FND_LOG.LEVEL_PROCEDURE,
232 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.end',
233 'End of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
234
235 EXCEPTION
236 WHEN L_SEED_DATA_NOTFOUND THEN
237 x_return_status := FND_API.G_RET_STS_ERROR ;
238 RETCODE := 2;
239 x_msg_count := 1;
240 ERRBUF := x_msg_data;
241 jty_log(FND_LOG.LEVEL_EXCEPTION,
242 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.l_seed_data_notfound',
243 x_msg_data);
244
245 WHEN L_SCHEMA_NOTFOUND THEN
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
247 RETCODE := 2;
248 x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_nm_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
249 ERRBUF := x_msg_data;
250 jty_log(FND_LOG.LEVEL_EXCEPTION,
251 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.l_schema_notfound',
252 x_msg_data);
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 jty_log(FND_LOG.LEVEL_EXCEPTION,
260 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.no_data_found',
261 x_msg_data);
262
263 WHEN OTHERS THEN
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
265 x_msg_data := SQLCODE || ' : ' || SQLERRM;
266 x_msg_count := 1;
267 ERRBUF := x_msg_data;
268 jty_log(FND_LOG.LEVEL_EXCEPTION,
269 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.other',
270 substr(x_msg_data, 1, 4000));
271
272 END Insert_NM_Trans_Data;
273
274
275 -- ***************************************************
276 -- API Specifications
277 -- ***************************************************
278 -- api name : insert_trans_data
279 -- type : private.
280 -- function : inserts the transaction objects into TRANS table
281 -- pre-reqs :
282 -- notes:
283 --
284 PROCEDURE Insert_Trans_Data
285 ( p_source_id IN NUMBER,
286 p_trans_id IN NUMBER,
287 p_program_name IN VARCHAR2,
288 p_mode IN VARCHAR2,
289 p_where IN VARCHAR2,
290 p_no_of_workers IN NUMBER,
291 p_request_id IN NUMBER,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_msg_count OUT NOCOPY NUMBER,
294 x_msg_data OUT NOCOPY VARCHAR2,
295 ERRBUF OUT NOCOPY VARCHAR2,
296 RETCODE OUT NOCOPY VARCHAR2,
297 p_oic_mode IN VARCHAR2 DEFAULT 'NOOIC'
298 )
299 AS
300 l_trans_target VARCHAR2(30);
301 l_insert_stmt VARCHAR2(3000);
302 l_select_stmt VARCHAR2(3000);
303 first_time BOOLEAN;
304 l_owner VARCHAR2(30);
305 l_indent VARCHAR2(30);
306 l_status VARCHAR2(30);
307 l_industry VARCHAR2(30);
308 l_seeded_sql VARCHAR2(32767);
309 l_final_sql VARCHAR2(32767);
310 l_plsql_block VARCHAR2(32767);
311 l_sysdate DATE;
312
313
314 CURSOR c1(p_table_name IN VARCHAR2, p_owner IN VARCHAR2) is
315 SELECT column_name
316 FROM all_tab_columns
317 WHERE table_name = p_table_name
318 AND owner = p_owner
319 AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
320 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
321 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
322
323 L_SCHEMA_NOTFOUND EXCEPTION;
324 L_SEED_DATA_NOTFOUND EXCEPTION;
325 L_INVALID_WORKERS EXCEPTION;
326
327 BEGIN
328
329 -- debug message
330 jty_log(FND_LOG.LEVEL_PROCEDURE,
331 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.begin',
332 'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
333
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335 l_sysdate := SYSDATE;
336
337 /* Number of workers is restricted from 1 to 10 */
338 IF (p_oic_mode = 'NOOIC') THEN
339 IF ( (p_no_of_workers > 10 or p_no_of_workers < 1)) THEN
340 RAISE L_INVALID_WORKERS;
341 END IF;
342 ELSIF (p_oic_mode = 'INSERT') THEN
343 IF ( (p_no_of_workers < 1)) THEN
344 RAISE L_INVALID_WORKERS;
345 END IF;
346 END IF;
347
348 /* Get the TRANS table name and active transaction type batch SQL */
349 BEGIN
350 SELECT decode(p_mode, 'TOTAL', tup.batch_trans_table_name
351 , 'INCREMENTAL', tup.batch_nm_trans_table_name
352 , 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
353 INTO l_trans_target
354 FROM jty_trans_usg_pgm_details tup
355 WHERE tup.source_id = p_source_id
356 AND tup.trans_type_id = p_trans_id
357 AND tup.program_name = p_program_name;
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
361 ' program name : ' || p_program_name;
362 RAISE;
363 END;
364
365 BEGIN
366 SELECT decode(p_mode, 'TOTAL', tus.batch_total_sql
367 , 'INCREMENTAL', tus.batch_incr_sql
368 , 'DATE EFFECTIVE', decode(tus.use_total_for_dea_flag, 'Y', tus.batch_total_sql, tus.batch_dea_sql))
369 INTO l_seeded_sql
370 FROM jty_trans_usg_pgm_sql tus
371 WHERE tus.source_id = p_source_id
372 AND tus.trans_type_id = p_trans_id
373 AND tus.program_name = p_program_name
374 AND tus.enabled_flag = 'Y';
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 x_msg_data := 'No active row in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
378 ' program name : ' || p_program_name;
379 RAISE;
380 END;
381
382 IF (l_trans_target IS NULL) THEN
383 x_msg_data := 'No trans table name in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' ||
384 p_trans_id || ' program name : ' || p_program_name;
385 RAISE L_SEED_DATA_NOTFOUND;
386 END IF;
387
388 IF (l_seeded_sql IS NULL) THEN
389 x_msg_data := 'No active transaction sql in jty_trans_usg_pgm_sql corresponding to usage : ' || p_source_id || ' transaction : ' ||
390 p_trans_id || ' program name : ' || p_program_name;
391 RAISE L_SEED_DATA_NOTFOUND;
392 END IF;
393
394 -- debug message
395 jty_log(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 jty_log(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 jty_log(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
407 /* Initialize local variables */
408 first_time := TRUE;
409 l_indent := ' ';
410
411 /* Get the schema name corresponding to JTF application */
412 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
413 NULL;
414 END IF;
415
416 IF (l_owner IS NULL) THEN
417 RAISE L_SCHEMA_NOTFOUND;
418 END IF;
419
420 /* Form the insert statement to insert transaction objects into TRANS table */
421 IF p_oic_mode = 'INSERT'
422 THEN
423 l_insert_stmt := 'INSERT INTO ' || l_trans_target || '(';
424 l_select_stmt := '(SELECT ';
425 ELSE
426 l_insert_stmt := 'INSERT /*+ APPEND PARALLEL(' || l_trans_target || ') */ INTO ' || l_trans_target || '(';
427 l_select_stmt := '(SELECT /*+ PARALLEL */';
428 END IF;
429
430
431 FOR column_names in c1(l_trans_target, l_owner) LOOP
432 IF (first_time) THEN
433 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
434 l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
435 first_time := FALSE;
436 ELSE
437 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
438 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
439 END IF;
440 END LOOP;
441
442 /* Standard WHO columns */
443 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
444 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
445 g_new_line || l_indent || ',CREATION_DATE ' ||
446 g_new_line || l_indent || ',CREATED_BY ' ||
447 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
448 g_new_line || l_indent || ',REQUEST_ID ' ||
449 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
450 g_new_line || l_indent || ',PROGRAM_ID ' ||
451 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
452 g_new_line || l_indent || ',WORKER_ID ' ||
453 g_new_line || l_indent || ',TXN_DATE ' ||
454 g_new_line || ')';
455
456 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
457 g_new_line || l_indent || ',''' || g_user_id || '''' ||
458 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
459 g_new_line || l_indent || ',''' || g_user_id || '''' ||
460 g_new_line || l_indent || ',''' || g_login_id || '''' ||
461 g_new_line || l_indent || ',''' || p_request_id || '''' ||
462 g_new_line || l_indent || ',''' || g_appl_id || '''' ||
463 g_new_line || l_indent || ',''' || g_program_id || '''' ||
464 g_new_line || l_indent || ',''' || l_sysdate || '''';
465
466 IF (p_mode = 'INCREMENTAL') THEN
467 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',1';
468 ELSE
469 IF p_oic_mode = 'NOOIC'
470 THEN
471 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',mod(trans_object_id ,' || p_no_of_workers || ') + 1';
472 ELSIF p_oic_mode = 'INSERT'
473 THEN
474 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',mod(floor(trans_object_id/1000) ,' || p_no_of_workers || ') + 1';
475 END IF;
476 END IF;
477
478 IF (p_mode = 'DATE EFFECTIVE') THEN
479 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',txn_date';
480 ELSE
481 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''';
482 END IF;
483
484 l_final_sql := l_insert_stmt || l_select_stmt ||
485 g_new_line || 'FROM ( ' ||
486 g_new_line || l_seeded_sql ||
487 g_new_line || ' ) ';
488
489 /* Append the where clause , passed by the customer, to the seeded SQL */
490 IF (p_where IS NOT NULL) THEN
491 l_final_sql := l_final_sql || ' ' || p_where;
492 END IF;
493 l_final_sql := l_final_sql || ' ) ';
494
495 -- debug message
496 jty_log(FND_LOG.LEVEL_STATEMENT,
497 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.final_sql',
498 substr('Insert satement : ' || l_final_sql, 1, 4000));
499
500 /* commit is executed to execute parallel dml in single transaction */
501 commit;
502 EXECUTE IMMEDIATE 'alter session enable parallel dml';
503 /* Insert all the transaction objects into the TRANS table */
504 EXECUTE IMMEDIATE l_final_sql;
505 commit;
506 EXECUTE IMMEDIATE 'alter session disable parallel dml';
507
508 -- debug message
509 jty_log(FND_LOG.LEVEL_STATEMENT,
510 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.num_rows',
511 'Number of rows inserted : ' || SQL%ROWCOUNT);
512
513 -- debug message
514 jty_log(FND_LOG.LEVEL_PROCEDURE,
515 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.end',
516 'End of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
517
518 EXCEPTION
519 WHEN L_INVALID_WORKERS THEN
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
521 RETCODE := 2;
522 x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: Invalid number of workers : Valid range from 1 - 10';
523 ERRBUF := x_msg_data;
524 jty_log(FND_LOG.LEVEL_EXCEPTION,
525 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_invalid_workers',
526 x_msg_data);
527
528 WHEN L_SEED_DATA_NOTFOUND THEN
529 x_return_status := FND_API.G_RET_STS_ERROR ;
530 RETCODE := 2;
531 x_msg_count := 1;
532 ERRBUF := x_msg_data;
533 jty_log(FND_LOG.LEVEL_EXCEPTION,
534 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_seed_data_notfound',
535 x_msg_data);
536
537 WHEN L_SCHEMA_NOTFOUND THEN
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
539 RETCODE := 2;
540 x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
541 ERRBUF := x_msg_data;
542 jty_log(FND_LOG.LEVEL_EXCEPTION,
543 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_schema_notfound',
544 x_msg_data);
545
546 WHEN NO_DATA_FOUND THEN
547 x_return_status := FND_API.G_RET_STS_ERROR ;
548 RETCODE := 2;
549 x_msg_count := 1;
550 ERRBUF := x_msg_data;
551 jty_log(FND_LOG.LEVEL_EXCEPTION,
552 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.no_data_found',
553 x_msg_data);
554
555 WHEN OTHERS THEN
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
557 x_msg_data := SQLCODE || ' : ' || SQLERRM;
558 x_msg_count := 1;
559 ERRBUF := x_msg_data;
560 jty_log(FND_LOG.LEVEL_EXCEPTION,
561 'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.other',
562 substr(x_msg_data, 1, 4000));
563
564 END Insert_Trans_Data;
565
566
567 -- ***************************************************
568 -- API Specifications
569 -- ***************************************************
570 -- api name : Clear_trans_data
571 -- type : public.
572 -- function : Truncate Trans Table, and Drop_TAE_TRANS_Indexes
573 -- pre-reqs :
574 -- notes:
575 --
576 PROCEDURE Clear_Trans_Data
577 ( p_source_id IN NUMBER,
578 p_trans_id IN NUMBER,
579 p_program_name IN VARCHAR2,
580 p_mode IN VARCHAR2,
581 p_request_id IN NUMBER,
582 x_return_status OUT NOCOPY VARCHAR2,
583 x_msg_count OUT NOCOPY NUMBER,
584 x_msg_data OUT NOCOPY VARCHAR2,
585 ERRBUF OUT NOCOPY VARCHAR2,
586 RETCODE OUT NOCOPY VARCHAR2
587 )
588 AS
589 l_trans_target VARCHAR2(30);
590 l_match_target VARCHAR2(30);
591 l_umatch_target VARCHAR2(30);
592 l_winner_target VARCHAR2(30);
593 l_uwinner_target VARCHAR2(30);
594 l_L1_target VARCHAR2(30);
595 l_L2_target VARCHAR2(30);
596 l_L3_target VARCHAR2(30);
597 l_L4_target VARCHAR2(30);
598 l_L5_target VARCHAR2(30);
599 l_WT_target VARCHAR2(30);
600
601 l_dummy NUMBER;
602 BEGIN
603
604 -- debug message
605 jty_log(FND_LOG.LEVEL_PROCEDURE,
606 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.begin',
607 'Start of the procedure JTY_ASSIGN_BULK_PUB.clear_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
608
609 /* Get the temp tables names corresponding to the usage, transaction type and program name */
610 SELECT
611 decode(p_mode, 'TOTAL', tup.batch_trans_table_name
612 , 'INCREMENTAL', tup.batch_nm_trans_table_name
613 , 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
614 ,tup.batch_match_table_name
615 ,tup.batch_unique_match_table_name
616 ,tup.batch_winner_table_name
617 ,tup.batch_unique_winner_table_name
618 ,tup.batch_l1_winner_table_name
619 ,tup.batch_l2_winner_table_name
620 ,tup.batch_l3_winner_table_name
621 ,tup.batch_l4_winner_table_name
622 ,tup.batch_l5_winner_table_name
623 ,tup.batch_wt_winner_table_name
624 INTO
625 l_trans_target
626 ,l_match_target
627 ,l_umatch_target
628 ,l_winner_target
629 ,l_uwinner_target
630 ,l_L1_target
631 ,l_L2_target
632 ,l_L3_target
633 ,l_L4_target
634 ,l_L5_target
635 ,l_WT_target
636 FROM
637 jty_trans_usg_pgm_details tup
638 WHERE tup.source_id = p_source_id
639 AND tup.trans_type_id = p_trans_id
640 AND tup.program_name = p_program_name;
641
642 -- debug message
643 jty_log(FND_LOG.LEVEL_STATEMENT,
644 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.temp_table_names',
645 'TRANS table name : ' || l_trans_target || ' MATCH table name : ' || l_match_target ||
646 ' WINNER table name : ' || l_winner_target || ' L1 table name : ' || l_L1_target ||
647 ' L2 table name : ' || l_L2_target || ' L3 table name : ' || l_L3_target ||
648 ' L4 table name : ' || l_L4_target || ' L5 table name : ' || l_L5_target ||
649 ' WT table name : ' || l_WT_target);
650
651 /* Truncate and drop indexes on the TRANS table */
652 IF (l_trans_target IS NOT NULL) THEN
653
654 -- debug message
655 jty_log(FND_LOG.LEVEL_STATEMENT,
656 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.trans_clear',
657 'Deleting data and dropping indexes from ' || l_trans_target);
658
659 /* Drop the indexes on the TRANS table */
660 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
661 p_table_name => l_trans_target
662 ,x_return_status => x_return_status );
663
664 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
665 -- debug message
666 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_trans_target;
667 jty_log(FND_LOG.LEVEL_EXCEPTION,
668 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.trans_clear',
669 x_msg_data);
670
671 RAISE FND_API.G_EXC_ERROR;
672 END IF;
673
674 /* Truncate the TRANS table */
675 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
676 p_table_name => l_trans_target
677 ,x_return_status => x_return_status );
678
679 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
680 -- debug message
681 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_trans_target;
682 jty_log(FND_LOG.LEVEL_EXCEPTION,
683 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.trans_clear',
684 x_msg_data);
685
686 RAISE FND_API.G_EXC_ERROR;
687 END IF;
688
689 END IF; /* end IF (l_trans_target IS NOT NULL) */
690
691 /* Truncate and drop indexes on the MATCH table */
692 IF (l_match_target IS NOT NULL) THEN
693
694 -- debug message
695 jty_log(FND_LOG.LEVEL_STATEMENT,
696 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.match_clear',
697 'Deleting data and dropping indexes from ' || l_match_target);
698
699 /* Drop the indexes on the MATCH table */
700 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
701 p_table_name => l_match_target
702 ,x_return_status => x_return_status );
703
704 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
705
706 -- debug message
707 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_match_target;
708 jty_log(FND_LOG.LEVEL_EXCEPTION,
709 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.match_clear',
710 x_msg_data);
711
712 RAISE FND_API.G_EXC_ERROR;
713 END IF;
714
715 /* Truncate the MATCH table */
716 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
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 -- debug message
722 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_match_target;
723 jty_log(FND_LOG.LEVEL_EXCEPTION,
724 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.match_clear',
725 x_msg_data);
726
727 RAISE FND_API.G_EXC_ERROR;
728 END IF;
729
730 END IF; /* end (l_match_target IS NOT NULL) */
731
732 /* Truncate and drop indexes on the UMATCH table */
733 IF (l_umatch_target IS NOT NULL) THEN
734
735 -- debug message
736 jty_log(FND_LOG.LEVEL_STATEMENT,
737 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.umatch_clear',
738 'Deleting data and dropping indexes from ' || l_umatch_target);
739
740 /* Drop the indexes on the UMATCH table */
741 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
742 p_table_name => l_umatch_target
743 ,x_return_status => x_return_status );
744
745 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
746
747 -- debug message
748 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_umatch_target;
749 jty_log(FND_LOG.LEVEL_EXCEPTION,
750 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.umatch_clear',
751 x_msg_data);
752
753 RAISE FND_API.G_EXC_ERROR;
754 END IF;
755
756 /* Truncate the UMATCH table */
757 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
758 p_table_name => l_umatch_target
759 ,x_return_status => x_return_status );
760
761 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
762 -- debug message
763 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_umatch_target;
764 jty_log(FND_LOG.LEVEL_EXCEPTION,
765 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.umatch_clear',
766 x_msg_data);
767
768 RAISE FND_API.G_EXC_ERROR;
769 END IF;
770
771 END IF; /* end (l_umatch_target IS NOT NULL) */
772
773 /* Truncate and drop indexes on the WINNER table */
774 IF (l_winner_target IS NOT NULL) THEN
775
776 -- debug message
777 jty_log(FND_LOG.LEVEL_STATEMENT,
778 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.winner_clear',
779 'Deleting data and dropping indexes from ' || l_winner_target);
780
781 /* Drop the indexes on the WINNER table */
782 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
783 p_table_name => l_winner_target
784 ,x_return_status => x_return_status );
785
786 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
787
788 -- debug message
789 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_winner_target;
790 jty_log(FND_LOG.LEVEL_EXCEPTION,
791 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.winner_clear',
792 x_msg_data);
793
794 RAISE FND_API.G_EXC_ERROR;
795 END IF;
796
797 /* Truncate the WINNER table */
798 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
799 p_table_name => l_winner_target
800 ,x_return_status => x_return_status );
801
802 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
803 -- debug message
804 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_winner_target;
805 jty_log(FND_LOG.LEVEL_EXCEPTION,
806 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.winner_clear',
807 x_msg_data);
808
809 RAISE FND_API.G_EXC_ERROR;
810 END IF;
811
812 END IF; /* end (l_winner_target IS NOT NULL) */
813
814 /* Truncate and drop indexes on the UWINNER table */
815 IF (l_uwinner_target IS NOT NULL) THEN
816
817 -- debug message
818 jty_log(FND_LOG.LEVEL_STATEMENT,
819 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.uwinner_clear',
820 'Deleting data and dropping indexes from ' || l_uwinner_target);
821
822 /* Drop the indexes on the UWINNER table */
823 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
824 p_table_name => l_uwinner_target
825 ,x_return_status => x_return_status );
826
827 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
828
829 -- debug message
830 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_uwinner_target;
831 jty_log(FND_LOG.LEVEL_EXCEPTION,
832 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.uwinner_clear',
833 x_msg_data);
834
835 RAISE FND_API.G_EXC_ERROR;
836 END IF;
837
838 /* Truncate the UWINNER table */
839 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
840 p_table_name => l_uwinner_target
841 ,x_return_status => x_return_status );
842
843 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
844 -- debug message
845 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_uwinner_target;
846 jty_log(FND_LOG.LEVEL_EXCEPTION,
847 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.uwinner_clear',
848 x_msg_data);
849
850 RAISE FND_API.G_EXC_ERROR;
851 END IF;
852
853 END IF; /* end (l_uwinner_target IS NOT NULL) */
854
855 /* Truncate and drop indexes on the L1 table */
856 IF (l_l1_target IS NOT NULL) THEN
857
858 -- debug message
859 jty_log(FND_LOG.LEVEL_STATEMENT,
860 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l1_clear',
861 'Deleting data and dropping indexes from ' || l_l1_target);
862
863 /* Drop the indexes on the L1 table */
864 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
865 p_table_name => l_l1_target
866 ,x_return_status => x_return_status );
867
868 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
869
870 -- debug message
871 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l1_target;
872 jty_log(FND_LOG.LEVEL_EXCEPTION,
873 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l1_clear',
874 x_msg_data);
875
876 RAISE FND_API.G_EXC_ERROR;
877 END IF;
878
879 /* Truncate the L1 table */
880 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
881 p_table_name => l_l1_target
882 ,x_return_status => x_return_status );
883
884 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
885 -- debug message
886 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l1_target;
887 jty_log(FND_LOG.LEVEL_EXCEPTION,
888 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l1_clear',
889 x_msg_data);
890
891 RAISE FND_API.G_EXC_ERROR;
892 END IF;
893
894 END IF; /* end (l_l1_target IS NOT NULL) */
895
896 /* Truncate and drop indexes on the L2 table */
897 IF (l_l2_target IS NOT NULL) THEN
898
899 -- debug message
900 jty_log(FND_LOG.LEVEL_STATEMENT,
901 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l2_clear',
902 'Deleting data and dropping indexes from ' || l_l2_target);
903
904 /* Drop the indexes on the L2 table */
905 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
906 p_table_name => l_l2_target
907 ,x_return_status => x_return_status );
908
909 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
910
911 -- debug message
912 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l2_target;
913 jty_log(FND_LOG.LEVEL_EXCEPTION,
914 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l2_clear',
915 x_msg_data);
916
917 RAISE FND_API.G_EXC_ERROR;
918 END IF;
919
920 /* Truncate the L2 table */
921 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
922 p_table_name => l_l2_target
923 ,x_return_status => x_return_status );
924
925 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
926 -- debug message
927 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l2_target;
928 jty_log(FND_LOG.LEVEL_EXCEPTION,
929 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l2_clear',
930 x_msg_data);
931
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934
935 END IF; /* end (l_l2_target IS NOT NULL) */
936
937 /* Truncate and drop indexes on the L3 table */
938 IF (l_l3_target IS NOT NULL) THEN
939
940 -- debug message
941 jty_log(FND_LOG.LEVEL_STATEMENT,
942 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l3_clear',
943 'Deleting data and dropping indexes from ' || l_l3_target);
944
945 /* Drop the indexes on the L3 table */
946 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
947 p_table_name => l_l3_target
948 ,x_return_status => x_return_status );
949
950 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
951
952 -- debug message
953 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l3_target;
954 jty_log(FND_LOG.LEVEL_EXCEPTION,
955 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l3_clear',
956 x_msg_data);
957
958 RAISE FND_API.G_EXC_ERROR;
959 END IF;
960
961 /* Truncate the L3 table */
962 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
963 p_table_name => l_l3_target
964 ,x_return_status => x_return_status );
965
966 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
967 -- debug message
968 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l3_target;
969 jty_log(FND_LOG.LEVEL_EXCEPTION,
970 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l3_clear',
971 x_msg_data);
972
973 RAISE FND_API.G_EXC_ERROR;
974 END IF;
975
976 END IF; /* end (l_l3_target IS NOT NULL) */
977
978 /* Truncate and drop indexes on the L4 table */
979 IF (l_l4_target IS NOT NULL) THEN
980
981 -- debug message
982 jty_log(FND_LOG.LEVEL_STATEMENT,
983 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l4_clear',
984 'Deleting data and dropping indexes from ' || l_l4_target);
985
986 /* Drop the indexes on the L4 table */
987 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
988 p_table_name => l_l4_target
989 ,x_return_status => x_return_status );
990
991 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
992
993 -- debug message
994 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l4_target;
995 jty_log(FND_LOG.LEVEL_EXCEPTION,
996 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l4_clear',
997 x_msg_data);
998
999 RAISE FND_API.G_EXC_ERROR;
1000 END IF;
1001
1002 /* Truncate the L4 table */
1003 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1004 p_table_name => l_l4_target
1005 ,x_return_status => x_return_status );
1006
1007 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1008 -- debug message
1009 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l4_target;
1010 jty_log(FND_LOG.LEVEL_EXCEPTION,
1011 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l4_clear',
1012 x_msg_data);
1013
1014 RAISE FND_API.G_EXC_ERROR;
1015 END IF;
1016
1017 END IF; /* end (l_l4_target IS NOT NULL) */
1018
1019 /* Truncate and drop indexes on the L5 table */
1020 IF (l_l5_target IS NOT NULL) THEN
1021
1022 -- debug message
1023 jty_log(FND_LOG.LEVEL_STATEMENT,
1024 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l5_clear',
1025 'Deleting data and dropping indexes from ' || l_l5_target);
1026
1027 /* Drop the indexes on the L5 table */
1028 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1029 p_table_name => l_l5_target
1030 ,x_return_status => x_return_status );
1031
1032 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1033
1034 -- debug message
1035 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_l5_target;
1036 jty_log(FND_LOG.LEVEL_EXCEPTION,
1037 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l5_clear',
1038 x_msg_data);
1039
1040 RAISE FND_API.G_EXC_ERROR;
1041 END IF;
1042
1043 /* Truncate the L5 table */
1044 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1045 p_table_name => l_l5_target
1046 ,x_return_status => x_return_status );
1047
1048 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1049 -- debug message
1050 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_l5_target;
1051 jty_log(FND_LOG.LEVEL_EXCEPTION,
1052 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.l5_clear',
1053 x_msg_data);
1054
1055 RAISE FND_API.G_EXC_ERROR;
1056 END IF;
1057
1058 END IF; /* end (l_l5_target IS NOT NULL) */
1059
1060 /* Truncate and drop indexes on the WT table */
1061 IF (l_wt_target IS NOT NULL) THEN
1062
1063 -- debug message
1064 jty_log(FND_LOG.LEVEL_STATEMENT,
1065 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.wt_clear',
1066 'Deleting data and dropping indexes from ' || l_wt_target);
1067
1068 /* Drop the indexes on the WT table */
1069 JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1070 p_table_name => l_wt_target
1071 ,x_return_status => x_return_status );
1072
1073 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1074
1075 -- debug message
1076 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' || l_wt_target;
1077 jty_log(FND_LOG.LEVEL_EXCEPTION,
1078 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.wt_clear',
1079 x_msg_data);
1080
1081 RAISE FND_API.G_EXC_ERROR;
1082 END IF;
1083
1084 /* Truncate the WT table */
1085 JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1086 p_table_name => l_wt_target
1087 ,x_return_status => x_return_status );
1088
1089 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1090 -- debug message
1091 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' || l_wt_target;
1092 jty_log(FND_LOG.LEVEL_EXCEPTION,
1093 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.wt_clear',
1094 x_msg_data);
1095
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098
1099 END IF; /* end (l_wt_target IS NOT NULL) */
1100
1101 IF (p_mode = 'TOTAL') THEN
1102 DELETE jty_changed_terrs
1103 WHERE source_id = p_source_id
1104 AND star_request_id IS NOT NULL;
1105 ELSIF (p_mode = 'INCREMENTAL') THEN
1106 DELETE jty_changed_terrs
1107 WHERE source_id = p_source_id
1108 AND tap_request_id IS NOT NULL
1109 AND tap_request_id <> p_request_id;
1110
1111 BEGIN
1112 SELECT 1
1113 INTO l_dummy
1114 FROM jty_changed_terrs
1115 WHERE source_id = p_source_id
1116 AND tap_request_id = p_request_id
1117 AND rownum <= 1;
1118 EXCEPTION
1119 WHEN NO_DATA_FOUND THEN
1120 UPDATE jty_changed_terrs
1121 SET tap_request_id = p_request_id
1122 WHERE source_id = p_source_id
1123 AND star_request_id IS NOT NULL;
1124 END;
1125 END IF;
1126
1127 -- debug message
1128 jty_log(FND_LOG.LEVEL_PROCEDURE,
1129 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.end',
1130 'End of the procedure JTY_ASSIGN_BULK_PUB.clear_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1131
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 x_return_status := FND_API.G_RET_STS_ERROR ;
1135 RETCODE := 2;
1136 x_msg_count := 1;
1137 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
1138 ' program name : ' || p_program_name;
1139 ERRBUF := x_msg_data;
1140 jty_log(FND_LOG.LEVEL_EXCEPTION,
1141 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Clear_Trans_Data.no_data_found',
1142 x_msg_data);
1143
1144 WHEN FND_API.G_EXC_ERROR THEN
1145 RETCODE := 2;
1146 x_msg_count := 1;
1147 ERRBUF := x_msg_data;
1148 jty_log(FND_LOG.LEVEL_EXCEPTION,
1149 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Clear_Trans_Data.g_exc_error',
1150 x_msg_data);
1151
1152 WHEN OTHERS THEN
1153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1154 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1155 x_msg_count := 1;
1156 ERRBUF := x_msg_data;
1157 jty_log(FND_LOG.LEVEL_EXCEPTION,
1158 'jtf.plsql.JTY_ASSIGN_BULK_PUB.clear_trans_data.other',
1159 substr(x_msg_data, 1, 4000));
1160
1161 END Clear_Trans_Data;
1162
1163
1164 -- ***************************************************
1165 -- API Specifications
1166 -- ***************************************************
1167 -- api name : GET_WINNERS_PARALLEL_SETUP
1168 -- type : public.
1169 -- function :
1170 -- pre-reqs :
1171 -- notes:
1172 --
1173 PROCEDURE get_winners_parallel_setup
1174 ( p_source_id IN NUMBER,
1175 p_trans_id IN NUMBER,
1176 p_program_name IN VARCHAR2,
1177 p_mode IN VARCHAR2,
1178 p_no_of_workers IN NUMBER,
1179 p_percent_analyzed IN NUMBER,
1180 p_request_id IN NUMBER,
1181 x_return_status OUT NOCOPY VARCHAR2,
1182 x_msg_count OUT NOCOPY NUMBER,
1183 x_msg_data OUT NOCOPY VARCHAR2,
1184 ERRBUF OUT NOCOPY VARCHAR2,
1185 RETCODE OUT NOCOPY VARCHAR2
1186 )
1187 AS
1188
1189 l_sysdate DATE;
1190 num_of_terr NUMBER;
1191 num_of_trans NUMBER;
1192 d_statement VARCHAR2(2000);
1193
1194 l_trans_target VARCHAR2(30);
1195 l_match_target VARCHAR2(30);
1196 l_umatch_target VARCHAR2(30);
1197 l_winner_target VARCHAR2(30);
1198 l_uwinner_target VARCHAR2(30);
1199 l_L1_target VARCHAR2(30);
1200 l_L2_target VARCHAR2(30);
1201 l_L3_target VARCHAR2(30);
1202 l_L4_target VARCHAR2(30);
1203 l_L5_target VARCHAR2(30);
1204 l_WT_target VARCHAR2(30);
1205
1206 BEGIN
1207
1208 -- debug message
1209 jty_log(FND_LOG.LEVEL_PROCEDURE,
1210 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.begin',
1211 'Start of the procedure JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1212
1213 x_return_status := FND_API.G_RET_STS_SUCCESS;
1214 l_sysdate := SYSDATE;
1215
1216 /* Corresponding to the usage, transaction type and program name */
1217 /* get all the interface table names */
1218 SELECT
1219 decode(p_mode, 'TOTAL', tup.batch_trans_table_name,
1220 'INCREMENTAL', tup.batch_nm_trans_table_name,
1221 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
1222 ,tup.batch_match_table_name
1223 ,tup.batch_unique_match_table_name
1224 ,tup.batch_winner_table_name
1225 ,tup.batch_unique_winner_table_name
1226 ,tup.batch_l1_winner_table_name
1227 ,tup.batch_l2_winner_table_name
1228 ,tup.batch_l3_winner_table_name
1229 ,tup.batch_l4_winner_table_name
1230 ,tup.batch_l5_winner_table_name
1231 ,tup.batch_wt_winner_table_name
1232 INTO
1233 l_trans_target
1234 ,l_umatch_target
1235 ,l_match_target
1236 ,l_winner_target
1237 ,l_uwinner_target
1238 ,l_L1_target
1239 ,l_L2_target
1240 ,l_L3_target
1241 ,l_L4_target
1242 ,l_L5_target
1243 ,l_WT_target
1244 FROM
1245 jty_trans_usg_pgm_details tup
1246 WHERE tup.source_id = p_source_id
1247 AND tup.trans_type_id = p_trans_id
1248 AND tup.program_name = p_program_name;
1249
1250 -- debug message
1251 jty_log(FND_LOG.LEVEL_STATEMENT,
1252 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.temp_table_names',
1253 'TRANS table name : ' || l_trans_target || ' MATCH table name : ' || l_match_target ||
1254 ' WINNER table name : ' || l_winner_target || ' L1 table name : ' || l_L1_target ||
1255 ' L2 table name : ' || l_L2_target || ' L3 table name : ' || l_L3_target ||
1256 ' L4 table name : ' || l_L4_target || ' L5 table name : ' || l_L5_target ||
1257 ' WT table name : ' || l_WT_target);
1258
1259 /* set NOLOGGING on JTF_TAE_..._MATCHES and JTF_TAE_..._WINNERS tables */
1260 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_trans_target);
1261 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_match_target);
1262 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_umatch_target);
1263 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_winner_target);
1264 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_uwinner_target);
1265 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L1_target);
1266 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L2_target);
1267 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L3_target);
1268 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L4_target);
1269 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_L5_target);
1270 JTY_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_WT_target);
1271
1272 /* Check for territories for this Usage/Transaction Type */
1273 /* This check is not done in date effective mode as inactive territories */
1274 /* can also win depending on the date of the transaction object */
1275 IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
1276 SELECT COUNT(*)
1277 INTO num_of_terr
1278 FROM jtf_terr_qtype_usgs_all jtqu
1279 , jtf_terr_all jt1
1280 , jtf_qual_type_usgs jqtu
1281 WHERE jtqu.terr_id = jt1.terr_id
1282 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1283 AND jqtu.qual_type_id = p_trans_id
1284 AND jqtu.source_id = p_source_id
1285 AND jt1.end_date_active >= l_sysdate
1286 AND jt1.start_date_active <= l_sysdate
1287 AND EXISTS (
1288 SELECT 1
1289 FROM jtf_terr_rsc_all jtr,
1290 jtf_terr_rsc_access_all jtra,
1291 jtf_qual_types_all jqta
1292 WHERE jtr.terr_id = jt1.terr_id
1293 AND jtr.end_date_active >= l_sysdate
1294 AND jtr.start_date_active <= l_sysdate
1295 AND jtr.resource_type <> 'RS_ROLE'
1296 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1297 AND jtra.access_type = jqta.name
1298 AND jqta.qual_type_id = p_trans_id
1299 AND jtra.trans_access_code <> 'NONE')
1300 AND NOT EXISTS (
1301 SELECT jt.terr_id
1302 FROM jtf_terr_all jt
1303 WHERE jt.end_date_active < l_sysdate
1304 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
1305 START WITH jt.terr_id = jt1.terr_id)
1306 AND jqtu.qual_type_id <> -1001;
1307
1308 -- debug message
1309 jty_log(FND_LOG.LEVEL_STATEMENT,
1310 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.num_of_terr',
1311 'Number of valid territories with resources for this transaction : ' || num_of_terr);
1312
1313 IF (num_of_terr = 0) THEN
1314 x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP: There are NO Active Territories with Active ' ||
1315 'Resources existing for this Usage/Transaction combination, so no assignments ' ||
1316 'can take place.';
1317
1318 RAISE NO_TAE_DATA_FOUND;
1319 END IF;
1320 END IF; /* END IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) */
1321
1322 d_statement := ' SELECT COUNT(*) FROM ' || l_trans_target || ' WHERE rownum < 2 ';
1323 EXECUTE IMMEDIATE d_statement INTO num_of_trans;
1324
1325 -- debug message
1326 jty_log(FND_LOG.LEVEL_STATEMENT,
1327 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.num_of_trans',
1328 'Number of valid transaction objects : ' || num_of_trans);
1329
1330 IF (num_of_trans = 0) THEN
1331 x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP : There are NO valid Transaction Objects to assign.';
1332 RAISE NO_TAE_DATA_FOUND;
1333 END IF;
1334
1335 -- debug message
1336 jty_log(FND_LOG.LEVEL_STATEMENT,
1337 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_trans',
1338 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_trans_target);
1339
1340 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
1341 p_table_name => l_trans_target
1342 , p_percent => p_percent_analyzed
1343 , x_return_status => x_return_status );
1344
1345 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1346 x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP: Call to ' ||
1347 'JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' || l_trans_target;
1348 RAISE FND_API.G_EXC_ERROR;
1349 END IF;
1350
1351 -- debug message
1352 jty_log(FND_LOG.LEVEL_STATEMENT,
1353 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.decompose_terr_defns',
1354 'Call to JTY_TAE_CONTROL_PVT.Decompose_Terr_Defns API begins for : ' || l_trans_target);
1355
1356 JTY_TAE_CONTROL_PVT.Decompose_Terr_Defns
1357 (p_Api_Version_Number => 1.0,
1358 p_Init_Msg_List => FND_API.G_FALSE,
1359 p_trans_target => l_trans_target,
1360 p_classify_terr_comb => 'N',
1361 p_process_tx_oin_sel => 'Y',
1362 p_generate_indexes => 'Y',
1363 p_source_id => p_source_id,
1364 p_trans_id => p_trans_id,
1365 p_program_name => p_program_name,
1366 p_mode => p_mode,
1367 x_Return_Status => x_return_status,
1368 x_Msg_Count => x_msg_count,
1369 x_Msg_Data => x_msg_data,
1370 errbuf => ERRBUF,
1371 retcode => RETCODE );
1372
1373 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1374 x_msg_data := 'JTY_ASSIGN_BULK_PUB.GET_WINNERS_PARALLEL_SETUP: Call to ' ||
1375 'JTY_TAE_CONTROL_PVT.Decompose_Terr_Defns API has failed for ' || l_trans_target;
1376 RAISE FND_API.G_EXC_ERROR;
1377 END IF;
1378
1379 -- debug message
1380 jty_log(FND_LOG.LEVEL_STATEMENT,
1381 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_matches',
1382 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_match_target);
1383
1384 /* Build Index on Matches table */
1385 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1386 ( p_table_name => l_match_target,
1387 p_trans_id => p_trans_id,
1388 p_source_id => p_source_id,
1389 p_program_name => p_program_name,
1390 p_mode => p_mode,
1391 x_Return_Status => x_return_status,
1392 p_run_mode => 'MATCH');
1393
1394 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1395 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_match_target;
1396 RAISE FND_API.G_EXC_ERROR;
1397 END IF;
1398
1399 -- debug message
1400 jty_log(FND_LOG.LEVEL_STATEMENT,
1401 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_winners',
1402 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_winner_target);
1403
1404 /* Build Index on Winners table */
1405 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1406 ( p_table_name => l_winner_target,
1407 p_trans_id => p_trans_id,
1408 p_source_id => p_source_id,
1409 p_program_name => p_program_name,
1410 p_mode => p_mode,
1411 x_Return_Status => x_return_status,
1412 p_run_mode => 'WINNER');
1413
1414 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1415 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_winner_target;
1416 RAISE FND_API.G_EXC_ERROR;
1417 END IF;
1418
1419 -- debug message
1420 jty_log(FND_LOG.LEVEL_STATEMENT,
1421 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l1',
1422 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l1_target);
1423
1424 /* Build Index on L1 table */
1425 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1426 ( p_table_name => l_l1_target,
1427 p_trans_id => p_trans_id,
1428 p_source_id => p_source_id,
1429 p_program_name => p_program_name,
1430 p_mode => p_mode,
1431 x_Return_Status => x_return_status,
1432 p_run_mode => 'TEMP_WINNER');
1433
1434 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1435 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l1_target;
1436 RAISE FND_API.G_EXC_ERROR;
1437 END IF;
1438
1439 -- debug message
1440 jty_log(FND_LOG.LEVEL_STATEMENT,
1441 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l2',
1442 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l2_target);
1443
1444 /* Build Index on L2 table */
1445 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1446 ( p_table_name => l_l2_target,
1447 p_trans_id => p_trans_id,
1448 p_source_id => p_source_id,
1449 p_program_name => p_program_name,
1450 p_mode => p_mode,
1451 x_Return_Status => x_return_status,
1452 p_run_mode => 'TEMP_WINNER');
1453
1454 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1455 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l2_target;
1456 RAISE FND_API.G_EXC_ERROR;
1457 END IF;
1458
1459
1460 -- debug message
1461 jty_log(FND_LOG.LEVEL_STATEMENT,
1462 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l3',
1463 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l3_target);
1464
1465 /* Build Index on L3 table */
1466 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1467 ( p_table_name => l_l3_target,
1468 p_trans_id => p_trans_id,
1469 p_source_id => p_source_id,
1470 p_program_name => p_program_name,
1471 p_mode => p_mode,
1472 x_Return_Status => x_return_status,
1473 p_run_mode => 'TEMP_WINNER');
1474
1475 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1476 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l3_target;
1477 RAISE FND_API.G_EXC_ERROR;
1478 END IF;
1479
1480 -- debug message
1481 jty_log(FND_LOG.LEVEL_STATEMENT,
1482 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l4',
1483 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l4_target);
1484
1485 /* Build Index on L4 table */
1486 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1487 ( p_table_name => l_l4_target,
1488 p_trans_id => p_trans_id,
1489 p_source_id => p_source_id,
1490 p_program_name => p_program_name,
1491 p_mode => p_mode,
1492 x_Return_Status => x_return_status,
1493 p_run_mode => 'TEMP_WINNER');
1494
1495 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1496 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l4_target;
1497 RAISE FND_API.G_EXC_ERROR;
1498 END IF;
1499
1500 -- debug message
1501 jty_log(FND_LOG.LEVEL_STATEMENT,
1502 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_l5',
1503 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_l5_target);
1504
1505 /* Build Index on L5 table */
1506 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1507 ( p_table_name => l_l5_target,
1508 p_trans_id => p_trans_id,
1509 p_source_id => p_source_id,
1510 p_program_name => p_program_name,
1511 p_mode => p_mode,
1512 x_Return_Status => x_return_status,
1513 p_run_mode => 'TEMP_WINNER');
1514
1515 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1516 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_l5_target;
1517 RAISE FND_API.G_EXC_ERROR;
1518 END IF;
1519
1520 -- debug message
1521 jty_log(FND_LOG.LEVEL_STATEMENT,
1522 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.analyze_wt',
1523 'Call to JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API begins for : ' || l_wt_target);
1524
1525 /* Build Index on WT table */
1526 JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX
1527 ( p_table_name => l_wt_target,
1528 p_trans_id => p_trans_id,
1529 p_source_id => p_source_id,
1530 p_program_name => p_program_name,
1531 p_mode => p_mode,
1532 x_Return_Status => x_return_status,
1533 p_run_mode => 'TEMP_WINNER');
1534
1535 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1536 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' || l_wt_target;
1537 RAISE FND_API.G_EXC_ERROR;
1538 END IF;
1539
1540 /* if mode is incremental, update the worker_id column for the TRANS table */
1541 IF (p_mode = 'INCREMENTAL') THEN
1542 d_statement := 'UPDATE ' || l_trans_target ||
1543 ' SET worker_id = mod(trans_object_id, :no_of_workers) + 1';
1544 EXECUTE IMMEDIATE d_statement USING p_no_of_workers;
1545 END IF;
1546
1547 -- debug message
1548 jty_log(FND_LOG.LEVEL_PROCEDURE,
1549 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.end',
1550 'End of the procedure JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1551
1552 EXCEPTION
1553 WHEN NO_TAE_DATA_FOUND THEN
1554 x_return_status := FND_API.G_RET_STS_SUCCESS;
1555 x_msg_count := 1;
1556 RETCODE := 0;
1557 ERRBUF := null;
1558 jty_log(FND_LOG.LEVEL_EXCEPTION,
1559 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.no_tae_data_found',
1560 x_msg_data);
1561
1562 WHEN FND_API.G_EXC_ERROR THEN
1563 x_return_status := FND_API.G_RET_STS_ERROR;
1564 x_msg_count := 1;
1565 RETCODE := 2;
1566 ERRBUF := x_msg_data;
1567 jty_log(FND_LOG.LEVEL_EXCEPTION,
1568 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.g_exc_error',
1569 x_msg_data);
1570
1571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1573 x_msg_count := 1;
1574 RETCODE := 2;
1575 ERRBUF := x_msg_data;
1576 jty_log(FND_LOG.LEVEL_EXCEPTION,
1577 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.g_exc_unexpected_error',
1578 x_msg_data);
1579
1580 WHEN OTHERS THEN
1581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1582 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1583 x_msg_count := 1;
1584 RETCODE := 2;
1585 ERRBUF := x_msg_data;
1586 jty_log(FND_LOG.LEVEL_EXCEPTION,
1587 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup.other',
1588 substr(x_msg_data, 1, 4000));
1589
1590 END get_winners_parallel_setup;
1591
1592 -- ***************************************************
1593 -- API Specifications
1594 -- ***************************************************
1595 PROCEDURE Process_Level_Winners (
1596 p_terr_LEVEL_target_tbl IN VARCHAR2,
1597 p_terr_PARENT_LEVEL_tbl IN VARCHAR2,
1598 p_UPPER_LEVEL_FROM_ROOT IN NUMBER,
1599 p_LOWER_LEVEL_FROM_ROOT IN NUMBER,
1600 p_matches_target IN VARCHAR2,
1601 p_source_id IN NUMBER,
1602 p_run_mode IN VARCHAR2,
1603 p_date_effective IN BOOLEAN,
1604 x_return_status OUT NOCOPY VARCHAR2,
1605 p_worker_id IN NUMBER
1606 )
1607 AS
1608
1609 l_denorm_table_name VARCHAR2(60);
1610 l_dyn_str VARCHAR2(32767);
1611
1612 BEGIN
1613
1614 -- debug message
1615 jty_log(FND_LOG.LEVEL_PROCEDURE,
1616 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.start',
1617 'Start of the procedure JTY_ASSIGN_BULK_PUB.Process_Level_Winners ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1618
1619 x_return_status := FND_API.G_RET_STS_SUCCESS;
1620
1621 IF (p_date_effective) THEN
1622 l_denorm_table_name := 'JTY_DENORM_DEA_RULES_ALL';
1623 ELSE
1624 l_denorm_table_name := 'JTF_TERR_DENORM_RULES_ALL';
1625 END IF;
1626
1627 IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 1) THEN
1628
1629 l_dyn_str := ' ' ||
1630 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
1631 ' ( ' ||
1632 ' trans_object_id ' ||
1633 ' , trans_detail_object_id ' ||
1634 ' , txn_date ' ||
1635 ' , WIN_TERR_ID ' ||
1636 ' , UL_TERR_ID ' ||
1637 ' , LL_TERR_ID ' ||
1638 ' , LL_NUM_WINNERS ' ||
1639 ' , WORKER_ID ' ||
1640 ' ) ' ||
1641 ' (SELECT ' ||
1642 ' TL.trans_object_id ' ||
1643 ' , TL.trans_detail_object_id ' ||
1644 ' , TL.txn_date ' ||
1645 ' , TL.CL_WIN_TERR_ID ' ||
1646 ' , TL.UL_terr_id ' ||
1647 ' , TL.LL_terr_id ' ||
1648 ' , TL.LL_num_winners ' ||
1649 ' , :B_WORKER_ID ' ||
1650 ' FROM ( ' ||
1651 ' SELECT ';
1652
1653 IF (p_run_mode = 'BATCH') THEN
1654 /* Batch TAE */
1655 l_dyn_str := l_dyn_str || ' /*+ FULL(M) */ ';
1656 ELSE
1657 /* Real-time TAE */
1658 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
1659 END IF;
1660
1661 l_dyn_str := l_dyn_str ||
1662 ' DISTINCT ' ||
1663 ' m.trans_object_id ' ||
1664 ' , m.trans_detail_object_id ' ||
1665 ' , m.txn_date ' ||
1666 ' , LL.RELATED_TERR_ID CL_WIN_TERR_ID ' ||
1667 ' , UL.related_terr_id UL_TERR_ID ' ||
1668 ' , NVL(UL.num_winners, 1) UL_NUM_WINNERS ' ||
1669 ' , LL.related_terr_id LL_TERR_ID ' ||
1670 ' , NVL(LL.num_winners, 1) LL_NUM_WINNERS ' ||
1671 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
1672 ' m.trans_object_id ' ||
1673 ' , m.trans_detail_object_id ' ||
1674 ' , UL.related_terr_id ' ||
1675 ' ORDER BY LL.absolute_rank DESC ' ||
1676 ' , LL.related_terr_id ) AS LL_TERR_RANK ' ||
1677 ' FROM ' || p_matches_target || ' M ' ||
1678 ' , ' || l_denorm_table_name || ' UL ' ||
1679 ' , ' || l_denorm_table_name || ' LL ' ||
1680 ' WHERE UL.level_from_root = :b1_UPPER_LEVEL +1 ' || /* UPPER level territory */
1681 ' AND UL.source_id = :b1_source_id ' ||
1682 ' AND UL.terr_id = M.TERR_ID ' ||
1683 ' AND LL.level_from_root = :b1_LOWER_LEVEL +1 ' || /* LOWER level territory */
1684 ' AND LL.source_id = :b2_source_id ' ||
1685 ' AND LL.terr_id = M.TERR_ID ' ||
1686 ' AND M.worker_id = :B_WORKER_ID ' ||
1687 ' ) TL ' ||
1688 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
1689 ' ) ';
1690
1691 BEGIN
1692 EXECUTE IMMEDIATE l_dyn_str USING
1693 p_worker_id
1694 , p_UPPER_LEVEL_FROM_ROOT
1695 , p_source_id
1696 , p_LOWER_LEVEL_FROM_ROOT
1697 , p_source_id
1698 , p_worker_id;
1699 EXCEPTION
1700 WHEN NO_DATA_FOUND THEN
1701 NULL;
1702 END;
1703
1704 ELSE
1705
1706 l_dyn_str := ' ' ||
1707 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
1708 ' ( ' ||
1709 ' trans_object_id ' ||
1710 ' , trans_detail_object_id ' ||
1711 ' , txn_date ' ||
1712 ' , WIN_TERR_ID ' ||
1713 ' , UL_TERR_ID ' ||
1714 ' , LL_TERR_ID ' ||
1715 ' , LL_NUM_WINNERS ' ||
1716 ' , WORKER_ID ' ||
1717 ' ) ' ||
1718 ' (SELECT ' ||
1719 ' TL.trans_object_id ' ||
1720 ' , TL.trans_detail_object_id ' ||
1721 ' , TL.txn_date ' ||
1722 ' , TL.CL_WIN_TERR_ID ' ||
1723 ' , TL.UL_terr_id ' ||
1724 ' , TL.LL_terr_id ' ||
1725 ' , TL.LL_num_winners ' ||
1726 ' , :B_WORKER_ID ' ||
1727 ' FROM ( ' || /* NL */
1728 ' SELECT ' ||
1729 ' CL.trans_object_id ' ||
1730 ' , CL.trans_detail_object_id ' ||
1731 ' , CL.txn_date ' ||
1732 ' , CL.CL_WIN_TERR_ID ' ||
1733 ' , CL.UL_terr_id ';
1734
1735 IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 2) THEN
1736 l_dyn_str := l_dyn_str || ' , NVL(CL.UL_NUM_WINNERS, 1) UL_NUM_WINNERS ';
1737 ELSE
1738 l_dyn_str := l_dyn_str || ' , CL.UL_NUM_WINNERS UL_NUM_WINNERS ';
1739 END IF;
1740
1741 l_dyn_str := l_dyn_str ||
1742 ' , CL.LL_TERR_ID ' ||
1743 ' , CL.LL_NUM_WINNERS ' ||
1744 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
1745 ' CL.trans_object_id ' ||
1746 ' , CL.trans_detail_object_id ' ||
1747 ' , CL.UL_TERR_ID ' ||
1748 ' ORDER BY ' ||
1749 ' CL.M_ABS_RANK DESC ' ||
1750 ' , CL.CL_WIN_TERR_ID ) AS LL_TERR_RANK ' ||
1751 ' FROM ( ' ||
1752 ' SELECT ';
1753
1754 IF (p_run_mode = 'BATCH') THEN
1755 /* Batch TAE */
1756 l_dyn_str := l_dyn_str || ' /*+ USE_HASH(ML) USE_HASH(LL) USE_HASH(UL) USE_HASH(M) ORDERED */ ';
1757 ELSE
1758 /* Real-time TAE */
1759 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
1760 END IF;
1761
1762 l_dyn_str := l_dyn_str ||
1763 ' m.trans_object_id ' ||
1764 ' , m.trans_detail_object_id ' ||
1765 ' , m.txn_date ' ||
1766 ' , LL.related_terr_id CL_WIN_TERR_ID ' ||
1767 ' , UL.related_terr_id UL_TERR_ID ' ||
1768 ' , UL.num_winners UL_NUM_WINNERS ' ||
1769 ' , LL.related_terr_id LL_TERR_ID ' ||
1770 ' , LL.num_winners LL_NUM_WINNERS ' ||
1771 ' , max(m.absolute_rank) M_ABS_RANK ' ||
1772 ' FROM ';
1773
1774 IF (p_run_mode = 'BATCH') THEN
1775 l_dyn_str := l_dyn_str ||
1776 ' ' || l_denorm_table_name || ' UL ' ||
1777 ' , ' || p_matches_target || ' M ' ||
1778 ' , ' || l_denorm_table_name || ' LL ' ||
1779 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ';
1780 ELSE
1781 l_dyn_str := l_dyn_str ||
1782 ' ' || l_denorm_table_name || ' LL ' ||
1783 ' , ' || p_matches_target || ' M ' ||
1784 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
1785 ' , ' || l_denorm_table_name || ' UL ';
1786 END IF;
1787
1788 l_dyn_str := l_dyn_str ||
1789 ' WHERE UL.level_from_root = :b1_UPPER_LEVEL +1 ' || /* UPPER level territory */
1790 ' AND UL.source_id = :b2_source_id ' ||
1791 ' AND UL.terr_id = M.TERR_ID ' ||
1792 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
1793 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
1794 ' M.trans_detail_object_id IS NULL ) ' ||
1795 ' AND M.trans_object_id = ML.trans_object_id ' ||
1796 ' AND M.worker_id = ML.WORKER_ID' ||
1797 ' AND M.worker_id = :B_WORKER_ID ' ||
1798 ' AND ML.worker_id = :B_WORKER_ID ' ||
1799 ' AND LL.level_from_root = :b4_LOWER_LEVEL +1 ' || /* LOWER level territory */
1800 ' AND LL.source_id = :b5_source_id ' ||
1801 ' AND ML.LL_NUM_WINNERS IS NOT NULL ' ||
1802 ' AND LL.NUM_WINNERS IS NOT NULL ' ||
1803 ' AND LL.terr_id = M.TERR_ID ' ||
1804 ' GROUP BY ' ||
1805 ' m.trans_object_id ' ||
1806 ' , m.trans_detail_object_id ' ||
1807 ' , m.txn_date ' ||
1808 ' , LL.related_terr_id ' ||
1809 ' , UL.related_terr_id ' ||
1810 ' , UL.num_winners ' ||
1811 ' , LL.related_terr_id ' ||
1812 ' , LL.num_winners ' ||
1813
1814 ' UNION ALL ' ||
1815
1816 ' SELECT ';
1817
1818 IF (p_run_mode = 'BATCH') THEN
1819 /* Batch TAE */
1820 l_dyn_str := l_dyn_str || ' /*+ ORDERED USE_HASH(ML) USE_HASH(M) USE_HASH(UL) USE_HASH(LL) */ ';
1821 ELSE
1822 /* Real-time TAE */
1823 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
1824 END IF;
1825
1826 l_dyn_str := l_dyn_str ||
1827 ' DISTINCT ' ||
1828 ' m.trans_object_id ' ||
1829 ' , m.trans_detail_object_id ' ||
1830 ' , m.txn_date ' ||
1831 ' , m.terr_id CL_WIN_TERR_ID ' ||
1832 ' , UL.related_terr_id UL_TERR_ID ' ||
1833 ' , UL.num_winners UL_NUM_WINNERS ' ||
1834 ' , LL.related_terr_id LL_TERR_ID ' ||
1835 ' , LL.num_winners LL_NUM_WINNERS ' ||
1836 ' , m.absolute_rank M_ABS_RANK ' ||
1837 ' FROM ';
1838
1839 IF (p_run_mode = 'BATCH') THEN
1840 l_dyn_str := l_dyn_str ||
1841 ' ' || l_denorm_table_name || ' UL ' ||
1842 ' , ' || p_matches_target || ' M ' ||
1843 ' , ' || l_denorm_table_name || ' LL ' ||
1844 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ';
1845 ELSE
1846 l_dyn_str := l_dyn_str ||
1847 ' ' || l_denorm_table_name || ' LL ' ||
1848 ' , ' || p_matches_target || ' M ' ||
1849 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
1850 ' , ' || l_denorm_table_name || ' UL ';
1851 END IF;
1852
1853 l_dyn_str := l_dyn_str ||
1854 ' WHERE UL.level_from_root = :b9_UPPER_LEVEL +1 ' || /* UPPER level territory */
1855 ' AND UL.source_id = :b10_source_id ' ||
1856 ' AND UL.terr_id = M.TERR_ID ' ||
1857 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
1858 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
1859 ' M.trans_detail_object_id IS NULL ) ' ||
1860 ' AND M.trans_object_id = ML.trans_object_id ' ||
1861 ' AND M.worker_id = ML.WORKER_ID' ||
1862 ' AND M.worker_id = :B_WORKER_ID ' ||
1863 ' AND ML.worker_id = :B_WORKER_ID ' ||
1864 ' AND LL.level_from_root = :b12_LOWER_LEVEL +1 ' || /* LOWER level territory */
1865 ' AND LL.source_id = :b13_source_id ' ||
1866 ' AND ML.LL_NUM_WINNERS IS NOT NULL ' ||
1867 ' AND LL.NUM_WINNERS IS NULL ' ||
1868 ' AND LL.terr_id = M.TERR_ID ' ||
1869 ' ) CL ' ||
1870 ' ) TL ' ||
1871 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
1872 ' ) ';
1873
1874 BEGIN
1875 EXECUTE IMMEDIATE l_dyn_str USING
1876 p_worker_id
1877 , p_UPPER_LEVEL_FROM_ROOT
1878 , p_source_id
1879 , p_worker_id
1880 , p_worker_id
1881 , p_LOWER_LEVEL_FROM_ROOT
1882 , p_source_id
1883 , p_UPPER_LEVEL_FROM_ROOT
1884 , p_source_id
1885 , p_worker_id
1886 , p_worker_id
1887 , p_LOWER_LEVEL_FROM_ROOT
1888 , p_source_id;
1889
1890 EXCEPTION
1891 WHEN NO_DATA_FOUND THEN
1892 NULL;
1893 END;
1894
1895 END IF; /* end IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 1) */
1896
1897 -- debug message
1898 jty_log(FND_LOG.LEVEL_STATEMENT,
1899 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.num_rows',
1900 'Number of rows inserted into ' || p_terr_LEVEL_target_tbl || ' : ' || SQL%ROWCOUNT);
1901
1902 -- debug message
1903 jty_log(FND_LOG.LEVEL_PROCEDURE,
1904 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.end',
1905 'End of the procedure JTY_ASSIGN_BULK_PUB.Process_Level_Winners ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1906
1907 EXCEPTION
1908
1909 WHEN FND_API.G_EXC_ERROR THEN
1910 x_return_status := FND_API.G_RET_STS_ERROR;
1911 jty_log(FND_LOG.LEVEL_EXCEPTION,
1912 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.other',
1913 'JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed with FND_API.G_EXC_ERROR exception for ' ||
1914 'UPPER LEVEL : ' || p_UPPER_LEVEL_FROM_ROOT || ' LOWER LEVEL : ' || p_LOWER_LEVEL_FROM_ROOT);
1915
1916 WHEN OTHERS THEN
1917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1918 jty_log(FND_LOG.LEVEL_EXCEPTION,
1919 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Level_Winners.other',
1920 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
1921
1922 END Process_Level_Winners;
1923
1924
1925 -- ***************************************************
1926 -- API Specifications
1927 -- ***************************************************
1928 PROCEDURE Process_Final_Level_Winners (
1929 p_terr_LEVEL_target_tbl IN VARCHAR2,
1930 p_terr_L5_target_tbl IN VARCHAR2,
1931 p_matches_target IN VARCHAR2,
1932 p_source_id IN NUMBER,
1933 p_run_mode IN VARCHAR2,
1934 p_date_effective IN BOOLEAN,
1935 x_return_status OUT NOCOPY VARCHAR2,
1936 p_worker_id IN NUMBER
1937 )
1938 AS
1939
1940 l_dyn_str VARCHAR2(32767);
1941 l_denorm_table_name VARCHAR2(30);
1942
1943 BEGIN
1944
1945 -- debug message
1946 jty_log(FND_LOG.LEVEL_PROCEDURE,
1947 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.start',
1948 'Start of the procedure JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1949
1950 x_return_status := FND_API.G_RET_STS_SUCCESS;
1951
1952 IF (p_date_effective) THEN
1953 l_denorm_table_name := 'JTY_DENORM_DEA_RULES_ALL';
1954 ELSE
1955 l_denorm_table_name := 'JTF_TERR_DENORM_RULES_ALL';
1956 END IF;
1957
1958 l_dyn_str := ' ' ||
1959 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
1960 ' ( ' ||
1961 ' trans_object_id ' ||
1962 ' , trans_detail_object_id ' ||
1963 ' , txn_date ' ||
1964 ' , WIN_TERR_ID ' ||
1965 ' , UL_TERR_ID ' ||
1966 ' , LL_TERR_ID ' ||
1967 ' , worker_id ' ||
1968 ' ) ' ||
1969 ' (SELECT ' ||
1970 ' TL.trans_object_id ' ||
1971 ' , TL.trans_detail_object_id ' ||
1972 ' , TL.txn_date ' ||
1973 ' , TL.WIN_TERR_ID ' ||
1974 ' , TL.UL_terr_id ' ||
1975 ' , TL.terr_id ' ||
1976 ' , :B_WORKER_ID ' || --p_worker_id ||
1977 ' FROM ( ' ||
1978 ' SELECT ';
1979
1980 IF (p_run_mode = 'BATCH') THEN
1981 /* Batch TAE */
1982 NULL;
1983 ELSE
1984 /* Real-time TAE */
1985 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
1986 END IF;
1987
1988 l_dyn_str := l_dyn_str ||
1989 ' DISTINCT ' ||
1990 ' m.trans_object_id ' ||
1991 ' , m.trans_detail_object_id ' ||
1992 ' , m.txn_date ' ||
1993 ' , M.TERR_ID WIN_TERR_ID ' ||
1994 ' , UL.related_terr_id UL_TERR_ID ' ||
1995 ' , UL.num_winners UL_NUM_WINNERS ' ||
1996 ' , M.terr_id TERR_ID ' ||
1997 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
1998 ' m.trans_object_id ' ||
1999 ' , m.trans_detail_object_id ' ||
2000 ' , UL.related_terr_id ' ||
2001 ' ORDER BY M.absolute_rank DESC, M.TERR_ID ) AS LL_TERR_RANK ' ||
2002 ' FROM ' || p_matches_target || ' M ' ||
2003 ' , ' || l_denorm_table_name || ' UL ' ||
2004 ' , ' || p_terr_L5_target_tbl || ' ML ' || /* FINAL LEVEL TABLE */
2005 ' , jtf_terr_all jt ' ||
2006 ' , ' || l_denorm_table_name || ' LL ' ||
2007 ' WHERE UL.level_from_root = 6 ' || /* UPPER level */
2008 ' AND UL.source_id = :b1_source_id ' ||
2009 ' AND UL.terr_id = M.TERR_ID ' ||
2010 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
2011 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
2012 ' M.trans_detail_object_id IS NULL ) ' ||
2013 ' AND M.trans_object_id = ML.trans_object_id ' ||
2014 ' AND M.worker_id = ML.WORKER_ID' ||
2015 ' AND M.worker_id = :B_WORKER_ID ' ||
2016 ' AND ML.worker_id = :B_WORKER_ID ' ||
2017 ' AND jt.terr_id = LL.related_terr_id ' ||
2018 ' AND LL.level_from_root >= 6 ' || /* FINAL LEVEL(S) */
2019 ' AND LL.source_id = :b2_source_id ' ||
2020 ' AND LL.terr_id = M.TERR_ID ' ||
2021 ' ) TL ' ||
2022 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
2023 ' ) ';
2024
2025 BEGIN
2026 EXECUTE IMMEDIATE l_dyn_str USING
2027 p_worker_id
2028 , p_source_id
2029 , p_worker_id
2030 , p_worker_id
2031 , p_source_id;
2032
2033 EXCEPTION
2034 WHEN NO_DATA_FOUND THEN
2035 NULL;
2036 END;
2037
2038 -- debug message
2039 jty_log(FND_LOG.LEVEL_STATEMENT,
2040 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.num_rows',
2041 'Number of rows inserted into ' || p_terr_LEVEL_target_tbl || ' : ' || SQL%ROWCOUNT);
2042
2043 -- debug message
2044 jty_log(FND_LOG.LEVEL_PROCEDURE,
2045 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.end',
2046 'End of the procedure JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2047
2048 EXCEPTION
2049 WHEN OTHERS THEN
2050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2051 jty_log(FND_LOG.LEVEL_EXCEPTION,
2052 'jtf.plsql.JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners.other',
2053 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
2054
2055 END Process_Final_Level_Winners;
2056
2057
2058 -- ***************************************************
2059 -- API Specifications
2060 -- ***************************************************
2061 -- api name : process_nmc_match
2062 -- type : private.
2063 -- function :
2064 -- pre-reqs :
2065 -- notes: API designed to get the transaction objs that satisfy changed terr defn.
2066 --
2067 PROCEDURE process_nmc_match
2068 ( p_source_id IN NUMBER,
2069 p_trans_id IN NUMBER,
2070 p_program_name IN VARCHAR2,
2071 p_request_id IN NUMBER,
2072 x_return_status OUT NOCOPY VARCHAR2,
2073 x_msg_count OUT NOCOPY NUMBER,
2074 x_msg_data OUT NOCOPY VARCHAR2,
2075 ERRBUF OUT NOCOPY VARCHAR2,
2076 RETCODE OUT NOCOPY VARCHAR2
2077 ) AS
2078
2079 l_match_sql VARCHAR2(32767);
2080 l_status VARCHAR2(30);
2081 l_industry VARCHAR2(30);
2082 l_owner VARCHAR2(30);
2083 l_trans_target VARCHAR2(30);
2084 l_nm_trans_target VARCHAR2(30);
2085 l_insert_stmt VARCHAR2(3000);
2086 l_select_stmt VARCHAR2(3000);
2087 first_time BOOLEAN;
2088 l_indent VARCHAR2(30);
2089 l_final_sql VARCHAR2(32767);
2090 l_sysdate DATE;
2091 l_delete_sql VARCHAR2(3000);
2092
2093
2094 CURSOR c_get_qualrel_prod(cl_source_id number, cl_trans_id number) IS
2095 SELECT jtqp.relation_product
2096 FROM jtf_tae_qual_products jtqp
2097 WHERE jtqp.source_id = cl_source_id
2098 AND jtqp.trans_object_type_id = cl_trans_id
2099 ORDER BY jtqp.relation_product DESC;
2100
2101 CURSOR c1(p_table_name IN VARCHAR2, p_owner IN VARCHAR2) is
2102 SELECT column_name
2103 FROM all_tab_columns
2104 WHERE table_name = p_table_name
2105 AND owner = p_owner
2106 AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
2107 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
2108 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
2109
2110 L_SCHEMA_NOTFOUND EXCEPTION;
2111 BEGIN
2112
2113 -- debug message
2114 jty_log(FND_LOG.LEVEL_PROCEDURE,
2115 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.start',
2116 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_nmc_match ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2117
2118 x_return_status := FND_API.G_RET_STS_SUCCESS;
2119
2120 /* Get the schema name corresponding to JTF application */
2121 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
2122 NULL;
2123 END IF;
2124
2125 IF (l_owner IS NULL) THEN
2126 RAISE L_SCHEMA_NOTFOUND;
2127 END IF;
2128
2129 /* Get the TRANS and NM_TRANS table names and active transaction type batch SQL */
2130 BEGIN
2131 SELECT tup.batch_trans_table_name
2132 ,tup.batch_nm_trans_table_name
2133 INTO l_trans_target
2134 ,l_nm_trans_target
2135 FROM jty_trans_usg_pgm_details tup
2136 WHERE tup.source_id = p_source_id
2137 AND tup.trans_type_id = p_trans_id
2138 AND tup.program_name = p_program_name;
2139 EXCEPTION
2140 WHEN NO_DATA_FOUND THEN
2141 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
2142 ' program name : ' || p_program_name;
2143 RAISE;
2144 END;
2145
2146 /* Delete from TRANS the txn objs present in NM_TRANS table */
2147 commit;
2148 EXECUTE IMMEDIATE 'alter session enable parallel dml';
2149
2150 l_delete_sql :=
2151 'DELETE FROM ' || l_trans_target || ' A' || g_new_LINE ||
2152 'WHERE EXISTS ( ' || g_new_line ||
2153 ' SELECT 1 ' || g_new_line ||
2154 ' FROM ' || l_nm_trans_target || ' B' || g_new_line ||
2155 ' WHERE A.trans_object_id = B.trans_object_id )';
2156 EXECUTE IMMEDIATE l_delete_sql;
2157
2158 commit;
2159 EXECUTE IMMEDIATE 'alter session disable parallel dml';
2160
2161 /* Initialize local variables */
2162 first_time := TRUE;
2163 l_indent := ' ';
2164 l_sysdate := SYSDATE;
2165
2166 /* Form the insert statement to insert transaction objects into TRANS table */
2167 l_insert_stmt := 'INSERT INTO ' || l_trans_target || '(';
2168 l_select_stmt := '(SELECT ';
2169
2170 FOR column_names in c1(l_trans_target, l_owner) LOOP
2171 IF (first_time) THEN
2172 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
2173 l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
2174 first_time := FALSE;
2175 ELSE
2176 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
2177 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
2178 END IF;
2179 END LOOP;
2180
2181 /* Standard WHO columns */
2182 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
2183 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
2184 g_new_line || l_indent || ',CREATION_DATE ' ||
2185 g_new_line || l_indent || ',CREATED_BY ' ||
2186 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
2187 g_new_line || l_indent || ',REQUEST_ID ' ||
2188 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
2189 g_new_line || l_indent || ',PROGRAM_ID ' ||
2190 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
2191 g_new_line || l_indent || ',TXN_DATE ' ||
2192 g_new_line || l_indent || ',WORKER_ID ' ||
2193 g_new_line || ')';
2194
2195 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2196 g_new_line || l_indent || ',''' || g_user_id || '''' ||
2197 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2198 g_new_line || l_indent || ',''' || g_user_id || '''' ||
2199 g_new_line || l_indent || ',''' || g_login_id || '''' ||
2200 g_new_line || l_indent || ',''' || p_request_id || '''' ||
2201 g_new_line || l_indent || ',''' || g_appl_id || '''' ||
2202 g_new_line || l_indent || ',''' || g_program_id || '''' ||
2203 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2204 g_new_line || l_indent || ',''' || l_sysdate || '''' ||
2205 g_new_line || l_indent || ', 1 ';
2206
2207 l_final_sql := l_insert_stmt || g_new_line ||
2208 l_select_stmt || g_new_line || 'FROM ' || l_nm_trans_target || g_new_line || ' ) ';
2209
2210 -- debug message
2211 jty_log(FND_LOG.LEVEL_STATEMENT,
2212 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.final_sql',
2213 substr('Insert satement : ' || l_final_sql, 1, 4000));
2214
2215 /* Insert all the transaction objects into the TRANS table */
2216 EXECUTE IMMEDIATE l_final_sql;
2217
2218 FOR jtf_csr IN c_get_qualrel_prod(p_source_id, p_trans_id) LOOP
2219 BEGIN
2220 SELECT batch_nmc_match_sql
2221 INTO l_match_sql
2222 FROM jty_tae_attr_products_sql
2223 WHERE source_id = p_source_id
2224 AND trans_type_id = p_trans_id
2225 AND program_name = p_program_name
2226 AND attr_relation_product = jtf_csr.relation_product;
2227 EXCEPTION
2228 WHEN NO_DATA_FOUND THEN
2229 x_msg_data := 'No matching SQL found corresponding to source : ' || p_source_id || ' trans : ' || p_trans_id ||
2230 ' Program name : ' || p_program_name || ' relation product : ' || jtf_csr.relation_product;
2231 RAISE;
2232 END;
2233
2234 -- EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
2235 -- g_appl_id, g_program_id, l_sysdate, p_request_id, l_sysdate;
2236
2237 -- Adding IF condition to add more bind variables for the following variables. Fix for bug 9118732.
2238 IF ((mod(jtf_csr.relation_product,79) = 0 and jtf_csr.relation_product/79 <> 1) or -- account classification
2239 (mod(jtf_csr.relation_product,137) = 0 and jtf_csr.relation_product/137 <> 1) or -- lead expected purchase
2240 (mod(jtf_csr.relation_product,113) = 0 and jtf_csr.relation_product/113 <> 1) or -- purchase amount
2241 (mod(jtf_csr.relation_product,131) = 0 and jtf_csr.relation_product/131 <> 1) or -- lead inventory item
2242 (mod(jtf_csr.relation_product,163) = 0 and jtf_csr.relation_product/163 <> 1) or -- opportunity inventory item
2243 (mod(jtf_csr.relation_product,167) = 0 and jtf_csr.relation_product/167 <> 1) or -- opportunity classification
2244 (mod(jtf_csr.relation_product,139) = 0 and jtf_csr.relation_product/139 <> 1)) THEN -- opportunity expected purchase
2245
2246 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
2247 g_appl_id, g_program_id, l_sysdate, p_request_id, l_sysdate, p_request_id, l_sysdate;
2248 ELSE
2249
2250 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
2251 g_appl_id, g_program_id, l_sysdate, p_request_id, l_sysdate;
2252
2253 END IF;
2254
2255 -- debug message
2256 jty_log(FND_LOG.LEVEL_STATEMENT,
2257 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.match',
2258 'Number of records inserted for qualifier combination ' || jtf_csr.relation_product || ' : ' || SQL%ROWCOUNT);
2259 END LOOP;
2260
2261 -- debug message
2262 jty_log(FND_LOG.LEVEL_PROCEDURE,
2263 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.end',
2264 'End of the procedure JTY_ASSIGN_BULK_PUB.process_nmc_match ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2265
2266 EXCEPTION
2267 WHEN L_SCHEMA_NOTFOUND THEN
2268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2269 x_msg_data := 'Schema name corresponding to JTF application not found';
2270 RETCODE := 2;
2271 x_msg_count := 1;
2272 ERRBUF := x_msg_data;
2273 jty_log(FND_LOG.LEVEL_EXCEPTION,
2274 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.l_schema_notfound',
2275 'Schema name corresponding to the JTF application not found');
2276
2277 WHEN NO_DATA_FOUND THEN
2278 x_return_status := FND_API.G_RET_STS_ERROR ;
2279 RETCODE := 2;
2280 x_msg_count := 1;
2281 ERRBUF := x_msg_data;
2282 jty_log(FND_LOG.LEVEL_EXCEPTION,
2283 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.no_data_found',
2284 x_msg_data);
2285
2286 WHEN OTHERS THEN
2287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2288 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2289 x_msg_count := 1;
2290 RETCODE := 2;
2291 ERRBUF := x_msg_data;
2292 jty_log(FND_LOG.LEVEL_EXCEPTION,
2293 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_nmc_match.other',
2294 substr(x_msg_data, 1, 4000));
2295
2296 END process_nmc_match;
2297
2298
2299 -- ***************************************************
2300 -- API Specifications
2301 -- ***************************************************
2302 -- api name : collect_trans_data
2303 -- type : public.
2304 -- function :
2305 -- pre-reqs :
2306 -- notes: API designed to insert transaction objects into TRANS table
2307 -- for "TOTAL", "INCREMENTAL" and "DATE EFFECTIVE" mode.
2308 --
2309 -- Parameter p_oic_mode added only for OIC.
2310 --
2311 PROCEDURE collect_trans_data
2312 ( p_api_version_number IN NUMBER,
2313 p_init_msg_list IN VARCHAR2,
2314 p_source_id IN NUMBER,
2315 p_trans_id IN NUMBER,
2316 p_program_name IN VARCHAR2,
2317 p_mode IN VARCHAR2,
2318 p_where IN VARCHAR2,
2319 p_no_of_workers IN NUMBER,
2320 p_percent_analyzed IN NUMBER,
2321 p_request_id IN NUMBER,
2322 x_return_status OUT NOCOPY VARCHAR2,
2323 x_msg_count OUT NOCOPY NUMBER,
2324 x_msg_data OUT NOCOPY VARCHAR2,
2325 ERRBUF OUT NOCOPY VARCHAR2,
2326 RETCODE OUT NOCOPY VARCHAR2,
2327 p_oic_mode IN VARCHAR2 DEFAULT 'NOOIC'
2328 ) AS
2329
2330 l_api_name CONSTANT VARCHAR2(30) := 'collect_trans_data';
2331 l_api_version_number CONSTANT NUMBER := 1.0;
2332
2333 BEGIN
2334
2335 -- debug message
2336 jty_log(FND_LOG.LEVEL_PROCEDURE,
2337 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.start',
2338 'Start of the procedure JTY_ASSIGN_BULK_PUB.collect_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2339
2340 /* Standard call to check for call compatibility. */
2341 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2342 p_api_version_number,
2343 l_api_name,
2344 G_PKG_NAME) THEN
2345
2346 x_msg_data := 'API FND_API.Compatible_API_Call has failed';
2347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2348 END IF;
2349
2350 /* Initialize message list if p_init_msg_list is set to TRUE. */
2351 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2352 FND_MSG_PUB.initialize;
2353 END IF;
2354
2355 x_return_status := FND_API.G_RET_STS_SUCCESS;
2356
2357 IF p_oic_mode = 'NOOIC' or p_oic_mode = 'CLEAR' THEN
2358
2359 -- debug message
2360 jty_log(FND_LOG.LEVEL_STATEMENT,
2361 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.param_value',
2362 'Source : ' || p_source_id || ' Trans : ' || p_trans_id || ' Program Name : ' || p_program_name ||
2363 ' Mode : ' || p_mode || ' Where clause : ' || p_where || ' Number of workers : ' || p_no_of_workers ||
2364 ' Percent Analyzed : ' || p_percent_analyzed);
2365
2366 /* Clear the interface tables */
2367 clear_trans_data (
2368 p_source_id => p_source_id,
2369 p_trans_id => p_trans_id,
2370 p_program_name => p_program_name,
2371 p_mode => p_mode,
2372 p_request_id => p_request_id,
2373 x_return_status => x_return_status,
2374 x_msg_count => x_msg_count,
2375 x_msg_data => x_msg_data,
2376 errbuf => errbuf,
2377 retcode => retcode);
2378
2379 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2380 -- debug message
2381 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.clear_trans_data has failed';
2382 jty_log(FND_LOG.LEVEL_EXCEPTION,
2383 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.clear_trans_data',
2384 x_msg_data);
2385
2386 RAISE FND_API.G_EXC_ERROR;
2387 END IF;
2388
2389 -- debug message
2390 jty_log(FND_LOG.LEVEL_EVENT,
2391 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_clear_trans_data',
2392 'API clear_trans_data completed successfully');
2393 END IF; -- p_oic_mode = 'NOOIC' or p_oic_mode = 'CLEAR'
2394
2395 IF p_oic_mode = 'NOOIC' or p_oic_mode = 'INSERT' THEN
2396
2397 /* Insert the txn objects into TRANS table */
2398 insert_trans_data (
2399 p_source_id => p_source_id,
2400 p_trans_id => p_trans_id,
2401 p_program_name => p_program_name,
2402 p_mode => p_mode,
2403 p_where => p_where,
2404 p_no_of_workers => p_no_of_workers,
2405 p_request_id => p_request_id,
2406 x_return_status => x_return_status,
2407 x_msg_count => x_msg_count,
2408 x_msg_data => x_msg_data,
2409 errbuf => errbuf,
2410 retcode => retcode,
2411 p_oic_mode => p_oic_mode
2412 );
2413
2414 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2415 -- debug message
2416 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_trans_data has failed';
2417 jty_log(FND_LOG.LEVEL_EXCEPTION,
2418 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_trans_data',
2419 x_msg_data);
2420
2421 RAISE FND_API.G_EXC_ERROR;
2422 END IF;
2423
2424 -- debug message
2425 jty_log(FND_LOG.LEVEL_EVENT,
2426 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_insert_trans_data',
2427 'API insert_trans_data completed successfully');
2428 END IF; -- p_oic_mode = 'NOOIC' or p_oic_mode = 'INSERT'
2429
2430
2431 IF (p_mode = 'INCREMENTAL') THEN
2432
2433 /* Synchronize trans and nm_trans table and insert objects */
2434 /* that satisfy the modified territory definition */
2435 process_nmc_match (
2436 p_source_id => p_source_id,
2437 p_trans_id => p_trans_id,
2438 p_program_name => p_program_name,
2439 p_request_id => p_request_id,
2440 x_return_status => x_return_status,
2441 x_msg_count => x_msg_count,
2442 x_msg_data => x_msg_data,
2443 errbuf => errbuf,
2444 retcode => retcode);
2445
2446 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2447 -- debug message
2448 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.process_nmc_match has failed';
2449 jty_log(FND_LOG.LEVEL_EXCEPTION,
2450 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.process_nmc_match',
2451 x_msg_data);
2452
2453 RAISE FND_API.G_EXC_ERROR;
2454 END IF;
2455
2456 -- debug message
2457 jty_log(FND_LOG.LEVEL_EVENT,
2458 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_process_nmc_match',
2459 'API process_nmc_match completed successfully');
2460
2461 /* insert into NM_TRANS txn objs that are assigned to changed territories */
2462 insert_nm_trans_data (
2463 p_source_id => p_source_id,
2464 p_trans_id => p_trans_id,
2465 p_program_name => p_program_name,
2466 p_request_id => p_request_id,
2467 x_return_status => x_return_status,
2468 x_msg_count => x_msg_count,
2469 x_msg_data => x_msg_data,
2470 errbuf => errbuf,
2471 retcode => retcode);
2472
2473 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2474 -- debug message
2475 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_nm_trans_data has failed';
2476 jty_log(FND_LOG.LEVEL_EXCEPTION,
2477 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_nm_trans_data',
2478 x_msg_data);
2479
2480 RAISE FND_API.G_EXC_ERROR;
2481 END IF;
2482
2483 -- debug message
2484 jty_log(FND_LOG.LEVEL_EVENT,
2485 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_insert_nm_trans_data',
2486 'API insert_nm_trans_data completed successfully');
2487
2488 END IF; /* end IF (p_mode = 'INCREMENTAL') */
2489
2490 IF p_oic_mode = 'NOOIC' or p_oic_mode = 'POST' THEN
2491
2492 get_winners_parallel_setup (
2493 p_source_id => p_source_id,
2494 p_trans_id => p_trans_id,
2495 p_program_name => p_program_name,
2496 p_mode => p_mode,
2497 p_no_of_workers => p_no_of_workers,
2498 p_percent_analyzed => p_percent_analyzed,
2499 p_request_id => p_request_id,
2500 x_return_status => x_return_status,
2501 x_msg_count => x_msg_count,
2502 x_msg_data => x_msg_data,
2503 ERRBUF => ERRBUF,
2504 RETCODE => RETCODE
2505 );
2506
2507 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2508 -- debug message
2509 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.get_winners_parallel_setup has failed';
2510 jty_log(FND_LOG.LEVEL_EXCEPTION,
2511 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.get_winners_parallel_setup',
2512 x_msg_data);
2513
2514 RAISE FND_API.G_EXC_ERROR;
2515 END IF;
2516 END IF; -- p_oic_mode = 'NOOIC' or p_oic_mode = 'POST'
2517
2518 retcode := 0;
2519 errbuf := null;
2520
2521 -- debug message
2522 jty_log(FND_LOG.LEVEL_PROCEDURE,
2523 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end',
2524 'End of the procedure JTY_ASSIGN_BULK_PUB.collect_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2525
2526 EXCEPTION
2527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2529 x_msg_count := 1;
2530 RETCODE := 2;
2531 ERRBUF := x_msg_data;
2532 jty_log(FND_LOG.LEVEL_EXCEPTION,
2533 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.g_exc_unexpected_error',
2534 x_msg_data);
2535
2536 WHEN NO_DATA_FOUND THEN
2537 x_return_status := FND_API.G_RET_STS_ERROR ;
2538 RETCODE := 2;
2539 x_msg_count := 1;
2540 ERRBUF := x_msg_data;
2541 jty_log(FND_LOG.LEVEL_EXCEPTION,
2542 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.no_data_found',
2543 x_msg_data);
2544
2545 WHEN FND_API.G_EXC_ERROR THEN
2546 RETCODE := 2;
2547 x_msg_count := 1;
2548 ERRBUF := x_msg_data;
2549 jty_log(FND_LOG.LEVEL_EXCEPTION,
2550 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.g_exc_error',
2551 x_msg_data);
2552
2553 WHEN OTHERS THEN
2554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2555 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2556 x_msg_count := 1;
2557 RETCODE := 2;
2558 ERRBUF := x_msg_data;
2559 jty_log(FND_LOG.LEVEL_EXCEPTION,
2560 'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.other',
2561 substr(x_msg_data, 1, 4000));
2562
2563 END collect_trans_data;
2564
2565
2566 -- ***************************************************
2567 -- API Specifications
2568 -- ***************************************************
2569 -- api name : process_winners
2570 -- type : private.
2571 -- function :
2572 -- pre-reqs :
2573 -- notes: API designed to get the winning territories for the
2574 -- transaction objs, it supports multiple worker architecture
2575 --
2576 PROCEDURE process_winners
2577 ( p_source_id IN NUMBER,
2578 p_trans_id IN NUMBER,
2579 p_program_name IN VARCHAR2,
2580 p_mode IN VARCHAR2,
2581 p_percent_analyzed IN NUMBER,
2582 p_worker_id IN NUMBER,
2583 x_return_status OUT NOCOPY VARCHAR2,
2584 x_msg_count OUT NOCOPY NUMBER,
2585 x_msg_data OUT NOCOPY VARCHAR2,
2586 ERRBUF OUT NOCOPY VARCHAR2,
2587 RETCODE OUT NOCOPY VARCHAR2,
2588 p_oic_mode IN VARCHAR2
2589 ) AS
2590
2591 l_match_target VARCHAR2(40);
2592 l_umatch_target VARCHAR2(40);
2593 l_l1_target VARCHAR2(40);
2594 l_l2_target VARCHAR2(40);
2595 l_l3_target VARCHAR2(40);
2596 l_l4_target VARCHAR2(40);
2597 l_l5_target VARCHAR2(40);
2598 l_wt_target VARCHAR2(40);
2599 l_winner_target VARCHAR2(40);
2600 l_uwinner_target VARCHAR2(40);
2601 l_mp_winner_target VARCHAR2(40);
2602 l_dmc_winner_target VARCHAR2(40);
2603 lp_sysdate DATE;
2604 l_multi_level_winning_flag VARCHAR2(1);
2605 l_date_effective BOOLEAN;
2606 l_status VARCHAR2(30);
2607 l_industry VARCHAR2(30);
2608 l_fnd_schema VARCHAR2(30);
2609 l_max_terr NUMBER;
2610 l_no_ind_cols NUMBER;
2611
2612
2613 l_dyn_str LONG;
2614
2615 L_SCHEMA_NOTFOUND EXCEPTION;
2616 L_NO_MATCH_TERR EXCEPTION;
2617 BEGIN
2618
2619 -- debug message
2620 jty_log(FND_LOG.LEVEL_PROCEDURE,
2621 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.start',
2622 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_winner ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2623
2624 x_return_status := FND_API.G_RET_STS_SUCCESS;
2625 lp_sysdate := SYSDATE;
2626
2627 IF (p_mode = 'DATE EFFECTIVE') THEN
2628 l_date_effective := true;
2629 ELSE
2630 l_date_effective := false;
2631 END IF;
2632
2633 BEGIN
2634 SELECT batch_match_table_name
2635 ,batch_unique_match_table_name
2636 ,batch_l1_winner_table_name
2637 ,batch_l2_winner_table_name
2638 ,batch_l3_winner_table_name
2639 ,batch_l4_winner_table_name
2640 ,batch_l5_winner_table_name
2641 ,batch_wt_winner_table_name
2642 ,batch_winner_table_name
2643 ,batch_unique_winner_table_name
2644 ,batch_mp_winner_table_name || p_worker_id
2645 ,batch_dmc_winner_table_name || p_worker_id
2646 ,multi_level_winning_flag
2647 INTO l_match_target
2648 ,l_umatch_target
2649 ,l_l1_target
2650 ,l_l2_target
2651 ,l_l3_target
2652 ,l_l4_target
2653 ,l_l5_target
2654 ,l_wt_target
2655 ,l_winner_target
2656 ,l_uwinner_target
2657 ,l_mp_winner_target
2658 ,l_dmc_winner_target
2659 ,l_multi_level_winning_flag
2660 FROM jty_trans_usg_pgm_details tup
2661 WHERE tup.source_id = p_source_id
2662 AND tup.trans_type_id = p_trans_id
2663 AND tup.program_name = p_program_name;
2664 EXCEPTION
2665 WHEN NO_DATA_FOUND THEN
2666 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
2667 ' program name : ' || p_program_name;
2668 RAISE;
2669 END;
2670
2671 COMMIT;
2672
2673 IF p_oic_mode = 'NOOIC' OR p_oic_mode = 'WINNER/POPULATE'
2674 THEN
2675
2676 IF (l_multi_level_winning_flag <> 'Y') THEN
2677 l_dyn_str :=
2678 ' INSERT INTO ' || l_winner_target || ' i ' ||
2679 ' ( ' ||
2680 ' TRANS_OBJECT_ID ' ||
2681 ' ,TRANS_DETAIL_OBJECT_ID ' ||
2682 ' ,WORKER_ID ' ||
2683 ' ,SOURCE_ID ' ||
2684 ' ,TRANS_OBJECT_TYPE_ID ' ||
2685 ' ,LAST_UPDATE_DATE ' ||
2686 ' ,LAST_UPDATED_BY ' ||
2687 ' ,CREATION_DATE ' ||
2688 ' ,CREATED_BY ' ||
2689 ' ,LAST_UPDATE_LOGIN ' ||
2690 ' ,REQUEST_ID ' ||
2691 ' ,PROGRAM_APPLICATION_ID ' ||
2692 ' ,PROGRAM_ID ' ||
2693 ' ,PROGRAM_UPDATE_DATE ' ||
2694 ' ,TERR_ID ' ||
2695 ' ,ABSOLUTE_RANK ' ||
2696 ' ,TOP_LEVEL_TERR_ID ' ||
2697 ' ,RESOURCE_ID ' ||
2698 ' ,RESOURCE_TYPE ' ||
2699 ' ,GROUP_ID ' ||
2700 ' ,ROLE_ID ' ||
2701 ' ,ROLE ' ||
2702 ' ,PRIMARY_CONTACT_FLAG ' ||
2703 ' ,PERSON_ID ' ||
2704 ' ,ORG_ID ' ||
2705 ' ,TERR_RSC_ID ' ||
2706 ' ,FULL_ACCESS_FLAG ' ||
2707 ' ) ' ||
2708 ' ( ' ||
2709
2710 ' SELECT ' || -- DISTINCT ' ||
2711 ' WT.trans_object_id ' ||
2712 ' , WT.trans_detail_object_id ' ||
2713 ' , :bv_worker_id ' || --p_worker_id ||
2714 ' , :BV1_SOURCE_ID ' ||
2715 ' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
2716 ' , :BV1_LAST_UPDATE_DATE ' ||
2717 ' , :BV1_LAST_UPDATED_BY ' ||
2718 ' , :BV1_CREATION_DATE ' ||
2719 ' , :BV1_CREATED_BY ' ||
2720 ' , :BV1_LAST_UPDATE_LOGIN ' ||
2721 ' , :BV1_REQUEST_ID ' ||
2722 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
2723 ' , :BV1_PROGRAM_ID ' ||
2724 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
2725 ' , WT.terr_id ' ||
2726 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
2727 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
2728 ' , jtr.resource_id ' ||
2729 ' , jtr.resource_type ' ||
2730 ' , jtr.group_id ' ||
2731 ' , inv.role_id ' ||
2732 ' , jtr.role ' ||
2733 ' , jtr.primary_contact_flag ' ||
2734 ' , jtr.PERSON_ID ' ||
2735 ' , jtr.org_id ' ||
2736 ' , jtr.terr_rsc_id ' ||
2737 ' , decode(jtra.trans_access_code, ''FULL_ACCESS'', ''Y'', ''N'') ' ||
2738 ' FROM ( /* WINNERS ILV */ ' ||
2739
2740 ' SELECT ' ||
2741 ' o.trans_object_id ' ||
2742 ' ,o.trans_detail_object_id ' ||
2743 ' ,o.terr_id ' ||
2744 ' ,o.txn_date ' ||
2745 ' FROM ' ||
2746 ' ( SELECT ' ||
2747 ' i.trans_id ' ||
2748 ' ,i.trans_object_id ' ||
2749 ' ,i.trans_detail_object_id ' ||
2750 ' ,i.terr_id ' ||
2751 ' ,i.top_level_terr_id ' ||
2752 ' ,i.txn_date ' ||
2753 ' ,RANK() OVER ( PARTITION BY ' ||
2754 ' i.trans_id ' ||
2755 ' , i.trans_object_id ' ||
2756 ' , i.trans_detail_object_id ' ||
2757 ' , i.top_level_terr_id ' ||
2758 ' ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK ' ||
2759 ' FROM ' || l_match_target || ' i ' ||
2760 ' WHERE i.worker_id = :bv_worker_id ) o ' ||
2761 ' WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id) ' ||
2762 ' ) WT ' ||
2763 ' , jtf_terr_rsc_all jtr ' ||
2764 ' , jtf_terr_rsc_access_all jtra ' ||
2765 ' , jtf_qual_types_all jqta ' ||
2766 ' , (SELECT ' ||
2767 ' max(role_id) role_id ' ||
2768 ' ,role_code role_code ' ||
2769 ' FROM jtf_rs_roles_b ' ||
2770 ' GROUP BY role_code ) inv ' ||
2771 ' WHERE WT.terr_id = jtr.terr_id ' ||
2772 ' AND jtr.end_date_active >= WT.txn_date ' ||
2773 ' AND jtr.start_date_active <= WT.txn_date ' ||
2774 ' AND jtr.resource_type <> ''RS_ROLE'' ' ||
2775 ' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
2776 ' AND jtr.role = inv.role_code(+) ' ||
2777 ' AND jtra.access_type = jqta.name ' ||
2778 ' AND jtra.trans_access_code <> ''NONE'' ' ||
2779 ' AND jqta.qual_type_id = :bv_trans_id ';
2780
2781 BEGIN
2782
2783 EXECUTE IMMEDIATE l_dyn_str USING
2784 p_worker_id /* :bv_worker_id */
2785 , p_source_id /* :BV1_SOURCE_ID */
2786 , p_trans_id /* :BV1_TRANS_OBJECT_TYPE_ID */
2787 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
2788 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
2789 , lp_sysdate /* :BV1_CREATION_DATE */
2790 , G_USER_ID /* :BV1_CREATED_BY */
2791 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
2792 , G_REQUEST_ID /* :BV1_REQUEST_ID */
2793 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
2794 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
2795 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
2796 , p_worker_id /* :bv_worker_id */
2797 , p_trans_id;
2798
2799 COMMIT; -- after modifying table in parallel
2800
2801 jty_log(FND_LOG.LEVEL_STATEMENT,
2802 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.winner_num_row',
2803 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
2804 EXCEPTION
2805 WHEN NO_DATA_FOUND THEN
2806 NULL;
2807 END;
2808
2809 ELSE
2810 jty_log(FND_LOG.LEVEL_STATEMENT,
2811 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.winning_process',
2812 'Star of winning process');
2813
2814 IF (FND_INSTALLATION.GET_APP_INFO('FND', l_status, l_industry, l_fnd_schema)) THEN
2815 NULL;
2816 END IF;
2817
2818 IF (l_fnd_schema IS NULL) THEN
2819 RAISE L_SCHEMA_NOTFOUND;
2820 END IF;
2821
2822 /* Get the maximun number of territories in the matching combinations */
2823 l_dyn_str :=
2824 'SELECT max(count(terr_id)) ' ||
2825 'FROM ' || l_match_target || ' ' ||
2826 'WHERE worker_id = :worker_id ' ||
2827 'GROUP BY trans_object_id, trans_detail_object_id';
2828
2829 EXECUTE IMMEDIATE l_dyn_str INTO l_max_terr USING p_worker_id;
2830
2831 jty_log(FND_LOG.LEVEL_STATEMENT,
2832 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_max_terr',
2833 'Number of maximum territories for a matching combination for the worker_id : ' || p_worker_id || ' : ' || l_max_terr);
2834
2835 IF ((l_max_terr IS NULL) OR (l_max_terr = 0)) THEN
2836 x_msg_data := 'No row in ' || l_match_target || ' for worker_id = ' || p_worker_id;
2837 x_return_status := FND_API.G_RET_STS_SUCCESS;
2838 RETCODE := 0;
2839 x_msg_count := 1;
2840 ERRBUF := null;
2841 RAISE L_NO_MATCH_TERR;
2842 END IF;
2843
2844 /* create a temp table that will contain all the transaction objects */
2845 /* and their matching territories in a single row */
2846 /* drop the table if it alreday exists */
2847 BEGIN
2848 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.drop_table, 'drop table ' || l_mp_winner_target, l_mp_winner_target);
2849 EXCEPTION
2850 when others then
2851 jty_log(FND_LOG.LEVEL_STATEMENT,
2852 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_target',
2853 'Table ' || l_mp_winner_target || ' drop FAILED ');
2854 -- RAISE ;
2855 END;
2856
2857 jty_log(FND_LOG.LEVEL_STATEMENT,
2858 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_target',
2859 'Table ' || l_mp_winner_target || ' successfully dropped');
2860
2861 /* create a temp table that will contain all the transaction objects */
2862 /* add the columns trans_object_id, trans_details_object_id, txn_date */
2863 l_dyn_str :=
2864 'create table ' || l_mp_winner_target || ' as ( ' ||
2865 ' select trans_object_id, trans_detail_object_id, txn_date, max(rownum) link ';
2866
2867 /* create column for each of the matching territories */
2868 for i IN 1..l_max_terr loop
2869 l_dyn_str := l_dyn_str || ' ,nvl(max(decode(trank, ' || i || ', terr_id, null)), 0) terr_id' || i;
2870 end loop;
2871
2872 /* get the data from match table */
2873 l_dyn_str := l_dyn_str ||
2874 ' from ' ||
2875 ' (select trans_object_id, trans_detail_object_id, terr_id, txn_date, ' ||
2876 ' dense_rank() over(partition by trans_object_id, trans_detail_object_id order by terr_id) trank ' ||
2877 ' from ' || l_match_target ||
2878 ' where worker_id = ' || p_worker_id || ' ) ' ||
2879 ' group by trans_object_id, trans_detail_object_id, txn_date )';
2880
2881 jty_log(FND_LOG.LEVEL_STATEMENT,
2882 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_target',
2883 substr(l_dyn_str, 1, 4000));
2884
2885 /* create the table in jtf schema */
2886 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.create_table, l_dyn_str, l_mp_winner_target);
2887
2888 jty_log(FND_LOG.LEVEL_STATEMENT,
2889 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_target',
2890 'Table ' || l_mp_winner_target || ' successfully created');
2891
2892 /* create a temp table that will contain only the distinct */
2893 /* combination of matching territories in a single row */
2894 /* drop the table if it alreday exists */
2895 BEGIN
2896 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.drop_table, 'drop table ' || l_dmc_winner_target, l_dmc_winner_target);
2897 EXCEPTION
2898 when others then
2899 jty_log(FND_LOG.LEVEL_STATEMENT,
2900 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target',
2901 'Table ' || l_dmc_winner_target || ' drop FAILED');
2902 -- RAISE;
2903 END;
2904
2905 jty_log(FND_LOG.LEVEL_STATEMENT,
2906 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target',
2907 'Table ' || l_dmc_winner_target || ' successfully dropped');
2908
2909 /* Form the create table statement */
2910 l_dyn_str :=
2911 'create table ' || l_dmc_winner_target || ' as ( ' ||
2912 ' select max(rownum) link ';
2913 for i in 1..l_max_terr loop
2914 l_dyn_str := l_dyn_str ||
2915 ' ,terr_id' || i;
2916 end loop;
2917 l_dyn_str := l_dyn_str || ' from ' || l_mp_winner_target || ' group by terr_id1 ';
2918 for i in 2..l_max_terr loop
2919 l_dyn_str := l_dyn_str ||
2920 ' ,terr_id' || i;
2921 end loop;
2922 l_dyn_str := l_dyn_str || ' )';
2923
2924 jty_log(FND_LOG.LEVEL_STATEMENT,
2925 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target',
2926 substr(l_dyn_str, 1, 4000));
2927
2928 /* Create the table in JTF schema */
2929 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.create_table, l_dyn_str, l_dmc_winner_target);
2930
2931 jty_log(FND_LOG.LEVEL_STATEMENT,
2932 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target',
2933 'Table ' || l_dmc_winner_target || ' successfully created');
2934
2935 /* Insert into umatch table the unique combination of matching territories */
2936 l_dyn_str :=
2937 'insert into ' || l_umatch_target ||
2938 ' (trans_object_id, ' ||
2939 ' trans_detail_object_id, ' ||
2940 ' terr_id, ' ||
2941 ' absolute_rank, ' ||
2942 ' txn_date, ' ||
2943 -- ' LAST_UPDATE_DATE, ' ||
2944 -- ' LAST_UPDATED_BY, ' ||
2945 -- ' CREATION_DATE, ' ||
2946 -- ' CREATED_BY, ' ||
2947 -- ' LAST_UPDATE_LOGIN, ' ||
2948 -- ' REQUEST_ID, ' ||
2949 -- ' PROGRAM_APPLICATION_ID, ' ||
2950 -- ' PROGRAM_ID, ' ||
2951 -- ' PROGRAM_UPDATE_DATE, ' ||
2952 ' worker_id) ( ';
2953
2954 -- SOLIN, bug 5633062
2955 -- move jtf_terr_all out of inline view to avoid
2956 -- database bug
2957 l_dyn_str := l_dyn_str ||
2958 'select ' ||
2959 ' ilv.trans_object_id, ' ||
2960 ' -1, ' || --trans_detail_object_id
2961 ' ilv.terr_id, ' ||
2962 ' jt.absolute_rank, ' ||
2963 ' null, ' ||
2964 p_worker_id ||
2965 ' from (' ;
2966
2967 for i IN 1..l_max_terr loop
2968 l_dyn_str := l_dyn_str ||
2969 'select ' ||
2970 ' a.link trans_object_id, ' ||
2971 -- ' -1 trans_detail_object_id, ' ||
2972 ' a.terr_id' || i || ' terr_id ' ||
2973 -- ' b.absolute_rank, ' ||
2974 'from ' || l_dmc_winner_target || ' a ';
2975 if (i < l_max_terr) then
2976 l_dyn_str := l_dyn_str || ' union ';
2977 end if;
2978 end loop;
2979 l_dyn_str := l_dyn_str || ' ) ilv, ' ||
2980 ' jtf_terr_all jt ' ||
2981 'where ilv.terr_id = jt.terr_id) ';
2982
2983 /* SOLIN, the oritinal code follows: 5633062
2984 for i IN 1..l_max_terr loop
2985 l_dyn_str := l_dyn_str ||
2986 'select ' ||
2987 ' a.link trans_object_id, ' ||
2988 ' -1 trans_detail_object_id, ' ||
2989 ' a.terr_id' || i || ' terr_id, ' ||
2990 ' b.absolute_rank, ' ||
2991 ' null, ' ||
2992 -- ' ''' || lp_sysdate || ''', ' ||
2993 -- ' ' || g_user_id || ', ' ||
2994 -- ' ''' || lp_sysdate || ''', ' ||
2995 -- ' ' || g_user_id || ', ' ||
2996 -- ' ' || g_login_id || ', ' ||
2997 -- ' ' || g_request_id || ', ' ||
2998 -- ' ' || g_appl_id || ', ' ||
2999 -- ' ' || g_program_id || ', ' ||
3000 -- ' ''' || lp_sysdate || ''', ' ||
3001 ' ' || p_worker_id || ' ' ||
3002 'from ' || l_dmc_winner_target || ' a, jtf_terr_all b ' ||
3003 'where a.terr_id' || i || ' = b.terr_id ';
3004 if (i < l_max_terr) then
3005 l_dyn_str := l_dyn_str || ' union ';
3006 end if;
3007 end loop;
3008 l_dyn_str := l_dyn_str || ' )';
3009 */
3010 jty_log(FND_LOG.LEVEL_STATEMENT,
3011 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_umatch',
3012 substr(l_dyn_str, 1, 4000));
3013
3014 execute immediate l_dyn_str;
3015
3016 jty_log(FND_LOG.LEVEL_STATEMENT,
3017 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_umatch',
3018 'Data successfully inserted in umatch table');
3019
3020 /* Create in index on l_dmc_winner_target */
3021 IF (l_max_terr > 32) THEN
3022 l_no_ind_cols := 32;
3023 ELSE
3024 l_no_ind_cols := l_max_terr;
3025 END IF;
3026 l_dyn_str :=
3027 'create index ' || l_dmc_winner_target || '_N1 on ' || l_dmc_winner_target || ' (terr_id1 ';
3028 for i in 2..l_no_ind_cols loop
3029 l_dyn_str := l_dyn_str ||
3030 ' , terr_id' || i;
3031 end loop;
3032 l_dyn_str := l_dyn_str || ' )';
3033
3034 jty_log(FND_LOG.LEVEL_STATEMENT,
3035 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_dmc_winner_target_index',
3036 substr(l_dyn_str, 1, 4000));
3037
3038 /* Create the index in JTF schema */
3039 ad_ddl.do_ddl(l_fnd_schema, 'JTF', ad_ddl.create_index, l_dyn_str, l_dmc_winner_target || 'N');
3040
3041 /* update the temporary table to maintain the link */
3042 /* between txn objects and unique terr matching combinations */
3043 l_dyn_str :=
3044 'update ' || l_mp_winner_target || ' a ' ||
3045 ' set link = ( ' ||
3046 ' select /*+ use_index(' || l_dmc_winner_target || '_N1) */ link from ' || l_dmc_winner_target || ' b ' ||
3047 ' where a.terr_id1 = b.terr_id1 ';
3048 for i in 2..l_max_terr loop
3049 l_dyn_str := l_dyn_str ||
3050 ' and a.terr_id' || i || ' = b.terr_id' || i || ' ';
3051 end loop;
3052 l_dyn_str := l_dyn_str || ' )';
3053
3054 jty_log(FND_LOG.LEVEL_STATEMENT,
3055 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_update',
3056 substr(l_dyn_str, 1, 4000));
3057
3058 execute immediate l_dyn_str;
3059
3060 jty_log(FND_LOG.LEVEL_STATEMENT,
3061 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_update',
3062 'Link successfully updated in ' || l_dmc_winner_target || ' table');
3063
3064 /* Process first level */
3065 Process_Level_Winners (
3066 p_terr_LEVEL_target_tbl => l_L1_target,
3067 p_terr_PARENT_LEVEL_tbl => l_L1_target,
3068 p_UPPER_LEVEL_FROM_ROOT => 1,
3069 p_LOWER_LEVEL_FROM_ROOT => 1,
3070 p_matches_target => l_umatch_target,
3071 p_source_id => p_source_id,
3072 p_run_mode => 'BATCH',
3073 p_date_effective => l_date_effective,
3074 x_return_status => x_return_status,
3075 p_worker_id => p_worker_id
3076 );
3077
3078 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3079 jty_log(FND_LOG.LEVEL_EXCEPTION,
3080 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l1',
3081 x_msg_data);
3082 RAISE FND_API.G_EXC_ERROR;
3083 END IF;
3084
3085 COMMIT;
3086
3087 /*
3088 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3089 p_table_name => l_L1_target
3090 , p_percent => p_percent_analyzed
3091 , x_return_status => x_return_status );
3092
3093 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3094 x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L1_target;
3095 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3096 jty_log(FND_LOG.LEVEL_EXCEPTION,
3097 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l1_analyze',
3098 x_msg_data);
3099 END IF;
3100 RAISE FND_API.G_EXC_ERROR;
3101 END IF;
3102 */
3103
3104 /* Process second level */
3105 Process_Level_Winners (
3106 p_terr_LEVEL_target_tbl => l_L2_target,
3107 p_terr_PARENT_LEVEL_tbl => l_L1_target,
3108 p_UPPER_LEVEL_FROM_ROOT => 1,
3109 p_LOWER_LEVEL_FROM_ROOT => 2,
3110 p_matches_target => l_umatch_target,
3111 p_source_id => p_source_id,
3112 p_run_mode => 'BATCH',
3113 p_date_effective => l_date_effective,
3114 x_return_status => x_return_status,
3115 p_worker_id => p_worker_id
3116 );
3117
3118 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3119 jty_log(FND_LOG.LEVEL_EXCEPTION,
3120 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l2',
3121 x_msg_data);
3122 RAISE FND_API.G_EXC_ERROR;
3123 END IF;
3124
3125 COMMIT;
3126
3127 /*
3128 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3129 p_table_name => l_L2_target
3130 , p_percent => p_percent_analyzed
3131 , x_return_status => x_return_status );
3132
3133 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3134 x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L2_target;
3135 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3136 jty_log(FND_LOG.LEVEL_EXCEPTION,
3137 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l2_analyze',
3138 x_msg_data);
3139 END IF;
3140 RAISE FND_API.G_EXC_ERROR;
3141 END IF;
3142 */
3143
3144 /* Process third level */
3145 Process_Level_Winners (
3146 p_terr_LEVEL_target_tbl => l_L3_target,
3147 p_terr_PARENT_LEVEL_tbl => l_L2_target,
3148 p_UPPER_LEVEL_FROM_ROOT => 2,
3149 p_LOWER_LEVEL_FROM_ROOT => 3,
3150 p_matches_target => l_umatch_target,
3151 p_source_id => p_source_id,
3152 p_run_mode => 'BATCH',
3153 p_date_effective => l_date_effective,
3154 x_return_status => x_return_status,
3155 p_worker_id => p_worker_id
3156 );
3157
3158 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3159 jty_log(FND_LOG.LEVEL_EXCEPTION,
3160 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l3',
3161 x_msg_data);
3162 RAISE FND_API.G_EXC_ERROR;
3163 END IF;
3164
3165 COMMIT;
3166
3167 /*
3168 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3169 p_table_name => l_L3_target
3170 , p_percent => p_percent_analyzed
3171 , x_return_status => x_return_status );
3172
3173 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3174 x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L3_target;
3175 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3176 jty_log(FND_LOG.LEVEL_EXCEPTION,
3177 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l3_analyze',
3178 x_msg_data);
3179 END IF;
3180 RAISE FND_API.G_EXC_ERROR;
3181 END IF;
3182 */
3183
3184 /* Process fourth level */
3185 Process_Level_Winners (
3186 p_terr_LEVEL_target_tbl => l_L4_target,
3187 p_terr_PARENT_LEVEL_tbl => l_L3_target,
3188 p_UPPER_LEVEL_FROM_ROOT => 3,
3189 p_LOWER_LEVEL_FROM_ROOT => 4,
3190 p_matches_target => l_umatch_target,
3191 p_source_id => p_source_id,
3192 p_run_mode => 'BATCH',
3193 p_date_effective => l_date_effective,
3194 x_return_status => x_return_status,
3195 p_worker_id => p_worker_id
3196 );
3197
3198 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3199 jty_log(FND_LOG.LEVEL_EXCEPTION,
3200 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l4',
3201 x_msg_data);
3202 RAISE FND_API.G_EXC_ERROR;
3203 END IF;
3204
3205 COMMIT;
3206
3207 /*
3208 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3209 p_table_name => l_L4_target
3210 , p_percent => p_percent_analyzed
3211 , x_return_status => x_return_status );
3212
3213 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3214 x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L4_target;
3215 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3216 jty_log(FND_LOG.LEVEL_EXCEPTION,
3217 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l4_analyze',
3218 x_msg_data);
3219 END IF;
3220 RAISE FND_API.G_EXC_ERROR;
3221 END IF;
3222 */
3223
3224 /* Process fifth level */
3225 Process_Level_Winners (
3226 p_terr_LEVEL_target_tbl => l_L5_target,
3227 p_terr_PARENT_LEVEL_tbl => l_L4_target,
3228 p_UPPER_LEVEL_FROM_ROOT => 4,
3229 p_LOWER_LEVEL_FROM_ROOT => 5,
3230 p_matches_target => l_umatch_target,
3231 p_source_id => p_source_id,
3232 p_run_mode => 'BATCH',
3233 p_date_effective => l_date_effective,
3234 x_return_status => x_return_status,
3235 p_worker_id => p_worker_id
3236 );
3237
3238 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3239 jty_log(FND_LOG.LEVEL_EXCEPTION,
3240 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l5',
3241 x_msg_data);
3242 RAISE FND_API.G_EXC_ERROR;
3243 END IF;
3244
3245 COMMIT;
3246
3247 /*
3248 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3249 p_table_name => l_L5_target
3250 , p_percent => p_percent_analyzed
3251 , x_return_status => x_return_status );
3252
3253 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3254 x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_L5_target;
3255 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3256 jty_log(FND_LOG.LEVEL_EXCEPTION,
3257 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.l5_analyze',
3258 x_msg_data);
3259 END IF;
3260 RAISE FND_API.G_EXC_ERROR;
3261 END IF;
3262 */
3263
3264 /* Process final level */
3265 Process_Final_Level_Winners (
3266 p_terr_LEVEL_target_tbl => l_wt_target,
3267 p_terr_L5_target_tbl => l_L5_target,
3268 p_matches_target => l_umatch_target,
3269 p_source_id => p_source_id,
3270 p_run_mode => 'BATCH',
3271 p_date_effective => l_date_effective,
3272 x_return_status => x_return_status,
3273 p_worker_id => p_worker_id
3274 );
3275
3276 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3277 jty_log(FND_LOG.LEVEL_EXCEPTION,
3278 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.wt',
3279 x_msg_data);
3280 RAISE FND_API.G_EXC_ERROR;
3281 END IF;
3282
3283 COMMIT;
3284
3285 /*
3286 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3287 p_table_name => l_wt_target
3288 , p_percent => p_percent_analyzed
3289 , x_return_status => x_return_status );
3290
3291 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3292 x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_wt_target;
3293 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3294 jty_log(FND_LOG.LEVEL_EXCEPTION,
3295 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.wt_analyze',
3296 x_msg_data);
3297 END IF;
3298 RAISE FND_API.G_EXC_ERROR;
3299 END IF;
3300 */
3301
3302 jty_log(FND_LOG.LEVEL_EXCEPTION,
3303 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.begin_populate_winners',
3304 'Start of populating the winner table');
3305
3306 l_dyn_str :=
3307 ' INSERT INTO ' || l_uwinner_target || ' i ' ||
3308 ' ( ' ||
3309 ' TRANS_OBJECT_ID ' ||
3310 ' ,TRANS_DETAIL_OBJECT_ID ' ||
3311 ' ,WORKER_ID ' ||
3312 ' ,LAST_UPDATE_DATE ' ||
3313 ' ,LAST_UPDATED_BY ' ||
3314 ' ,CREATION_DATE ' ||
3315 ' ,CREATED_BY ' ||
3316 ' ,LAST_UPDATE_LOGIN ' ||
3317 ' ,REQUEST_ID ' ||
3318 ' ,PROGRAM_APPLICATION_ID ' ||
3319 ' ,PROGRAM_ID ' ||
3320 ' ,PROGRAM_UPDATE_DATE ' ||
3321 ' ,TERR_ID ' ||
3322 ' ,ABSOLUTE_RANK ' ||
3323 ' ,TOP_LEVEL_TERR_ID ' ||
3324 ' ) ' ||
3325 ' ( ' ||
3326 ' SELECT ' || -- DISTINCT ' ||
3327 ' WINNERS.trans_object_id ' ||
3328 ' , WINNERS.trans_detail_object_id ' ||
3329 ' , :bv_worker_id ' || --p_worker_id ||
3330 ' , :BV1_LAST_UPDATE_DATE ' ||
3331 ' , :BV1_LAST_UPDATED_BY ' ||
3332 ' , :BV1_CREATION_DATE ' ||
3333 ' , :BV1_CREATED_BY ' ||
3334 ' , :BV1_LAST_UPDATE_LOGIN ' ||
3335 ' , :BV1_REQUEST_ID ' ||
3336 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
3337 ' , :BV1_PROGRAM_ID ' ||
3338 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
3339 ' , WINNERS.WIN_terr_id ' ||
3340 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
3341 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
3342 ' FROM ( /* WINNERS ILV */ ' ||
3343 ' SELECT ILV.trans_object_id ' ||
3344 ' , ILV.trans_detail_object_id ' ||
3345 ' , ILV.WIN_TERR_ID ' ||
3346 ' FROM ( SELECT trans_object_id ' ||
3347 ' , trans_detail_object_id ' ||
3348 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3349 ' FROM ' || l_L1_target ||
3350 ' WHERE WORKER_ID = :bv_worker_id ' ||
3351 ' MINUS ' ||
3352 ' SELECT trans_object_id ' ||
3353 ' , trans_detail_object_id ' ||
3354 ' , ul_terr_id WIN_TERR_ID ' ||
3355 ' FROM ' || l_L2_target ||
3356 ' WHERE WORKER_ID = :bv_worker_id ' ||
3357 ' ) ILV ' ||
3358
3359 ' UNION ALL ' ||
3360
3361 ' SELECT ILV.trans_object_id ' ||
3362 ' , ILV.trans_detail_object_id ' ||
3363 ' , ILV.WIN_TERR_ID ' ||
3364 ' FROM ( SELECT trans_object_id ' ||
3365 ' , trans_detail_object_id ' ||
3366 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3367 ' FROM ' || l_L2_target ||
3368 ' WHERE WORKER_ID = :bv_worker_id ' ||
3369 ' MINUS ' ||
3370 ' SELECT trans_object_id ' ||
3371 ' , trans_detail_object_id ' ||
3372 ' , ul_terr_id WIN_TERR_ID ' ||
3373 ' FROM ' || l_L3_target ||
3374 ' WHERE WORKER_ID = :bv_worker_id ' ||
3375 ' ) ILV ' ||
3376
3377 ' UNION ALL ' ||
3378
3379 ' SELECT ILV.trans_object_id ' ||
3380 ' , ILV.trans_detail_object_id ' ||
3381 ' , ILV.WIN_TERR_ID ' ||
3382 ' FROM ( SELECT trans_object_id ' ||
3383 ' , trans_detail_object_id ' ||
3384 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3385 ' FROM ' || l_L3_target ||
3386 ' WHERE WORKER_ID = :bv_worker_id ' ||
3387 ' MINUS ' ||
3388 ' SELECT trans_object_id ' ||
3389 ' , trans_detail_object_id ' ||
3390 ' , ul_terr_id WIN_TERR_ID ' ||
3391 ' FROM ' || l_L4_target ||
3392 ' WHERE WORKER_ID = :bv_worker_id ' ||
3393 ' ) ILV ' ||
3394
3395 ' UNION ALL ' ||
3396
3397 ' SELECT ILV.trans_object_id ' ||
3398 ' , ILV.trans_detail_object_id ' ||
3399 ' , ILV.WIN_TERR_ID ' ||
3400 ' FROM ( SELECT trans_object_id ' ||
3401 ' , trans_detail_object_id ' ||
3402 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3403 ' FROM ' || l_L4_target ||
3404 ' WHERE WORKER_ID = :bv_worker_id ' ||
3405 ' MINUS ' ||
3406 ' SELECT trans_object_id ' ||
3407 ' , trans_detail_object_id ' ||
3408 ' , ul_terr_id WIN_TERR_ID ' ||
3409 ' FROM ' || l_L5_target ||
3410 ' WHERE WORKER_ID = :bv_worker_id ' ||
3411 ' ) ILV ' ||
3412
3413 ' UNION ALL ' ||
3414
3415 ' SELECT ILV.trans_object_id ' ||
3416 ' , ILV.trans_detail_object_id ' ||
3417 ' , ILV.WIN_TERR_ID ' ||
3418 ' FROM ( SELECT trans_object_id ' ||
3419 ' , trans_detail_object_id ' ||
3420 ' , WIN_TERR_ID WIN_TERR_ID ' ||
3421 ' FROM ' || l_L5_target ||
3422 ' WHERE WORKER_ID = :bv_worker_id ' ||
3423 ' MINUS ' ||
3424 ' SELECT trans_object_id ' ||
3425 ' , trans_detail_object_id ' ||
3426 ' , ul_terr_id WIN_TERR_ID ' ||
3427 ' FROM ' || l_WT_target ||
3428 ' WHERE WORKER_ID = :bv_worker_id ' ||
3429 ' ) ILV ' ||
3430
3431 ' UNION ALL ' ||
3432
3433 ' SELECT trans_object_id ' ||
3434 ' , trans_detail_object_id ' ||
3435 ' , WIN_TERR_ID ' ||
3436 ' FROM ' || l_WT_target ||
3437 ' WHERE WORKER_ID = :bv_worker_id ' ||
3438
3439 ' ) WINNERS ' ||
3440 ' ) ';
3441
3442 BEGIN
3443
3444 EXECUTE IMMEDIATE l_dyn_str USING
3445 p_worker_id /* :bv_worker_id */
3446 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
3447 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
3448 , lp_sysdate /* :BV1_CREATION_DATE */
3449 , G_USER_ID /* :BV1_CREATED_BY */
3450 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
3451 , G_REQUEST_ID /* :BV1_REQUEST_ID */
3452 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
3453 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
3454 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
3455 , p_worker_id /* :bv_worker_id */ --1
3456 , p_worker_id /* :bv_worker_id */
3457 , p_worker_id /* :bv_worker_id */
3458 , p_worker_id /* :bv_worker_id */
3459 , p_worker_id /* :bv_worker_id */ --5
3460 , p_worker_id /* :bv_worker_id */
3461 , p_worker_id /* :bv_worker_id */
3462 , p_worker_id /* :bv_worker_id */
3463 , p_worker_id /* :bv_worker_id */
3464 , p_worker_id /* :bv_worker_id */ --10
3465 , p_worker_id; /* :bv_worker_id */
3466
3467 jty_log(FND_LOG.LEVEL_STATEMENT,
3468 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner_num_row',
3469 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
3470
3471 COMMIT; -- after modifying table in parallel
3472
3473 EXCEPTION
3474 WHEN NO_DATA_FOUND THEN
3475 NULL;
3476 END;
3477
3478 l_dyn_str :=
3479 ' INSERT INTO ' || l_winner_target || ' i ' ||
3480 ' ( ' ||
3481 ' TRANS_OBJECT_ID ' ||
3482 ' ,TRANS_DETAIL_OBJECT_ID ' ||
3483 ' ,WORKER_ID ' ||
3484 ' ,SOURCE_ID ' ||
3485 ' ,TRANS_OBJECT_TYPE_ID ' ||
3486 ' ,LAST_UPDATE_DATE ' ||
3487 ' ,LAST_UPDATED_BY ' ||
3488 ' ,CREATION_DATE ' ||
3489 ' ,CREATED_BY ' ||
3490 ' ,LAST_UPDATE_LOGIN ' ||
3491 ' ,REQUEST_ID ' ||
3492 ' ,PROGRAM_APPLICATION_ID ' ||
3493 ' ,PROGRAM_ID ' ||
3494 ' ,PROGRAM_UPDATE_DATE ' ||
3495 ' ,TERR_ID ' ||
3496 ' ,ABSOLUTE_RANK ' ||
3497 ' ,TOP_LEVEL_TERR_ID ' ||
3498 ' ,RESOURCE_ID ' ||
3499 ' ,RESOURCE_TYPE ' ||
3500 ' ,GROUP_ID ' ||
3501 ' ,ROLE_ID ' ||
3502 ' ,ROLE ' ||
3503 ' ,PRIMARY_CONTACT_FLAG ' ||
3504 ' ,PERSON_ID ' ||
3505 ' ,ORG_ID ' ||
3506 ' ,TERR_RSC_ID ' ||
3507 ' ,FULL_ACCESS_FLAG ' ||
3508 ' ) ' ||
3509 ' ( ' ||
3510 ' SELECT ' || -- DISTINCT ' ||
3511 ' WINNERS.trans_object_id ' ||
3512 ' , WINNERS.trans_detail_object_id ' ||
3513 ' , :bv_worker_id ' || --p_worker_id ||
3514 ' , :BV1_SOURCE_ID ' ||
3515 ' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
3516 ' , :BV1_LAST_UPDATE_DATE ' ||
3517 ' , :BV1_LAST_UPDATED_BY ' ||
3518 ' , :BV1_CREATION_DATE ' ||
3519 ' , :BV1_CREATED_BY ' ||
3520 ' , :BV1_LAST_UPDATE_LOGIN ' ||
3521 ' , :BV1_REQUEST_ID ' ||
3522 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
3523 ' , :BV1_PROGRAM_ID ' ||
3524 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
3525 ' , WINNERS.terr_id ' ||
3526 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
3527 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
3528 ' , jtr.resource_id ' ||
3529 ' , jtr.resource_type ' ||
3530 ' , jtr.group_id ' ||
3531 ' , inv.role_id ' ||
3532 ' , jtr.role ' ||
3533 ' , jtr.primary_contact_flag ' ||
3534 ' , jtr.PERSON_ID ' ||
3535 ' , jtr.org_id ' ||
3536 ' , jtr.terr_rsc_id ' ||
3537 ' , decode(jtra.trans_access_code, ''FULL_ACCESS'', ''Y'', ''N'') ' ||
3538 ' FROM ( /* WINNERS ILV */ ' ||
3539 ' SELECT a.trans_object_id ' ||
3540 ' , a.trans_detail_object_id ' ||
3541 ' , b.TERR_ID ' ||
3542 ' , a.txn_date ' ||
3543 ' FROM ' || l_mp_winner_target || ' a, ' || l_uwinner_target || ' b ' ||
3544 ' WHERE b.WORKER_ID = :bv_worker_id ' ||
3545 ' AND a.link = b.trans_object_id ' ||
3546 ' ) WINNERS ' ||
3547 ' , jtf_terr_rsc_all jtr ' ||
3548 ' , jtf_terr_rsc_access_all jtra ' ||
3549 ' , jtf_qual_types_all jqta ' ||
3550 ' , (SELECT ' ||
3551 ' max(role_id) role_id ' ||
3552 ' ,role_code role_code ' ||
3553 ' FROM jtf_rs_roles_b ' ||
3554 ' GROUP BY role_code ) inv ' ||
3555 ' WHERE WINNERS.terr_id = jtr.terr_id ' ||
3556 ' AND jtr.end_date_active >= WINNERS.txn_date ' ||
3557 ' AND jtr.start_date_active <= WINNERS.txn_date ' ||
3558 ' AND jtr.resource_type <> ''RS_ROLE'' ' ||
3559 ' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
3560 ' AND jtr.role = inv.role_code(+) ' ||
3561 ' AND jtra.access_type = jqta.name ' ||
3562 ' AND jtra.trans_access_code <> ''NONE'' ' ||
3563 ' AND jqta.qual_type_id = :bv_trans_id ' ||
3564 ' ) ';
3565
3566 BEGIN
3567
3568 EXECUTE IMMEDIATE l_dyn_str USING
3569 p_worker_id /* :bv_worker_id */
3570 , p_source_id /* :BV1_SOURCE_ID */
3571 , p_trans_id /* :BV1_TRANS_OBJECT_TYPE_ID */
3572 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
3573 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
3574 , lp_sysdate /* :BV1_CREATION_DATE */
3575 , G_USER_ID /* :BV1_CREATED_BY */
3576 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
3577 , G_REQUEST_ID /* :BV1_REQUEST_ID */
3578 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
3579 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
3580 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
3581 , p_worker_id /* :bv_worker_id */ --1
3582 , p_trans_id;
3583
3584 jty_log(FND_LOG.LEVEL_STATEMENT,
3585 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner_num_row',
3586 'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
3587
3588 COMMIT; -- after modifying table in parallel
3589
3590 EXCEPTION
3591 WHEN NO_DATA_FOUND THEN
3592 NULL;
3593 END;
3594 END IF; /* end IF (l_multi_level_winning_flag <> 'Y') */
3595
3596 END IF; -- End of loop for p_OIC_mode check
3597
3598 IF p_oic_mode = 'WINNER/POST' OR p_oic_mode = 'NOOIC'
3599 THEN
3600 /* Analyze Winners table */
3601 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3602 p_table_name => l_winner_target
3603 , p_percent => p_percent_analyzed
3604 , x_return_status => x_return_status );
3605
3606 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3607 x_msg_data := 'API JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for ' || l_winner_target;
3608 jty_log(FND_LOG.LEVEL_EXCEPTION,
3609 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner_analyze',
3610 x_msg_data);
3611 RAISE FND_API.G_EXC_ERROR;
3612 END IF;
3613 END IF;
3614
3615 /* Program completed successfully */
3616 ERRBUF := null;
3617 x_return_status := FND_API.G_RET_STS_SUCCESS;
3618 RETCODE := 0;
3619
3620 -- debug message
3621 jty_log(FND_LOG.LEVEL_PROCEDURE,
3622 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.end',
3623 'End of the procedure JTY_ASSIGN_BULK_PUB.process_winner ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
3624
3625 EXCEPTION
3626 WHEN L_SCHEMA_NOTFOUND THEN
3627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3628 x_msg_data := 'Schema name corresponding to the FND application not found';
3629 RETCODE := 2;
3630 x_msg_count := 1;
3631 ERRBUF := x_msg_data;
3632 jty_log(FND_LOG.LEVEL_EXCEPTION,
3633 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.l_schema_notfound',
3634 x_msg_data);
3635
3636 WHEN NO_DATA_FOUND THEN
3637 x_return_status := FND_API.G_RET_STS_ERROR ;
3638 RETCODE := 2;
3639 x_msg_count := 1;
3640 ERRBUF := x_msg_data;
3641 jty_log(FND_LOG.LEVEL_EXCEPTION,
3642 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.no_data_found',
3643 x_msg_data);
3644
3645 WHEN L_NO_MATCH_TERR THEN
3646 jty_log(FND_LOG.LEVEL_EXCEPTION,
3647 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.l_no_match_terr',
3648 x_msg_data);
3649
3650 WHEN FND_API.G_EXC_ERROR THEN
3651 x_return_status := FND_API.G_RET_STS_ERROR ;
3652 RETCODE := 2;
3653 x_msg_count := 1;
3654 ERRBUF := x_msg_data;
3655 jty_log(FND_LOG.LEVEL_EXCEPTION,
3656 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.g_exc_error',
3657 x_msg_data);
3658
3659 WHEN OTHERS THEN
3660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3661 x_msg_data := SQLCODE || ' : ' || SQLERRM;
3662 x_msg_count := 1;
3663 RETCODE := 2;
3664 ERRBUF := x_msg_data;
3665 jty_log(FND_LOG.LEVEL_EXCEPTION,
3666 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winner.other',
3667 substr(x_msg_data, 1, 4000));
3668
3669 END process_winners;
3670
3671
3672 -- ***************************************************
3673 -- API Specifications
3674 -- ***************************************************
3675 -- api name : process_match
3676 -- type : private.
3677 -- function :
3678 -- pre-reqs :
3679 -- notes: API designed to get the matching territories for the
3680 -- transaction objs, it supports multiple worker architecture
3681 --
3682 PROCEDURE process_match
3683 ( p_source_id IN NUMBER,
3684 p_trans_id IN NUMBER,
3685 p_program_name IN VARCHAR2,
3686 p_mode IN VARCHAR2,
3687 p_percent_analyzed IN NUMBER,
3688 p_worker_id IN NUMBER,
3689 x_return_status OUT NOCOPY VARCHAR2,
3690 x_msg_count OUT NOCOPY NUMBER,
3691 x_msg_data OUT NOCOPY VARCHAR2,
3692 ERRBUF OUT NOCOPY VARCHAR2,
3693 RETCODE OUT NOCOPY VARCHAR2,
3694 p_oic_mode IN VARCHAR2,
3695 p_terr_id IN NUMBER
3696 ) AS
3697
3698 l_trans_target VARCHAR2(40);
3699 l_match_target VARCHAR2(40);
3700 l_sql_stmt VARCHAR2(200);
3701 l_no_of_records NUMBER;
3702 l_match_sql VARCHAR2(32767);
3703 l_sysdate DATE;
3704
3705 CURSOR c_get_qualrel_prod(cl_source_id number, cl_trans_id number) IS
3706 SELECT jtqp.relation_product
3707 FROM jtf_tae_qual_products jtqp
3708 WHERE jtqp.source_id = cl_source_id
3709 AND jtqp.trans_object_type_id = cl_trans_id
3710 ORDER BY jtqp.relation_product DESC;
3711
3712 CURSOR c_dea_get_qualrel_prod(cl_source_id number, cl_trans_id number) IS
3713 SELECT jtqp.attr_relation_product
3714 FROM jty_dea_attr_products jtqp
3715 WHERE jtqp.source_id = cl_source_id
3716 AND jtqp.trans_type_id = cl_trans_id
3717 ORDER BY jtqp.attr_relation_product DESC;
3718
3719 BEGIN
3720
3721 -- debug message
3722 jty_log(FND_LOG.LEVEL_PROCEDURE,
3723 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.start',
3724 'Start of the procedure JTY_ASSIGN_BULK_PUB.process_match ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
3725
3726 x_return_status := FND_API.G_RET_STS_SUCCESS;
3727 l_sysdate := SYSDATE;
3728
3729 BEGIN
3730 SELECT decode(p_mode, 'TOTAL', tup.batch_trans_table_name
3731 , 'INCREMENTAL', tup.batch_nm_trans_table_name
3732 , 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
3733 ,batch_match_table_name
3734 INTO l_trans_target
3735 ,l_match_target
3736 FROM jty_trans_usg_pgm_details tup
3737 WHERE tup.source_id = p_source_id
3738 AND tup.trans_type_id = p_trans_id
3739 AND tup.program_name = p_program_name;
3740 EXCEPTION
3741 WHEN NO_DATA_FOUND THEN
3742 x_msg_data := 'No row in jty_trans_usg_pgm_details corresponding to usage : ' || p_source_id || ' transaction : ' || p_trans_id ||
3743 ' program name : ' || p_program_name;
3744 RAISE;
3745 END;
3746
3747 IF p_oic_mode = 'NOOIC' OR p_oic_mode = 'MATCH/POPULATE'
3748 THEN
3749
3750 l_sql_stmt := 'SELECT COUNT(*) FROM ' || l_trans_target || ' WHERE worker_id = :bv_worker_id';
3751 EXECUTE IMMEDIATE l_sql_stmt INTO l_no_of_records USING p_worker_id;
3752
3753 IF (l_no_of_records <= 0) THEN
3754 x_msg_data := 'No row in ' || l_trans_target || ' for worker_id = ' || p_worker_id;
3755 x_return_status := FND_API.G_RET_STS_SUCCESS;
3756 RETCODE := 0;
3757 x_msg_count := 1;
3758 ERRBUF := null;
3759 RAISE FND_API.G_EXC_ERROR;
3760 END IF;
3761
3762 IF (p_mode = 'DATE EFFECTIVE') THEN
3763 IF p_terr_id is NULL THEN
3764 FOR jtf_csr IN c_dea_get_qualrel_prod(p_source_id, p_trans_id) LOOP
3765 BEGIN
3766 SELECT batch_dea_match_sql
3767 INTO l_match_sql
3768 FROM jty_dea_attr_products_sql
3769 WHERE source_id = p_source_id
3770 AND trans_type_id = p_trans_id
3771 AND program_name = p_program_name
3772 AND attr_relation_product = jtf_csr.attr_relation_product;
3773 EXCEPTION
3774 WHEN NO_DATA_FOUND THEN
3775 x_msg_data := 'No matching SQL found corresponding to source : ' || p_source_id || ' trans : ' || p_trans_id ||
3776 ' Program name : ' || p_program_name || ' relation product : ' || jtf_csr.attr_relation_product;
3777 RAISE;
3778 END;
3779
3780 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
3781 g_appl_id, g_program_id, l_sysdate, p_worker_id;
3782 jty_log(FND_LOG.LEVEL_STATEMENT,
3783 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.dea_match',
3784 'Number of records inserted for qualifier combination ' || jtf_csr.attr_relation_product || ' : ' || SQL%ROWCOUNT);
3785
3786 END LOOP;
3787 ELSIF p_terr_id IS NOT NULL THEN
3788 FOR jtf_csr IN c_dea_get_qualrel_prod(p_source_id, p_trans_id) LOOP
3789 BEGIN
3790 SELECT batch_dea_match_sql_with_terr
3791 INTO l_match_sql
3792 FROM jty_dea_attr_products_sql
3793 WHERE source_id = p_source_id
3794 AND trans_type_id = p_trans_id
3795 AND program_name = p_program_name
3796 AND attr_relation_product = jtf_csr.attr_relation_product;
3797 EXCEPTION
3798 WHEN NO_DATA_FOUND THEN
3799 x_msg_data := 'No matching SQL found corresponding to source : ' || p_source_id || ' trans : ' || p_trans_id ||
3800 ' Program name : ' || p_program_name || ' relation product : ' || jtf_csr.attr_relation_product;
3801 RAISE;
3802 END;
3803
3804 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
3805 g_appl_id, g_program_id, l_sysdate, p_worker_id, p_terr_id;
3806 jty_log(FND_LOG.LEVEL_STATEMENT,
3807 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.dea_match',
3808 'Number of records inserted for qualifier combination ' || jtf_csr.attr_relation_product || ' : ' || SQL%ROWCOUNT);
3809
3810 END LOOP;
3811 END IF;
3812 ELSIF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
3813
3814 FOR jtf_csr IN c_get_qualrel_prod(p_source_id, p_trans_id) LOOP
3815 BEGIN
3816 SELECT decode(p_mode, 'TOTAL', batch_match_sql, 'INCREMENTAL', batch_nm_match_sql)
3817 INTO l_match_sql
3818 FROM jty_tae_attr_products_sql
3819 WHERE source_id = p_source_id
3820 AND trans_type_id = p_trans_id
3821 AND program_name = p_program_name
3822 AND attr_relation_product = jtf_csr.relation_product;
3823 EXCEPTION
3824 WHEN NO_DATA_FOUND THEN
3825 x_msg_data := 'No matching SQL found corresponding to source : ' || p_source_id || ' trans : ' || p_trans_id ||
3826 ' Program name : ' || p_program_name || ' relation product : ' || jtf_csr.relation_product;
3827 RAISE;
3828 END;
3829
3830 EXECUTE IMMEDIATE l_match_sql USING l_sysdate, g_user_id, l_sysdate, g_user_id, g_user_id, g_request_id,
3831 g_appl_id, g_program_id, l_sysdate, p_worker_id;
3832 -- debug message
3833 jty_log(FND_LOG.LEVEL_STATEMENT,
3834 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.match',
3835 'Number of records inserted for qualifier combination ' || jtf_csr.relation_product || ' : ' || SQL%ROWCOUNT);
3836 END LOOP;
3837
3838 END IF;
3839 END IF; -- End of addition for p_oic_mode check
3840
3841 IF p_oic_mode = 'MATCH/POST' or p_oic_mode ='NOOIC'
3842 THEN
3843 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3844 p_table_name => l_match_target
3845 , p_percent => p_percent_analyzed
3846 , x_return_status => x_return_status );
3847
3848 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3849 jty_log(FND_LOG.LEVEL_EXCEPTION,
3850 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.analyze_match',
3851 'JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for table : ' || l_match_target);
3852
3853 RAISE FND_API.G_EXC_ERROR;
3854 RETCODE := 2;
3855 x_msg_count := 1;
3856 x_msg_data := 'JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for table : ' || l_match_target;
3857 errbuf := x_msg_data;
3858 END IF;
3859 END IF;
3860 -- debug message
3861 jty_log(FND_LOG.LEVEL_PROCEDURE,
3862 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.end',
3863 'End of the procedure JTY_ASSIGN_BULK_PUB.process_match ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
3864
3865 EXCEPTION
3866 WHEN NO_DATA_FOUND THEN
3867 x_return_status := FND_API.G_RET_STS_ERROR ;
3868 RETCODE := 2;
3869 x_msg_count := 1;
3870 ERRBUF := x_msg_data;
3871 jty_log(FND_LOG.LEVEL_EXCEPTION,
3872 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.no_data_found',
3873 x_msg_data);
3874
3875 WHEN FND_API.G_EXC_ERROR THEN
3876 jty_log(FND_LOG.LEVEL_EXCEPTION,
3877 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.g_exc_error',
3878 x_msg_data);
3879
3880 WHEN OTHERS THEN
3881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3882 x_msg_data := SQLCODE || ' : ' || SQLERRM;
3883 x_msg_count := 1;
3884 RETCODE := 2;
3885 ERRBUF := x_msg_data;
3886 jty_log(FND_LOG.LEVEL_EXCEPTION,
3887 'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_match.other',
3888 substr(x_msg_data, 1, 4000));
3889
3890 END process_match;
3891
3892 -- ***************************************************
3893 -- API Specifications
3894 -- ***************************************************
3895 -- api name : get_winners
3896 -- type : public.
3897 -- function :
3898 -- pre-reqs :
3899 -- notes: API designed to get the winning territories for the
3900 -- transaction objs, it supports multiple worker architecture
3901 --
3902 PROCEDURE get_winners
3903 ( p_api_version_number IN NUMBER,
3904 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3905 p_source_id IN NUMBER,
3906 p_trans_id IN NUMBER,
3907 p_program_name IN VARCHAR2,
3908 p_mode IN VARCHAR2,
3909 p_percent_analyzed IN NUMBER,
3910 p_worker_id IN NUMBER,
3911 x_return_status OUT NOCOPY VARCHAR2,
3912 x_msg_count OUT NOCOPY NUMBER,
3913 x_msg_data OUT NOCOPY VARCHAR2,
3914 ERRBUF OUT NOCOPY VARCHAR2,
3915 RETCODE OUT NOCOPY VARCHAR2,
3916 p_oic_mode IN VARCHAR2 DEFAULT 'NOOIC',
3917 p_terr_id IN NUMBER DEFAULT NULL
3918 ) AS
3919
3920 l_api_name CONSTANT VARCHAR2(30) := 'get_winners';
3921 l_api_version_number CONSTANT NUMBER := 1.0;
3922
3923 BEGIN
3924
3925 -- debug message
3926 jty_log(FND_LOG.LEVEL_PROCEDURE,
3927 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.start',
3928 'Start of the procedure JTY_ASSIGN_BULK_PUB.get_winners ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
3929
3930 /* Standard call to check for call compatibility. */
3931 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3932 p_api_version_number,
3933 l_api_name,
3934 G_PKG_NAME) THEN
3935
3936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3937 END IF;
3938
3939 /* Initialize message list if p_init_msg_list is set to TRUE. */
3940 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3941 FND_MSG_PUB.initialize;
3942 END IF;
3943
3944 x_return_status := FND_API.G_RET_STS_SUCCESS;
3945
3946 -- debug message
3947 jty_log(FND_LOG.LEVEL_STATEMENT,
3948 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.param_value',
3949 'Source : ' || p_source_id || ' Trans : ' || p_trans_id || ' Program Name : ' || p_program_name ||
3950 ' Mode : ' || p_mode || ' Worker : ' || p_worker_id);
3951
3952 IF p_oic_mode = 'NOOIC' OR p_oic_mode = 'MATCH/POPULATE' OR p_oic_mode = 'MATCH/POST'
3953 THEN
3954 /* Find out the matching territories corresponding to the txn objects */
3955 process_match (
3956 p_source_id => p_source_id,
3957 p_trans_id => p_trans_id,
3958 p_program_name => p_program_name,
3959 p_mode => p_mode,
3960 p_percent_analyzed => p_percent_analyzed,
3961 p_worker_id => p_worker_id,
3962 x_return_status => x_return_status,
3963 x_msg_count => x_msg_count,
3964 x_msg_data => x_msg_data,
3965 errbuf => errbuf,
3966 retcode => retcode,
3967 p_oic_mode => p_oic_mode,
3968 p_terr_id => p_terr_id);
3969
3970 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3971 -- debug message
3972 jty_log(FND_LOG.LEVEL_EXCEPTION,
3973 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.match',
3974 'process_match API has failed for source : ' || p_source_id || ' trans : ' || p_trans_id ||
3975 ' program name : ' || p_program_name);
3976
3977 RAISE FND_API.G_EXC_ERROR;
3978 END IF;
3979 -- debug message
3980 jty_log(FND_LOG.LEVEL_EVENT,
3981 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.end_match',
3982 'Matching process completed successfully');
3983 END IF;
3984
3985 IF p_oic_mode = 'NOOIC' OR p_oic_mode = 'WINNER/POPULATE' OR p_oic_mode = 'WINNER/POST'
3986 THEN
3987 /* Find out the winning territories corresponding to the txn objects */
3988 process_winners (
3989 p_source_id => p_source_id,
3990 p_trans_id => p_trans_id,
3991 p_program_name => p_program_name,
3992 p_mode => p_mode,
3993 p_percent_analyzed => p_percent_analyzed,
3994 p_worker_id => p_worker_id,
3995 x_return_status => x_return_status,
3996 x_msg_count => x_msg_count,
3997 x_msg_data => x_msg_data,
3998 errbuf => errbuf,
3999 retcode => retcode,
4000 p_oic_mode => p_oic_mode);
4001
4002 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4003 -- debug message
4004 jty_log(FND_LOG.LEVEL_EXCEPTION,
4005 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.winner',
4006 'process_winners API has failed for source : ' || p_source_id || ' trans : ' || p_trans_id ||
4007 ' program name : ' || p_program_name);
4008
4009 RAISE FND_API.G_EXC_ERROR;
4010 END IF;
4011
4012 -- debug message
4013 jty_log(FND_LOG.LEVEL_EVENT,
4014 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.end_winner',
4015 'Winner process completed successfully');
4016 END IF;
4017 -- debug message
4018 jty_log(FND_LOG.LEVEL_PROCEDURE,
4019 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.end',
4020 'End of the procedure JTY_ASSIGN_BULK_PUB.get_winners ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
4021
4022 EXCEPTION
4023 WHEN FND_API.G_EXC_ERROR THEN
4024 x_return_status := FND_API.G_RET_STS_ERROR ;
4025 RETCODE := 2;
4026 x_msg_count := 1;
4027 jty_log(FND_LOG.LEVEL_EXCEPTION,
4028 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.g_exc_error',
4029 x_msg_data);
4030
4031 WHEN OTHERS THEN
4032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4033 x_msg_data := SQLCODE || ' : ' || SQLERRM;
4034 x_msg_count := 1;
4035 RETCODE := 2;
4036 ERRBUF := x_msg_data;
4037 jty_log(FND_LOG.LEVEL_EXCEPTION,
4038 'jtf.plsql.JTY_ASSIGN_BULK_PUB.get_winners.other',
4042
4039 substr(x_msg_data, 1, 4000));
4040
4041 END get_winners;
4043 END JTY_ASSIGN_BULK_PUB;