[Home] [Help]
PACKAGE BODY: APPS.JTF_TAE_ASSIGN_PUB
Source
1 Package Body JTF_TAE_ASSIGN_PUB AS
2 /* $Header: jtftaeab.pls 120.0 2005/06/02 18:21:08 appldev ship $ */
3 ---------------------------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TAE_ASSIGN_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force applications territory manager public api's.
10 -- This package is a public API for getting winning territories
11 -- or territory resources.
12 --
13 -- Procedures:
14 -- (see below for specification)
15 --
16 -- NOTES
17 -- This package is publicly available for use
18 --
19 -- Valid values for USE_TYPE:
20 -- TERRITORY - return only the distinct winning territories
21 -- RESOURCE - return resources of all winning territories
22 -- LOOKUP - return resource information as needed in territory Lookup
23 --
24 -- Program Flow:
25 -- Check usage to call proper API.
26 -- set output to lx_win_rec
27 -- Process lx_win_rec for output depending on USE_TYPE
28 --
29 --
30 -- Terminology: ---------------------------------------------------------
31 --
32 -- Variable Names
33 -- use_type variable Name
34 -- -----------------------------------
35 -- RESOURCE <not needed - simply copy dyn ouput to API output>
36 -- TERRITORY lx_terr_win_rec
37 -- LOOKUP lx_lookup_bulk_winners_rec
38 --
39 --
40 -- HISTORY
41 -- 03/22/2002 EIHSU CREATED
42 -- 03/22/2002 EIHSU Number of Winners processing
43 -- 04/03/2002 EIHSU Striped by transaction type
44 -- 04/08/2002 EIHSU Additions after code review
45 -- 04/11/2002 EIHSU Delete index from mathes table in delete procedure
46 -- 04/11/2002 EIHSU Fix access type resource name issue.
47 -- 07/22/2003 EIHSU Multi variable num winners processing fix: add LX restriction
48 -- 08/14/2003 EIHSU Putting in Parallel processing for 11.5.10
49 -- This was previously added on 6/12/2003 in ver 115.42
50 -- 08/25/2003 EIHSU Verified Multi-variable num winners proc in parallel
51 -- 10/11/2004 ACHANDA Bug 3920951 fix
52 -- 11/09/2004 ACHANDA Bug 3993227 fix
53 -- 12/08/2004 ACHANDA Bug 4048033 fix : added full table hint while inserting into %L1 tables
54 -- 04/12/2005 ACHANDA Bug 4307593 fix : remove the update to worker_id for new mode TAP
55 -- 04/26/2005 ACHANDA Bug 4329939 fix : remove the distinct clause in the sql statement which inserts data
56 -- into winners table and also reorder the tables in the level processing
57 --
58
59 -- End of Comments
60
61 -- ***************************************************
62 -- GLOBAL VARIABLES and RECORD TYPE DEFINITIONS
63 -- ***************************************************
64
65 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TAE_ASSIGN_PUB';
66 G_FILE_NAME CONSTANT VARCHAR2(12):='jtftaeab.pls';
67
68 G_NEW_LINE VARCHAR2(02) := fnd_global.local_chr(10);
69 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
70 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
71 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
72 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
73 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
74 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
75
76 NO_TAE_DATA_FOUND EXCEPTION;
77
78 /******************* FYI: FND_API STUFF ********************************
79
80 -- API return status
81 --
82 -- G_RET_STS_SUCCESS means that the API was successful in performing
83 -- all the operation requested by its caller.
84 --
85 -- G_RET_STS_ERROR means that the API failed to perform one or more
86 -- of the operations requested by its caller.
87 --
88 -- G_RET_STS_UNEXP_ERROR means that the API was not able to perform
89 -- any of the operations requested by its callers because of an
90 -- unexpected error.
91 --
92 --G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
93 --G_RET_STS_ERROR CONSTANT VARCHAR2(1) := 'E';
94 --G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := 'U';
95
96 -- API error exceptions.
97 -- G_EXC_ERROR : Is used within API bodies to indicate an error,
98 -- this exception should always be handled within the
99 -- API body and p_return_status shoul;d be set to
100 -- error. An API should never return this exception.
101 -- G_EXC_UNEXPECTED_ERROR :
102 -- Is raised by APIs when encountering an unexpected
103 -- error.
104 --
105 --G_EXC_ERROR EXCEPTION;
106 --G_EXC_UNEXPECTED_ERROR EXCEPTION;
107
108 ***************************************************/
109
110 -- ***************************************************
111 -- API Body Definitions
112 -- ***************************************************
113
114 PROCEDURE GET_NUM_ROWS( p_table_name IN VARCHAR2
115 , x_num_rows OUT NOCOPY NUMBER
116 , x_return_status OUT NOCOPY VARCHAR2 ) IS
117
118 retcode VARCHAR2(100);
119 errbuf varchar2(3000);
120 v_statement varchar2(800);
121
122 l_status VARCHAR2(30);
123 l_industry VARCHAR2(30);
124 l_jtf_schema VARCHAR2(30);
125
126 L_SCHEMA_NOTFOUND EXCEPTION;
127 BEGIN
128 x_num_rows := 0;
129
130 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
131 NULL;
132 END IF;
133
134 IF (l_jtf_schema IS NULL) THEN
135 RAISE L_SCHEMA_NOTFOUND;
136 END IF;
137
138 SELECT num_rows
139 INTO x_num_rows
140 FROM all_tables
141 WHERE owner = l_jtf_schema
142 AND table_name = p_table_name;
143
144 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
145 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_NUM_ROWS: Number of rows for the table ' ||
146 p_table_name || ' : ' || x_num_rows);
147 End If;
148
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150
151 EXCEPTION
152 WHEN L_SCHEMA_NOTFOUND THEN
153
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
155 RETCODE := 2;
156 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_NUM_ROWS: [END] SCHEMA NAME FOUND CORRESPONDING TO JTF APPLICATION. ';
157
158 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
159 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
160 End If;
161
162 WHEN OTHERS THEN
163
164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
165 RETCODE := 2;
166 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_NUM_ROWS: [END] OTHERS: ' ||
167 p_table_name || ': ' || SQLERRM;
168
169 If JTF_TAE_CONTROL_PVT.G_DEBUG Then
170 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
171 End If;
172
173
174 END GET_NUM_ROWS;
175
176 PROCEDURE get_winners
177 ( p_api_version_number IN NUMBER,
178 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
179 p_SQL_Trace IN VARCHAR2,
180 p_Debug_Flag IN VARCHAR2,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 x_msg_data OUT NOCOPY VARCHAR2,
184 p_request_id IN NUMBER,
185 p_source_id IN NUMBER,
186 p_trans_object_type_id IN NUMBER,
187 p_target_type IN VARCHAR2 := 'TAP',
188 ERRBUF OUT NOCOPY VARCHAR2,
189 RETCODE OUT NOCOPY VARCHAR2
190 )
191 AS
192
193 l_api_name CONSTANT VARCHAR2(30) := 'Get_Winners';
194 l_api_version_number CONSTANT NUMBER := 1.0;
195 l_return_status VARCHAR2(1);
196 l_count1 NUMBER := 0;
197 l_count2 NUMBER := 0;
198 l_RscCounter NUMBER := 0;
199 l_NumberOfWinners NUMBER ;
200
201 lp_sysdate DATE := SYSDATE;
202 l_rsc_counter NUMBER := 0;
203 l_dyn_str VARCHAR2(32767);
204 num_of_terr NUMBER;
205 num_of_trans NUMBER;
206 d_statement VARCHAR2(2000);
207
208 l_trans_target VARCHAR2(30);
209 l_matches_target VARCHAR2(30);
210 l_winners_target VARCHAR2(30);
211 l_terr_L1_target VARCHAR2(30);
212 l_terr_L2_target VARCHAR2(30);
213 l_terr_L3_target VARCHAR2(30);
214 l_terr_L4_target VARCHAR2(30);
215 l_terr_L5_target VARCHAR2(30);
216 l_terr_WT_target VARCHAR2(30);
217
218 l_matches_idx VARCHAR2(30);
219
220 l_access_list VARCHAR2(300);
221 l_truncate_matches_rtn_val NUMBER;
222 l_create_idx_match_rtn_val NUMBER;
223
224 lX_Msg_Data VARCHAR2(2000);
225 lx_runtime VARCHAR2(300);
226
227 l_worker_id NUMBER := 1;
228 BEGIN
229
230 x_return_status := FND_API.G_RET_STS_SUCCESS;
231
232 /* If the debug flag is set, Then turn on the debug message logging */
233 If UPPER( rtrim(p_Debug_Flag) ) = 'Y' Then
234 JTF_TAE_CONTROL_PVT.G_DEBUG := TRUE;
235 End If;
236
237 /* ARPATEL: 12/15/2003: Bug#3305019 */
238 --If UPPER(p_SQL_Trace) = 'Y' Then
239 -- dbms_session.set_sql_trace(TRUE);
240 --Else
241 -- dbms_session.set_sql_trace(FALSE);
242 --End If;
243
244 -- 05/16/02: JDOCHERT
245 -- Need COMMIT to avoid the following error:
246 -- ORA-12841: Cannot alter the session parallel DML state within a transaction
247 -- in the call to JTF_TAE_CONTROL_PVT.set_session_parameters below
248 ---
249 COMMIT;
250
251 /* Standard call to check for call compatibility. */
252 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
253 p_api_version_number,
254 l_api_name,
255 G_PKG_NAME) THEN
256
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258
259 END IF;
260
261 /* Initialize message list if p_init_msg_list is set to TRUE. */
262 IF FND_API.to_Boolean( p_init_msg_list ) THEN
263 FND_MSG_PUB.initialize;
264 END IF;
265
266 -- Debug Message
267 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
268 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MEM_TASK_START');
269 FND_MSG_PUB.Add;
270 END IF;
271
272 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
273 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, '/***************** BEGIN: TERRITORY ASSIGNMENT STATUS *********************/');
274 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
275 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS start....');
276 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'source_id = ' || TO_CHAR(p_source_Id) );
277 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'trans_object_type_id = ' || TO_CHAR(p_trans_object_type_id) );
278
279 /* JDOCHERT 05/05/02: Commented out settings in JTF_TAE_CONTROL
280 ** since OSO TAP program has these paramaters
281 */
282 --JTF_TAE_CONTROL_PVT.set_session_parameters
283 -- ( p_sort_area_size => 100000000
284 -- , p_hash_area_size => 200000000);
285
286 -----------------------------------------------------------
287 -- logic control to see which dyn package should be called
288 -----------------------------------------------------------
289 IF ( p_target_type = 'TAP' ) THEN
290
291 IF ( p_source_id = -1001 ) THEN
292
293 ------dbms_output.put_line(' p_source_id = -1001 ');
294
295 IF (p_trans_object_type_id = -1002) THEN
296
297 l_trans_target := 'JTF_TAE_1001_ACCOUNT_TRANS';
298 l_matches_target := 'JTF_TAE_1001_ACCOUNT_MATCHES';
299 l_winners_target := 'JTF_TAE_1001_ACCOUNT_WINNERS';
300
301 l_terr_L1_target := 'JTF_TAE_1001_ACCOUNT_L1';
302 l_terr_L2_target := 'JTF_TAE_1001_ACCOUNT_L2';
303 l_terr_L3_target := 'JTF_TAE_1001_ACCOUNT_L3';
304 l_terr_L4_target := 'JTF_TAE_1001_ACCOUNT_L4';
305 l_terr_L5_target := 'JTF_TAE_1001_ACCOUNT_L5';
306 l_terr_WT_target := 'JTF_TAE_1001_ACCOUNT_WT';
307
308 l_access_list := ' ''ACCOUNT'' ';
309
310
311 ELSIF (p_trans_object_type_id = -1003) THEN
312
313 l_trans_target := 'JTF_TAE_1001_LEAD_TRANS';
314 l_matches_target := 'JTF_TAE_1001_LEAD_MATCHES';
315 l_winners_target := 'JTF_TAE_1001_LEAD_WINNERS';
316
317 l_terr_L1_target := 'JTF_TAE_1001_LEAD_L1';
318 l_terr_L2_target := 'JTF_TAE_1001_LEAD_L2';
319 l_terr_L3_target := 'JTF_TAE_1001_LEAD_L3';
320 l_terr_L4_target := 'JTF_TAE_1001_LEAD_L4';
321 l_terr_L5_target := 'JTF_TAE_1001_LEAD_L5';
322 l_terr_WT_target := 'JTF_TAE_1001_LEAD_WT';
323
324 l_access_list := ' ''LEAD'' ';
325
326 ELSIF (p_trans_object_type_id = -1004) THEN
327
328 l_trans_target := 'JTF_TAE_1001_OPPOR_TRANS';
329 l_matches_target := 'JTF_TAE_1001_OPPOR_MATCHES';
330 l_winners_target := 'JTF_TAE_1001_OPPOR_WINNERS';
331
332 l_terr_L1_target := 'JTF_TAE_1001_OPPOR_L1';
333 l_terr_L2_target := 'JTF_TAE_1001_OPPOR_L2';
334 l_terr_L3_target := 'JTF_TAE_1001_OPPOR_L3';
335 l_terr_L4_target := 'JTF_TAE_1001_OPPOR_L4';
336 l_terr_L5_target := 'JTF_TAE_1001_OPPOR_L5';
337 l_terr_WT_target := 'JTF_TAE_1001_OPPOR_WT';
338
339 l_access_list := ' ''OPPOR'' ';
340
341 END IF; -- what tx type
342
343 END IF; -- what usage
344
345 /* set NOLOGGING on JTF_TAE_..._MATCHES and JTF_TAE_..._WINNERS tables */
346 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_trans_target);
347
348 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_matches_target);
349
350 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L1_target);
351 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L2_target);
352 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L3_target);
353 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L4_target);
354 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L5_target);
355 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_WT_target);
356
357 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_winners_target);
358
359 -----------
360 --- [2] ---
361 -----------
362 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
363 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [1] Call to ' ||
364 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API BEGINS ' ||
365 'for ' || l_matches_target);
366
367 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
368 p_table_name => l_matches_target
369 , x_return_status => x_return_status );
370
371 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
372
373 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [1] Call to ' ||
374 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
375 l_matches_target;
376
377 RAISE FND_API.G_EXC_ERROR;
378
379 END IF;
380
381 -----------
382 --- [3] ---
383 -----------
384 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
385 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [2] Call to ' ||
386 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API BEGINS ' ||
387 'for ' || l_winners_target);
388
389 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
390 p_table_name => l_winners_target
391 , x_return_status => x_return_status );
392
393 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
394
395 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [2] Call to ' ||
396 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
397 l_winners_target;
398
399 RAISE FND_API.G_EXC_ERROR;
400
401 END IF;
402
403 -----------
404 --- [3] ---
405 -----------
406 BEGIN
407
408 -- Check for territories for this Usage/Transaction Type
409 SELECT COUNT(*)
410 INTO num_of_terr
411 FROM jtf_terr_qtype_usgs_all jtqu
412 , jtf_terr_usgs_all jtu
413 , jtf_terr_all jt1
414 , jtf_qual_type_usgs jqtu
415 WHERE jtqu.terr_id = jt1.terr_id
416 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
417 AND jqtu.qual_type_id = p_trans_object_type_id
418 AND jtu.source_id = p_source_id
419 AND jtu.terr_id = jt1.terr_id
420 AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
421 AND jt1.start_date_active <= lp_sysdate
422 AND EXISTS (
423 SELECT jtrs.terr_rsc_id
424 FROM jtf_terr_rsc_all jtrs
425 WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
426 AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
427 AND jtrs.terr_id = jt1.terr_id )
428 AND NOT EXISTS (
429 SELECT jt.terr_id
430 FROM jtf_terr_all jt
431 WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
432 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
433 START WITH jt.terr_id = jt1.terr_id)
434 AND jqtu.qual_type_id <> -1001;
435
436 EXCEPTION
437 WHEN NO_DATA_FOUND THEN
438 num_of_terr := 0;
439 END;
440
441 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
442 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [3] Number of valid ' ||
443 'Territories with Resources for this Transaction: ' ||
444 num_of_terr );
445
446 -- 2357180: ERROR HANDLING FOR NO TERRITORY DATA:
447 -- AFTER MATCHES AND WINNERS TABLES HAVE BEEN TRUNCATED
448 IF (num_of_terr = 0) THEN
449
450 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [3] There are NO Active Territories with Active ' ||
451 'Resources existing for this Usage/Transaction combination, so no assignments ' ||
452 'can take place.';
453
454 RAISE NO_TAE_DATA_FOUND;
455
456 END IF;
457
458 -----------
459 --- [4] ---
460 ----------
461 BEGIN
462
463 d_statement := ' SELECT COUNT(*) FROM ' ||
464 l_trans_target ||
465 ' WHERE rownum < 2 ';
466 EXECUTE IMMEDIATE d_statement INTO num_of_trans;
467
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 num_of_trans := 0;
471 END;
472
473 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
474 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [4] ' ||
475 'There are valid Transaction Objects to be assigned.');
476
477 IF (num_of_trans = 0) THEN
478 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [4] There are NO valid ' ||
479 'Transaction Objects to assign.';
480 RAISE NO_TAE_DATA_FOUND;
481 END IF;
482
483 -------------
484 --- [4.1] --- BACKWARDS COMPATIBILITY FOR PARALLEL PROCESSING ARCHITECTURE
485 -------------
486 /* If you called this procedure then you are not using parallel processing.
487 Default worker number needs to be set.
488
489 06/12/2003
490 NOTE: UNDER NO CIRCUMSTANCES should this step behind phase 6, when Trans table
491 indexes actually created.
492
493 */
494
495 BEGIN
496
497 d_statement := ' UPDATE ' || l_trans_target || ' SET worker_id = 1';
498 EXECUTE IMMEDIATE d_statement;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
503 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [4.1] ' ||
504 'Exception while setting worker_id to 1');
505
506 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [4.1] Exception while setting worker_id to 1.';
507 RAISE NO_TAE_DATA_FOUND;
508 END;
509
510 -----------
511 --- [5] ---
512 -----------
513 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
514 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [5] Call to ' ||
515 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API BEGINS ' ||
516 'for ' || l_trans_target);
517
518 -- Analyze _TRANS table
519 -- JDOCHERT: 04/10/03: bug#2896552
520 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
521 p_table_name => l_trans_target
522 , p_percent => 99
523 , x_return_status => x_return_status );
524
525 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
526 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [5] Call to ' ||
527 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' ||
528 l_trans_target;
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531
532
533 -----------
534 --- [6] ---
535 -----------
536 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
537 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [6] Call to ' ||
538 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS API BEGINS...');
539 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
540
541 JTF_TAE_CONTROL_PVT.Decompose_Terr_Defns
542 (p_Api_Version_Number => 1.0,
543 p_Init_Msg_List => FND_API.G_FALSE,
544 p_Commit => FND_API.G_FALSE,
545 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
546 x_Return_Status => x_return_status,
547 x_Msg_Count => x_msg_count,
548 x_Msg_Data => x_msg_data,
549 p_run_mode => 'FULL',
550 p_classify_terr_comb => 'N',
551 p_process_tx_oin_sel => 'Y',
552 p_generate_indexes => 'Y',
553 p_source_id => p_source_id,
554 p_trans_id => p_trans_object_type_id,
555 errbuf => ERRBUF,
556 retcode => RETCODE );
557
558 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
559 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [6] Call to ' ||
560 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS API has failed.';
561 RAISE FND_API.G_EXC_ERROR;
562 END IF;
563
564 -----------
565 --- [7] ---
566 -----------
567 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
568 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [7] Call to ' ||
569 'JTF_TAE_1001_..._DYN.SEARCH_TERR_RULES API BEGINS... ');
570
571 -- Start MATCH processing
572 IF (p_trans_object_type_id = -1002) THEN
573
574 JTF_TAE_1001_ACCOUNT_DYN.Search_Terr_Rules(
575 p_source_id => p_source_id ,
576 p_trans_object_type_id => p_trans_object_type_id,
577 x_Return_Status => x_return_status,
578 x_Msg_Count => x_msg_count,
579 x_Msg_Data => x_msg_data );
580
581 ELSIF (p_trans_object_type_id = -1003) THEN
582
583 JTF_TAE_1001_LEAD_DYN.Search_Terr_Rules(
584 p_source_id => p_source_id ,
585 p_trans_object_type_id => p_trans_object_type_id,
586 x_Return_Status => x_return_status,
587 x_Msg_Count => x_msg_count,
588 x_Msg_Data => x_msg_data );
589
590 ELSIF (p_trans_object_type_id = -1004) THEN
591
592 JTF_TAE_1001_OPPOR_DYN.Search_Terr_Rules(
593 p_source_id => p_source_id ,
594 p_trans_object_type_id => p_trans_object_type_id,
595 x_Return_Status => x_return_status,
596 x_Msg_Count => x_msg_count,
597 x_Msg_Data => x_msg_data );
598
599 END IF; -- what tx type
600
601 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
602 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [7] Call to ' ||
603 'JTF_TAE_1001_..._DYN.SEARCH_TERR_RULES API has failed.';
604 RAISE FND_API.G_EXC_ERROR;
605 END IF;
606
607 -----------
608 --- [8] ---
609 -----------
610 -- 05/16/02: JDOCHERT:
611 -- With new NOWP processing using RANK() function,
612 -- we no longer need INDEX on _MATCH table.
613
614 -----------
615 --- [9] ---
616 -----------
617 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
618 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [9] Call to ' ||
619 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API BEGINS ' ||
620 'for ' || l_matches_target);
621
622 -- Analyze Matches table
623 -- JDOCHERT: 04/10/03: bug#2896552
624 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
625 p_table_name => l_matches_target
626 , p_percent => 20
627 , x_return_status => x_return_status );
628
629 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
630 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [9] Call to ' ||
631 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' ||
632 l_matches_target;
633 RAISE FND_API.G_EXC_ERROR;
634 END IF;
635
636 -----------
637 --- [10] ---
638 -----------
639 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
640 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [10] Call to ' ||
641 'MULTI-LEVEL TABLE CLEANUP BEGINS...');
642
643 ----dbms_output.put_line('10.1: Truncate Level Winners Tables ');
644 -- 10.1: Truncate Level Winners Tables
645 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
646 p_table_name => l_terr_L1_target
647 , x_return_status => x_return_status );
648
649 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
650 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [10.1] Call to ' ||
651 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
652 l_terr_L1_target;
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655
656 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
657 p_table_name => l_terr_L2_target
658 , x_return_status => x_return_status );
659
660 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
661 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [10.2] Call to ' ||
662 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
663 l_terr_L2_target;
664 RAISE FND_API.G_EXC_ERROR;
665 END IF;
666
667 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
668 p_table_name => l_terr_L3_target
669 , x_return_status => x_return_status );
670
671 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
672 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [10.3] Call to ' ||
673 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
674 l_terr_L3_target;
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677
678 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
679 p_table_name => l_terr_L4_target
680 , x_return_status => x_return_status );
681
682 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
683 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [10.4] Call to ' ||
684 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
685 l_terr_L4_target;
686 RAISE FND_API.G_EXC_ERROR;
687 END IF;
688
689 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
690 p_table_name => l_terr_L5_target
691 , x_return_status => x_return_status );
692
693 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
694 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [10.5] Call to ' ||
695 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
696 l_terr_L5_target;
697 RAISE FND_API.G_EXC_ERROR;
698 END IF;
699
700 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
701 p_table_name => l_terr_WT_target
702 , x_return_status => x_return_status );
703
704 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
705 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [10.6] Call to ' ||
706 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
707 l_terr_WT_target;
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710
711
712 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
713 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [11] Call to ' ||
714 'MULTI-LEVEL NUMBER OF WINNERS PROCESSING BEGINS...');
715
716 --ARPATEL 09/22/03 add commit to avoid large rollback segments
717 COMMIT;
718
719 --dbms_output.put_line('10.2: Process Level Winners ');
720 -- 11: Process Level Winners
721 -- NOTE: p_terr_PARENT_LEVEL_tbl arg ingnored here as we process top level
722 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L1_target,
723 p_terr_PARENT_LEVEL_tbl => l_terr_L1_target,
724 p_UPPER_LEVEL_FROM_ROOT => 1,
725 p_LOWER_LEVEL_FROM_ROOT => 1,
726 p_matches_target => l_matches_target,
727 p_source_id => p_source_id,
728 p_qual_type_id => p_trans_object_type_id,
729 x_return_status => l_return_status,
730 p_worker_id => 1
731 );
732
733 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
734 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.1] Call to ' ||
735 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
736 l_terr_L1_target;
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739
740 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
741 p_table_name => l_terr_L1_target
742 , p_percent => 20
743 , x_return_status => x_return_status );
744
745 --ARPATEL 09/22/03 add commit to avoid large rollback segments
746 COMMIT;
747
748 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L2_target,
749 p_terr_PARENT_LEVEL_tbl => l_terr_L1_target,
750 p_UPPER_LEVEL_FROM_ROOT => 1,
751 p_LOWER_LEVEL_FROM_ROOT => 2,
752 p_matches_target => l_matches_target,
753 p_source_id => p_source_id,
754 p_qual_type_id => p_trans_object_type_id,
755 x_return_status => l_return_status,
756 p_worker_id => 1
757 );
758
759 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
760 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.2] Call to ' ||
761 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
762 l_terr_L2_target;
763 RAISE FND_API.G_EXC_ERROR;
764 END IF;
765
766 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
767 p_table_name => l_terr_L2_target
768 , p_percent => 20
769 , x_return_status => x_return_status );
770
771 --ARPATEL 09/22/03 add commit to avoid large rollback segments
772 COMMIT;
773 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L3_target,
774 p_terr_PARENT_LEVEL_tbl => l_terr_L2_target,
775 p_UPPER_LEVEL_FROM_ROOT => 2,
776 p_LOWER_LEVEL_FROM_ROOT => 3,
777 p_matches_target => l_matches_target,
778 p_source_id => p_source_id,
779 p_qual_type_id => p_trans_object_type_id,
780 x_return_status => l_return_status,
781 p_worker_id => 1
782 );
783
784 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
785 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.3] Call to ' ||
786 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
787 l_terr_L3_target;
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790
791 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
792 p_table_name => l_terr_L3_target
793 , p_percent => 20
794 , x_return_status => x_return_status );
795
796 --ARPATEL 09/22/03 add commit to avoid large rollback segments
797 COMMIT;
798 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L4_target,
799 p_terr_PARENT_LEVEL_tbl => l_terr_L3_target,
800 p_UPPER_LEVEL_FROM_ROOT => 3,
801 p_LOWER_LEVEL_FROM_ROOT => 4,
802 p_matches_target => l_matches_target,
803 p_source_id => p_source_id,
804 p_qual_type_id => p_trans_object_type_id,
805 x_return_status => l_return_status,
806 p_worker_id => 1
807 );
808
809 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
810 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.4] Call to ' ||
811 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
812 l_terr_L4_target;
813 RAISE FND_API.G_EXC_ERROR;
814 END IF;
815
816 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
817 p_table_name => l_terr_L4_target
818 , p_percent => 20
819 , x_return_status => x_return_status );
820
821 --ARPATEL 09/22/03 add commit to avoid large rollback segments
822 COMMIT;
823 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L5_target,
824 p_terr_PARENT_LEVEL_tbl => l_terr_L4_target,
825 p_UPPER_LEVEL_FROM_ROOT => 4,
826 p_LOWER_LEVEL_FROM_ROOT => 5,
827 p_matches_target => l_matches_target,
828 p_source_id => p_source_id,
829 p_qual_type_id => p_trans_object_type_id,
830 x_return_status => l_return_status,
831 p_worker_id => 1
832 );
833
834 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
835 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.5] Call to ' ||
836 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
837 l_terr_L5_target;
838 RAISE FND_API.G_EXC_ERROR;
839 END IF;
840
841 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
842 p_table_name => l_terr_L5_target
843 , p_percent => 20
844 , x_return_status => x_return_status );
845
846 --ARPATEL 09/22/03 add commit to avoid large rollback segments
847 COMMIT;
848
849 Process_Final_Level_Winners (
850 p_terr_LEVEL_target_tbl => l_terr_WT_target,
851 p_terr_L5_target_tbl => l_terr_L5_target,
852 p_matches_target => l_matches_target,
853 p_source_id => p_source_id,
854 p_qual_type_id => p_trans_object_type_id,
855 x_return_status => l_return_status,
856 p_worker_id => 1
857 );
858
859 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
860 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.6] Call to ' ||
861 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
862 l_terr_WT_target;
863 RAISE FND_API.G_EXC_ERROR;
864 END IF;
865
866 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
867 p_table_name => l_terr_WT_target
868 , p_percent => 20
869 , x_return_status => x_return_status );
870
871 --ARPATEL 09/22/03 add commit to avoid large rollback segments
872 COMMIT;
873
874 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [12] Call to ' ||
875 'WINNING TERRITORY RESOURCE PROCESSING BEGINS...');
876
877 --dbms_output.put_line('12: Fetch Winners and Resources ');
878 -- 12.0: Fetch Winners and Resources
879
880 /* 05/16/02: JDOCHERT:
881 ** No longer create MATCHES INDEX, so INDEX hint is obsolete
882 ** l_matches_idx := REPLACE(UPPER(l_matches_target), 'ES', NULL);
883 */
884
885 l_dyn_str :=
886 ' INSERT INTO ' ||
887 l_winners_target || ' i ' ||
888 ' ( ' ||
889 ' TRANS_OBJECT_ID ' ||
890 ' ,TRANS_DETAIL_OBJECT_ID ' ||
891 ' ,WORKER_ID ' || /* ARPATEL 05/03/2004 Bug#3608474 */
892
893 /*
894 ** 07/17/03 JDOCHERT: NOT USED
895 **' ,HEADER_ID1 ' ||
896 **' ,HEADER_ID2 ' ||
897 */
898
899 ' ,SOURCE_ID ' ||
900 ' ,TRANS_OBJECT_TYPE_ID ' ||
901 ' ,LAST_UPDATE_DATE ' ||
902 ' ,LAST_UPDATED_BY ' ||
903 ' ,CREATION_DATE ' ||
904 ' ,CREATED_BY ' ||
905 ' ,LAST_UPDATE_LOGIN ' ||
906 ' ,REQUEST_ID ' ||
907 ' ,PROGRAM_APPLICATION_ID ' ||
908 ' ,PROGRAM_ID ' ||
909 ' ,PROGRAM_UPDATE_DATE ' ||
910 ' ,TERR_ID ' ||
911 ' ,ABSOLUTE_RANK ' ||
912 ' ,TOP_LEVEL_TERR_ID ' ||
913 ' ,RESOURCE_ID ' ||
914 ' ,RESOURCE_TYPE ' ||
915 ' ,GROUP_ID ' ||
916 ' ,ROLE ' ||
917 ' ,PRIMARY_CONTACT_FLAG ' ||
918 ' ,PERSON_ID ' ||
919 ' ,ORG_ID ' ||
920 ' ,TERR_RSC_ID ' ||
921 ' ,FULL_ACCESS_FLAG ' ||
922 ' ) ' ||
923 ' ( ' ||
924
925 --
926 -- 10/02/02: JDOCHERT: BUG#2594526 and BUG#2602646
927 --
928 --' SELECT /*+ ' ||
929 --' INDEX (jtr JTF_TERR_RSC_N1) ' ||
930 --' INDEX (jtra JTF_TERR_RSC_ACCESS_N1) ' ||
931 --' */ ' ||
932 --
933
934 ' SELECT DISTINCT ' ||
935 ' WINNERS.trans_object_id ' ||
936 ' , WINNERS.trans_detail_object_id ' ||
937 ' , 1 ' || /* ARPATEL 05/03/2004 Bug#3608474 Default value to 1 for non-parallel get_winners */
938
939 /*
940 ** 07/17/03 JDOCHERT: NOT USED
941 **' , 0 header_id1 ' || -- o_dttm.header_id1 ' ||
942 **' , 0 header_id2 ' || -- o_dttm.header_id2 ' ||
943 */
944
945 ' , :BV1_SOURCE_ID ' ||
946 ' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
947 ' , :BV1_LAST_UPDATE_DATE ' ||
948 ' , :BV1_LAST_UPDATED_BY ' ||
949 ' , :BV1_CREATION_DATE ' ||
950 ' , :BV1_CREATED_BY ' ||
951 ' , :BV1_LAST_UPDATE_LOGIN ' ||
952 ' , :BV1_REQUEST_ID ' ||
953 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
954 ' , :BV1_PROGRAM_ID ' ||
955 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
956 ' , WINNERS.WIN_terr_id ' ||
957 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
958 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
959 ' , jtr.resource_id ' ||
960 ' , jtr.resource_type ' ||
961 ' , jtr.group_id ' ||
962 ' , jtr.role ' ||
963 ' , jtr.primary_contact_flag ' ||
964 ' , jtr.PERSON_ID ' ||
965 ' , jtr.org_id ' ||
966 ' , jtr.terr_rsc_id ' ||
967 ' , jtr.full_access_flag ' ||
968 ' FROM ( /* WINNERS ILV */ ' ||
969 ' SELECT LX.trans_object_id ' ||
970 ' , LX.trans_detail_object_id ' ||
971 ' , LX.WIN_TERR_ID ' ||
972 ' FROM ' || l_terr_L1_target || ' LX ' ||
973 ' , ( SELECT trans_object_id ' ||
974 ' , trans_detail_object_id ' ||
975 ' , WIN_TERR_ID WIN_TERR_ID ' ||
976 ' FROM ' || l_terr_L1_target ||
977 ' MINUS ' ||
978 ' SELECT trans_object_id ' ||
979 ' , trans_detail_object_id ' ||
980 ' , ul_terr_id WIN_TERR_ID ' ||
981 ' FROM ' || l_terr_L2_target || ' ) ILV ' ||
982 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
983 ' LX.trans_detail_object_id IS NULL ) ' ||
984 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
985 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
986
987 ' UNION ALL ' ||
988
989 ' SELECT LX.trans_object_id ' ||
990 ' , LX.trans_detail_object_id ' ||
991 ' , LX.WIN_TERR_ID ' ||
992 ' FROM ' || l_terr_L2_target || ' LX ' ||
993 ' , ( SELECT trans_object_id ' ||
994 ' , trans_detail_object_id ' ||
995 ' , WIN_TERR_ID WIN_TERR_ID ' ||
996 ' FROM ' || l_terr_L2_target ||
997 ' MINUS ' ||
998 ' SELECT trans_object_id ' ||
999 ' , trans_detail_object_id ' ||
1000 ' , ul_terr_id WIN_TERR_ID ' ||
1001 ' FROM ' || l_terr_L3_target || ' ) ILV ' ||
1002 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
1003 ' LX.trans_detail_object_id IS NULL ) ' ||
1004 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
1005 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
1006
1007 ' UNION ALL ' ||
1008
1009 ' SELECT LX.trans_object_id ' ||
1010 ' , LX.trans_detail_object_id ' ||
1011 ' , LX.WIN_TERR_ID ' ||
1012 ' FROM ' || l_terr_L3_target || ' LX ' ||
1013 ' , ( SELECT trans_object_id ' ||
1014 ' , trans_detail_object_id ' ||
1015 ' , WIN_TERR_ID WIN_TERR_ID ' ||
1016 ' FROM ' || l_terr_L3_target ||
1017 ' MINUS ' ||
1018 ' SELECT trans_object_id ' ||
1019 ' , trans_detail_object_id ' ||
1020 ' , ul_terr_id WIN_TERR_ID ' ||
1021 ' FROM ' || l_terr_L4_target || ' ) ILV ' ||
1022 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
1023 ' LX.trans_detail_object_id IS NULL ) ' ||
1024 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
1025 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
1026
1027 ' UNION ALL ' ||
1028
1029 ' SELECT LX.trans_object_id ' ||
1030 ' , LX.trans_detail_object_id ' ||
1031 ' , LX.WIN_TERR_ID ' ||
1032 ' FROM ' || l_terr_L4_target || ' LX ' ||
1033 ' , ( SELECT trans_object_id ' ||
1034 ' , trans_detail_object_id ' ||
1035 ' , WIN_TERR_ID WIN_TERR_ID ' ||
1036 ' FROM ' || l_terr_L4_target ||
1037 ' MINUS ' ||
1038 ' SELECT trans_object_id ' ||
1039 ' , trans_detail_object_id ' ||
1040 ' , ul_terr_id WIN_TERR_ID ' ||
1041 ' FROM ' || l_terr_L5_target || ' ) ILV ' ||
1042 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
1043 ' LX.trans_detail_object_id IS NULL ) ' ||
1044 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
1045 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
1046
1047 ' UNION ALL ' ||
1048
1049 ' SELECT LX.trans_object_id ' ||
1050 ' , LX.trans_detail_object_id ' ||
1051 ' , LX.WIN_TERR_ID ' ||
1052 ' FROM ' || l_terr_L5_target || ' LX ' ||
1053 ' , ( SELECT trans_object_id ' ||
1054 ' , trans_detail_object_id ' ||
1055 ' , WIN_TERR_ID WIN_TERR_ID ' ||
1056 ' FROM ' || l_terr_L5_target ||
1057 ' MINUS ' ||
1058 ' SELECT trans_object_id ' ||
1059 ' , trans_detail_object_id ' ||
1060 ' , ul_terr_id WIN_TERR_ID ' ||
1061 ' FROM ' || l_terr_WT_target || ' ) ILV ' ||
1062 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
1063 ' LX.trans_detail_object_id IS NULL ) ' ||
1064 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
1065 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
1066
1067 ' UNION ALL ' ||
1068
1069 ' SELECT trans_object_id ' ||
1070 ' , trans_detail_object_id ' ||
1071 ' , WIN_TERR_ID ' ||
1072 ' FROM ' || l_terr_WT_target ||
1073 ' ) WINNERS ' ||
1074 ' , jtf_terr_rsc_all jtr ' ||
1075 ' , jtf_terr_rsc_access_all jtra ' ||
1076 ' WHERE WINNERS.WIN_terr_id = jtr.terr_id ' ||
1077 ' AND ( ( jtr.end_date_active IS NULL OR jtr.end_date_active >= :BV1_SYSDATE ) AND ' ||
1078 ' ( jtr.start_date_active IS NULL OR jtr.start_date_active <= :BV2_SYSDATE ) ' ||
1079 ' ) ' ||
1080 ' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
1081 ' AND jtra.access_type = ' || l_access_list ||
1082 ' ) ';
1083
1084
1085 BEGIN
1086
1087 --ARPATEL 09/22/03 add commit to avoid large rollback segments
1088 COMMIT;
1089
1090 EXECUTE IMMEDIATE l_dyn_str USING
1091 p_source_id /* :BV1_SOURCE_ID */
1092 , p_trans_object_type_id /* :BV1_TRANS_OBJECT_TYPE_ID */
1093 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
1094 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
1095 , lp_sysdate /* :BV1_CREATION_DATE */
1096 , G_USER_ID /* :BV1_CREATED_BY */
1097 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
1098 , p_request_id /* :BV1_REQUEST_ID */
1099 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
1100 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
1101 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
1102 , lp_sysdate /* :BV1_SYSDATE */
1103 , lp_sysdate; /* :BV2_SYSDATE */
1104
1105
1106 COMMIT; -- after modifying table in parallel
1107
1108 EXCEPTION
1109 WHEN NO_DATA_FOUND THEN
1110 NULL;
1111 END;
1112
1113 x_return_status := FND_API.G_RET_STS_SUCCESS;
1114
1115 --dbms_output.put_line(' phase 10 complete ');
1116
1117 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1118 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Number of records inserted into ' || l_winners_target ||
1119 ' = ' || SQL%ROWCOUNT );
1120 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1121 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [12] Call to ' ||
1122 'NUMBER OF WINNERS PROCESSING COMPLETE.');
1123
1124 -----------
1125 --- [13] ---
1126 -----------
1127 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1128 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [13] Call to ' ||
1129 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
1130 'for ' || l_winners_target);
1131
1132 /* Build Index on Winners table */
1133 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_winners_target
1134 , p_trans_object_type_id
1135 , p_source_id
1136 , l_return_status );
1137
1138 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1139 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [13] Call to ' ||
1140 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
1141 l_winners_target;
1142 RAISE FND_API.G_EXC_ERROR;
1143 END IF;
1144
1145 -----------
1146 --- [14] ---
1147 -----------
1148 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1149 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [14] Call to ' ||
1150 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API BEGINS ' ||
1151 'for ' || l_winners_target);
1152
1153 /* Analyze Winners table */
1154 /* JDOCHERT: 04/10/03: bug#2896552 */
1155 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
1156 p_table_name => l_winners_target
1157 , p_percent => 20
1158 , x_return_status => x_return_status );
1159
1160 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1161 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [14] Call to ' ||
1162 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' ||
1163 l_winners_target;
1164 RAISE FND_API.G_EXC_ERROR;
1165 END IF;
1166
1167 ELSIF p_target_type ='RPT' THEN
1168 NULL;
1169 END IF; -- RPT OR TAP
1170
1171 --------------------------------
1172 -- END API
1173 --------------------------------
1174 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1175 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, '/***************** END: TERRITORY ASSIGNMENT STATUS *********************/');
1176 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1177
1178 /* Program completed successfully */
1179 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: Successfully completed.';
1180 x_return_status := FND_API.G_RET_STS_SUCCESS;
1181 RETCODE := 0;
1182
1183 EXCEPTION
1184
1185 WHEN NO_TAE_DATA_FOUND THEN
1186
1187 x_return_status := FND_API.G_RET_STS_SUCCESS;
1188
1189 RETCODE := 1;
1190 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [END] NO_TAE_DATA_FOUND: ' ||
1191 SQLERRM;
1192 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1193
1194 WHEN FND_API.G_EXC_ERROR THEN
1195
1196 x_return_status := FND_API.G_RET_STS_ERROR;
1197
1198 RETCODE := 2;
1199 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [END] FND_API.G_EXC_ERROR: ' ||
1200 SQLERRM;
1201 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1202
1203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1204
1205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1206
1207 RETCODE := 2;
1208 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: FND_API.G_EXC_UNEXPECTED_ERROR: ' ||
1209 SQLERRM;
1210
1211 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1212
1213 WHEN utl_file.invalid_path OR
1214 utl_file.invalid_mode OR
1215 utl_file.invalid_filehandle OR
1216 utl_file.invalid_operation OR
1217 utl_file.write_error THEN
1218
1219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1220 RETCODE := 2;
1221 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [END] UTL_FILE: ' ||
1222 SQLERRM;
1223
1224 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1225
1226 WHEN OTHERS THEN
1227
1228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1229 RETCODE := 2;
1230 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [END] OTHERS: ' ||
1231 SQLERRM;
1232
1233 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1234
1235 End Get_Winners;
1236
1237 -- ***************************************************
1238 -- API Specifications
1239 -- ***************************************************
1240 -- api name : Drop_TAE_TRANS_Indexes
1241 -- type : public.
1242 -- function : Drop_TX_Input_Indexes
1243 -- pre-reqs :
1244 -- notes:
1245 --
1246 PROCEDURE Drop_TAE_TRANS_Indexes
1247 ( p_api_version_number IN NUMBER,
1248 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1249 p_SQL_Trace IN VARCHAR2,
1250 p_Debug_Flag IN VARCHAR2,
1251 x_return_status OUT NOCOPY VARCHAR2,
1252 p_source_id IN NUMBER,
1253 p_trans_object_type_id IN NUMBER,
1254 x_msg_count OUT NOCOPY NUMBER,
1255 x_msg_data OUT NOCOPY VARCHAR2,
1256 ERRBUF OUT NOCOPY VARCHAR2,
1257 RETCODE OUT NOCOPY VARCHAR2
1258 )
1259 AS
1260 l_trans_target varchar2(30);
1261 l_matches_target varchar2(30);
1262 l_winners_target varchar2(30);
1263 l_source_id number := p_source_id;
1264 l_trans_id number := p_trans_object_type_id;
1265
1266 BEGIN
1267
1268 x_return_status := FND_API.G_RET_STS_SUCCESS;
1269
1270 /* Sales and Telesales/Account */
1271 IF (l_source_id = -1001) THEN
1272
1273 IF (l_trans_id = -1002) THEN
1274 l_trans_target := 'JTF_TAE_1001_ACCOUNT_TRANS';
1275 l_matches_target := 'JTF_TAE_1001_ACCOUNT_MATCHES';
1276 l_winners_target := 'JTF_TAE_1001_ACCOUNT_WINNERS';
1277
1278 ELSIF (l_trans_id = -1003) THEN
1279 l_trans_target := 'JTF_TAE_1001_LEAD_TRANS';
1280 l_matches_target := 'JTF_TAE_1001_LEAD_MATCHES';
1281 l_winners_target := 'JTF_TAE_1001_LEAD_WINNERS';
1282
1283 ELSIF (l_trans_id = -1004) THEN
1284 l_trans_target := 'JTF_TAE_1001_OPPOR_TRANS';
1285 l_matches_target := 'JTF_TAE_1001_OPPOR_MATCHES';
1286 l_winners_target := 'JTF_TAE_1001_OPPOR_WINNERS';
1287
1288 END IF; -- what tx type
1289
1290 END IF; -- what usage
1291
1292
1293 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES( p_table_name => l_trans_target
1294 , x_return_status => x_return_status );
1295
1296 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1297
1298 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: [1] Call to ' ||
1299 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' ||
1300 l_trans_target;
1301
1302 RAISE FND_API.G_EXC_ERROR;
1303
1304 END IF;
1305
1306 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES( p_table_name => l_matches_target
1307 , x_return_status => x_return_status );
1308
1309 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1310
1311
1312 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: [2] Call to ' ||
1313 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' ||
1314 l_matches_target;
1315
1316 RAISE FND_API.G_EXC_ERROR;
1317
1318 END IF;
1319
1320 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES( p_table_name => l_winners_target
1321 , x_return_status => x_return_status );
1322
1323 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1324
1325
1326 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: [3] Call to ' ||
1327 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' ||
1328 l_winners_target;
1329
1330 RAISE FND_API.G_EXC_ERROR;
1331
1332 END IF;
1333
1334 /* Program completed successfully */
1335 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: Successfully completed.';
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337 RETCODE := 0;
1338
1339 EXCEPTION
1340
1341 WHEN FND_API.G_EXC_ERROR THEN
1342 x_return_status := FND_API.G_RET_STS_ERROR;
1343
1344 RETCODE := 2;
1345 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: [END] ' ||
1346 'FND_API.G_EXC_ERROR: ' ||
1347 SQLERRM;
1348 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1349
1350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1351
1352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1353
1354 RETCODE := 2;
1355 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: [END] ' ||
1356 'FND_API.G_EXC_UNEXPECTED_ERROR: ' ||
1357 SQLERRM;
1358 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1359
1360 WHEN utl_file.invalid_path OR
1361 utl_file.invalid_mode OR
1362 utl_file.invalid_filehandle OR
1363 Utl_file.invalid_operation OR
1364 utl_file.write_error THEN
1365
1366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1367 RETCODE := 2;
1368 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: [END] UTL_FILE: ' ||
1369 SQLERRM;
1370
1371 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1372
1373 WHEN OTHERS THEN
1374
1375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1376 RETCODE := 2;
1377 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DROP_TAE_TRANS_INDEXES: [END] OTHERS: ' ||
1378 SQLERRM;
1379
1380 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1381
1382 end Drop_TAE_TRANS_Indexes;
1383
1384 -- ***************************************************
1385 -- API Specifications
1386 -- ***************************************************
1387 -- api name : Get_SQL_For_Changes
1388 -- type : public.
1389 -- function : Get_SQL_For_Changes
1390 -- pre-reqs :
1391 -- notes:
1392 --
1393
1394 PROCEDURE Get_SQL_For_Changes (
1395 p_source_id IN NUMBER,
1396 p_trans_object_type_id IN NUMBER,
1397 p_view_name IN VARCHAR2,
1398 x_return_status OUT NOCOPY VARCHAR2,
1399 p_sql OUT NOCOPY JTF_TAE_GEN_PVT.terrsql_tbl_type,
1400 x_msg_count OUT NOCOPY NUMBER,
1401 x_msg_data OUT NOCOPY VARCHAR2,
1402 ERRBUF OUT NOCOPY VARCHAR2,
1403 RETCODE OUT NOCOPY VARCHAR2
1404 )
1405 AS
1406 l_source_id number := p_source_id;
1407 l_qual_type_id number := p_trans_object_type_id;
1408 l_view_name VARCHAR2(30) := p_view_name;
1409 BEGIN
1410 --dbms_output.put_line(' Get_SQL_For_Changes :BEGINS ');
1411 JTF_TAE_GEN_PVT.gen_details_for_terr_change(
1412 p_source_id => l_source_id, --IN NUMBER,
1413 p_qual_type_id => l_qual_type_id, --IN NUMBER,
1414 p_view_name => l_view_name, --IN VARCHAR2,
1415 p_sql => p_sql --OUT NOCOPY terrsql_tbl_type
1416 );
1417 --dbms_output.put_line('JTF_TAE_GEN_PVT.gen_details_for_terr_change returned');
1418
1419 /* Program completed successfully */
1420 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_SQL_FOR_CHANGES: Successfully completed.';
1421 x_return_status := FND_API.G_RET_STS_SUCCESS;
1422 RETCODE := 0;
1423
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427 END Get_SQL_For_Changes;
1428
1429
1430
1431
1432 -- ***************************************************
1433 -- API Specifications
1434 -- ***************************************************
1435 -- api name : Clear_trans_data
1436 -- type : public.
1437 -- function : Truncate Trans Table, and Drop_TAE_TRANS_Indexes
1438 -- pre-reqs :
1439 -- notes:
1440 --
1441
1442 PROCEDURE Clear_Trans_Data
1443 ( p_api_version_number IN NUMBER,
1444 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1445 p_SQL_Trace IN VARCHAR2,
1446 p_Debug_Flag IN VARCHAR2,
1447 x_return_status OUT NOCOPY VARCHAR2,
1448 p_source_id IN NUMBER,
1449 p_trans_object_type_id IN NUMBER,
1450 p_target_type IN VARCHAR2 := 'TAP',
1451 x_msg_count OUT NOCOPY NUMBER,
1452 x_msg_data OUT NOCOPY VARCHAR2,
1453 ERRBUF OUT NOCOPY VARCHAR2,
1454 RETCODE OUT NOCOPY VARCHAR2
1455 )
1456 AS
1457 l_api_name CONSTANT VARCHAR2(30) := 'CLEAR_TRANS_DATA';
1458 l_api_version_number CONSTANT NUMBER := 1.0;
1459 l_return_status VARCHAR2(1);
1460
1461 l_trans_target varchar2(30);
1462 l_matches_target varchar2(30);
1463 l_winners_target varchar2(30);
1464 l_source_id number := p_source_id;
1465 l_trans_id number := p_trans_object_type_id;
1466 l_execute_str varchar2(360);
1467
1468 l_terr_L1_target VARCHAR2(30);
1469 l_terr_L2_target VARCHAR2(30);
1470 l_terr_L3_target VARCHAR2(30);
1471 l_terr_L4_target VARCHAR2(30);
1472 l_terr_L5_target VARCHAR2(30);
1473 l_terr_WT_target VARCHAR2(30);
1474
1475 BEGIN
1476 -- Process Parameters
1477
1478 IF (l_source_id = -1001) THEN
1479 IF (p_trans_object_type_id = -1002) THEN
1480 --ARPATEL 09/12/2003 OIC requirements
1481 IF p_target_type = 'OIC_TAP' THEN
1482 l_trans_target := 'JTF_TAE_1001_SC_TRANS';
1483 l_matches_target := 'JTF_TAE_1001_SC_MATCHES';
1484 l_winners_target := 'JTF_TAE_1001_SC_WINNERS';
1485
1486 l_terr_L1_target := 'JTF_TAE_1001_SC_L1';
1487 l_terr_L2_target := 'JTF_TAE_1001_SC_L2';
1488 l_terr_L3_target := 'JTF_TAE_1001_SC_L3';
1489 l_terr_L4_target := 'JTF_TAE_1001_SC_L4';
1490 l_terr_L5_target := 'JTF_TAE_1001_SC_L5';
1491 l_terr_WT_target := 'JTF_TAE_1001_SC_WT';
1492 ELSE
1493 l_trans_target := 'JTF_TAE_1001_ACCOUNT_TRANS';
1494 l_matches_target := 'JTF_TAE_1001_ACCOUNT_MATCHES';
1495 l_winners_target := 'JTF_TAE_1001_ACCOUNT_WINNERS';
1496
1497 l_terr_L1_target := 'JTF_TAE_1001_ACCOUNT_L1';
1498 l_terr_L2_target := 'JTF_TAE_1001_ACCOUNT_L2';
1499 l_terr_L3_target := 'JTF_TAE_1001_ACCOUNT_L3';
1500 l_terr_L4_target := 'JTF_TAE_1001_ACCOUNT_L4';
1501 l_terr_L5_target := 'JTF_TAE_1001_ACCOUNT_L5';
1502 l_terr_WT_target := 'JTF_TAE_1001_ACCOUNT_WT';
1503
1504 END IF; --p_target_type = 'OIC_TAP'
1505 ELSIF (p_trans_object_type_id = -1003) THEN
1506 l_trans_target := 'JTF_TAE_1001_LEAD_TRANS';
1507 l_matches_target := 'JTF_TAE_1001_LEAD_MATCHES';
1508 l_winners_target := 'JTF_TAE_1001_LEAD_WINNERS';
1509
1510 l_terr_L1_target := 'JTF_TAE_1001_LEAD_L1';
1511 l_terr_L2_target := 'JTF_TAE_1001_LEAD_L2';
1512 l_terr_L3_target := 'JTF_TAE_1001_LEAD_L3';
1513 l_terr_L4_target := 'JTF_TAE_1001_LEAD_L4';
1514 l_terr_L5_target := 'JTF_TAE_1001_LEAD_L5';
1515 l_terr_WT_target := 'JTF_TAE_1001_LEAD_WT';
1516
1517 ELSIF (p_trans_object_type_id = -1004) THEN
1518 l_trans_target := 'JTF_TAE_1001_OPPOR_TRANS';
1519 l_matches_target := 'JTF_TAE_1001_OPPOR_MATCHES';
1520 l_winners_target := 'JTF_TAE_1001_OPPOR_WINNERS';
1521
1522 l_terr_L1_target := 'JTF_TAE_1001_OPPOR_L1';
1523 l_terr_L2_target := 'JTF_TAE_1001_OPPOR_L2';
1524 l_terr_L3_target := 'JTF_TAE_1001_OPPOR_L3';
1525 l_terr_L4_target := 'JTF_TAE_1001_OPPOR_L4';
1526 l_terr_L5_target := 'JTF_TAE_1001_OPPOR_L5';
1527 l_terr_WT_target := 'JTF_TAE_1001_OPPOR_WT';
1528
1529 END IF; -- what tx type
1530 END IF; -- what usage
1531
1532 -------------
1533 --- [P01] --- TRUNCATE TRANS
1534 -------------
1535 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1536 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P01] Call to ' ||
1537 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API BEGINS ' ||
1538 'for ' || l_trans_target);
1539
1540 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1541 p_table_name => l_trans_target
1542 , x_return_status => x_return_status );
1543
1544 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1545 ERRBUF := 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P01] Call to ' ||
1546 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1547 l_trans_target;
1548
1549 RAISE FND_API.G_EXC_ERROR;
1550 END IF;
1551
1552
1553 -------------
1554 --- [P02] --- TRUNCATE MATCHES
1555 -------------
1556 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1557 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P02] Call to ' ||
1558 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API BEGINS ' ||
1559 'for ' || l_matches_target);
1560
1561
1562 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1563 p_table_name => l_matches_target
1564 , x_return_status => x_return_status);
1565
1566 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1567 p_table_name => l_matches_target
1568 , x_return_status => x_return_status );
1569
1570 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1571 ERRBUF := 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P02] Call to ' ||
1572 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1573 l_matches_target;
1574 RAISE FND_API.G_EXC_ERROR;
1575 END IF;
1576
1577 -------------
1578 --- [P03] --- TRUNCATE WINNERS
1579 -------------
1580 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1581 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P03] Call to ' ||
1582 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API BEGINS ' ||
1583 'for ' || l_winners_target);
1584
1585 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1586 p_table_name => l_winners_target
1587 , x_return_status => x_return_status);
1588
1589 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1590 p_table_name => l_winners_target
1591 , x_return_status => x_return_status );
1592
1593 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1594 ERRBUF := 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P03] Call to ' ||
1595 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1596 l_winners_target;
1597 RAISE FND_API.G_EXC_ERROR;
1598 END IF;
1599
1600 -------------
1601 --- [P03.1] -
1602 -------------
1603 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1604 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03.1] Call to ' ||
1605 'MULTI-LEVEL TABLE CLEANUP BEGINS...');
1606
1607 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1608 p_table_name => l_terr_L1_target
1609 , x_return_status => x_return_status);
1610
1611 ----dbms_output.put_line('10.1: Truncate Level Winners Tables ');
1612 -- 10.1: Truncate Level Winners Tables
1613 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1614 p_table_name => l_terr_L1_target
1615 , x_return_status => x_return_status );
1616
1617 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1618 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03.1] Call to ' ||
1619 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1620 l_terr_L1_target;
1621 RAISE FND_API.G_EXC_ERROR;
1622 END IF;
1623
1624 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1625 p_table_name => l_terr_L2_target
1626 , x_return_status => x_return_status);
1627
1628 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1629 p_table_name => l_terr_L2_target
1630 , x_return_status => x_return_status );
1631
1632 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1633 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03.1] Call to ' ||
1634 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1635 l_terr_L2_target;
1636 RAISE FND_API.G_EXC_ERROR;
1637 END IF;
1638
1639
1640 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1641 p_table_name => l_terr_L3_target
1642 , x_return_status => x_return_status);
1643
1644 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1645 p_table_name => l_terr_L3_target
1646 , x_return_status => x_return_status );
1647
1648 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1649 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03.1] Call to ' ||
1650 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1651 l_terr_L3_target;
1652 RAISE FND_API.G_EXC_ERROR;
1653 END IF;
1654
1655 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1656 p_table_name => l_terr_L4_target
1657 , x_return_status => x_return_status);
1658
1659 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1660 p_table_name => l_terr_L4_target
1661 , x_return_status => x_return_status );
1662
1663 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1664 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03.1] Call to ' ||
1665 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1666 l_terr_L4_target;
1667 RAISE FND_API.G_EXC_ERROR;
1668 END IF;
1669
1670
1671 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1672 p_table_name => l_terr_L5_target
1673 , x_return_status => x_return_status);
1674
1675 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1676 p_table_name => l_terr_L5_target
1677 , x_return_status => x_return_status );
1678
1679 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1680 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03.1] Call to ' ||
1681 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1682 l_terr_L5_target;
1683 RAISE FND_API.G_EXC_ERROR;
1684 END IF;
1685
1686
1687 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
1688 p_table_name => l_terr_WT_target
1689 , x_return_status => x_return_status);
1690
1691 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
1692 p_table_name => l_terr_WT_target
1693 , x_return_status => x_return_status );
1694
1695 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1696 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03.1] Call to ' ||
1697 'JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE API has failed for ' ||
1698 l_terr_WT_target;
1699 RAISE FND_API.G_EXC_ERROR;
1700 END IF;
1701
1702 -------------
1703 --- [P04] --- DROP INDEXES for TRANS TABLE ONLY (WE KEEP INDEXES FOR MATCHES AND WINNERS )
1704 -------------
1705 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1706 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P04] Call to ' ||
1707 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API BEGINS ' ||
1708 'for ' || l_trans_target);
1709
1710 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES( p_table_name => l_trans_target
1711 , x_return_status => x_return_status );
1712
1713 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1714
1715 ERRBUF := 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [P04] Call to ' ||
1716 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES API has failed for ' ||
1717 l_trans_target;
1718
1719 RAISE FND_API.G_EXC_ERROR;
1720
1721 END IF;
1722
1723 EXCEPTION
1724 WHEN FND_API.G_EXC_ERROR THEN
1725 x_return_status := FND_API.G_RET_STS_ERROR;
1726 RETCODE := 2;
1727 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1728
1729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1731 RETCODE := 2;
1732 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1733
1734 WHEN utl_file.invalid_path OR
1735 utl_file.invalid_mode OR
1736 utl_file.invalid_filehandle OR
1737 Utl_file.invalid_operation OR
1738 utl_file.write_error THEN
1739
1740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1741 RETCODE := 2;
1742 ERRBUF := 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [END] UTL_FILE: ' ||
1743 SQLERRM;
1744 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1745
1746 WHEN OTHERS THEN
1747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1748 RETCODE := 2;
1749 ERRBUF := 'JTF_TAE_ASSIGN_PUB.CLEAR_TRANS_DATA: [END] OTHERS: ' ||
1750 SQLERRM;
1751 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
1752
1753 END Clear_Trans_Data;
1754
1755
1756 -- ***************************************************
1757 -- API Specifications
1758 -- ***************************************************
1759 -- api name : GET_WINNERS_PARALLEL_SETUP
1760 -- type : public.
1761 -- function :
1762 -- pre-reqs :
1763 -- notes:
1764 --
1765
1766 PROCEDURE get_winners_parallel_setup
1767 ( p_api_version_number IN NUMBER,
1768 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1769 p_SQL_Trace IN VARCHAR2,
1770 p_Debug_Flag IN VARCHAR2,
1771 x_return_status OUT NOCOPY VARCHAR2,
1772 x_msg_count OUT NOCOPY NUMBER,
1773 x_msg_data OUT NOCOPY VARCHAR2,
1774 p_request_id IN NUMBER,
1775 p_source_id IN NUMBER,
1776 p_trans_object_type_id IN NUMBER,
1777 p_target_type IN VARCHAR2 := 'TAP',
1778 ERRBUF OUT NOCOPY VARCHAR2,
1779 RETCODE OUT NOCOPY VARCHAR2
1780 )
1781 AS
1782
1783 l_api_name CONSTANT VARCHAR2(30) := 'GET_WINNERS_PARALLEL_SETUP';
1784 l_api_version_number CONSTANT NUMBER := 1.0;
1785 l_return_status VARCHAR2(1);
1786 l_count1 NUMBER := 0;
1787 l_count2 NUMBER := 0;
1788 l_RscCounter NUMBER := 0;
1789 l_NumberOfWinners NUMBER ;
1790
1791 lp_sysdate DATE := SYSDATE;
1792 l_rsc_counter NUMBER := 0;
1793 l_dyn_str VARCHAR2(32767);
1794 num_of_terr NUMBER;
1795 num_of_trans NUMBER;
1796 d_statement VARCHAR2(2000);
1797
1798 l_trans_target VARCHAR2(30);
1799 l_matches_target VARCHAR2(30);
1800 l_winners_target VARCHAR2(30);
1801
1802 l_terr_L1_target VARCHAR2(30);
1803 l_terr_L2_target VARCHAR2(30);
1804 l_terr_L3_target VARCHAR2(30);
1805 l_terr_L4_target VARCHAR2(30);
1806 l_terr_L5_target VARCHAR2(30);
1807 l_terr_WT_target VARCHAR2(30);
1808
1809 l_matches_idx VARCHAR2(30);
1810
1811 l_access_list VARCHAR2(300);
1812 l_truncate_matches_rtn_val NUMBER;
1813 l_create_idx_match_rtn_val NUMBER;
1814
1815 lX_Msg_Data VARCHAR2(2000);
1816 lx_runtime VARCHAR2(300);
1817
1818 BEGIN
1819
1820 x_return_status := FND_API.G_RET_STS_SUCCESS;
1821
1822 /* If the debug flag is set, Then turn on the debug message logging */
1823 If UPPER( rtrim(p_Debug_Flag) ) = 'Y' Then
1824 JTF_TAE_CONTROL_PVT.G_DEBUG := TRUE;
1825 End If;
1826
1827 /* ARPATEL: 12/15/2003: Bug#3305019 */
1828 --If UPPER(p_SQL_Trace) = 'Y' Then
1829 -- dbms_session.set_sql_trace(TRUE);
1830 --End If;
1831
1832 -- 05/16/02: JDOCHERT
1833 -- Need COMMIT to avoid the following error:
1834 -- ORA-12841: Cannot alter the session parallel DML state within a transaction
1835 -- in the call to JTF_TAE_CONTROL_PVT.set_session_parameters below
1836 COMMIT;
1837
1838 /* Standard call to check for call compatibility. */
1839 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1840 p_api_version_number,
1841 l_api_name,
1842 G_PKG_NAME) THEN
1843
1844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1845
1846 END IF;
1847
1848 /* Initialize message list if p_init_msg_list is set to TRUE. */
1849 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1850 FND_MSG_PUB.initialize;
1851 END IF;
1852
1853 -- Debug Message
1854 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1855 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MEM_TASK_START');
1856 FND_MSG_PUB.Add;
1857 END IF;
1858
1859 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1860 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, '/***************** BEGIN: TERRITORY ASSIGNMENT STATUS *********************/');
1861 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
1862 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP start....');
1863 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'source_id = ' || TO_CHAR(p_source_Id) );
1864 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'trans_object_type_id = ' || TO_CHAR(p_trans_object_type_id) );
1865
1866 /* JDOCHERT 05/05/02: Commented out settings in JTF_TAE_CONTROL
1867 ** since OSO TAP program has these paramaters
1868 */
1869 --JTF_TAE_CONTROL_PVT.set_session_parameters
1870 -- ( p_sort_area_size => 100000000
1871 -- , p_hash_area_size => 200000000);
1872
1873 -----------------------------------------------------------
1874 -- logic control to see which dyn package should be called
1875 -----------------------------------------------------------
1876
1877 IF ( p_source_id = -1001 ) THEN
1878 ----dbms_output.put_line(' p_source_id = -1001 ');
1879
1880 IF (p_trans_object_type_id = -1002) THEN
1881
1882 --ARPATEL 09/12/2003 OIC requirements
1883 IF ( p_target_type = 'OIC_TAP' ) THEN
1884 l_trans_target := 'JTF_TAE_1001_SC_TRANS';
1885 l_matches_target := 'JTF_TAE_1001_SC_MATCHES';
1886 l_winners_target := 'JTF_TAE_1001_SC_WINNERS';
1887
1888 l_terr_L1_target := 'JTF_TAE_1001_SC_L1';
1889 l_terr_L2_target := 'JTF_TAE_1001_SC_L2';
1890 l_terr_L3_target := 'JTF_TAE_1001_SC_L3';
1891 l_terr_L4_target := 'JTF_TAE_1001_SC_L4';
1892 l_terr_L5_target := 'JTF_TAE_1001_SC_L5';
1893 l_terr_WT_target := 'JTF_TAE_1001_SC_WT';
1894 l_access_list := ' ''ACCOUNT'', 1, ''LEAD'', 1, ''OPPOR'', 1 ';
1895 ELSE
1896 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
1897 l_trans_target := 'JTF_TAE_1001_ACCOUNT_NM_TRANS';
1898 ELSE
1899 l_trans_target := 'JTF_TAE_1001_ACCOUNT_TRANS';
1900 END IF;
1901 l_matches_target := 'JTF_TAE_1001_ACCOUNT_MATCHES';
1902 l_winners_target := 'JTF_TAE_1001_ACCOUNT_WINNERS';
1903
1904 l_terr_L1_target := 'JTF_TAE_1001_ACCOUNT_L1';
1905 l_terr_L2_target := 'JTF_TAE_1001_ACCOUNT_L2';
1906 l_terr_L3_target := 'JTF_TAE_1001_ACCOUNT_L3';
1907 l_terr_L4_target := 'JTF_TAE_1001_ACCOUNT_L4';
1908 l_terr_L5_target := 'JTF_TAE_1001_ACCOUNT_L5';
1909 l_terr_WT_target := 'JTF_TAE_1001_ACCOUNT_WT';
1910
1911 l_access_list := ' ''ACCOUNT'', 1, ''LEAD'', 1, ''OPPOR'', 1 ';
1912 END IF; --p_target_type = 'OIC_TAP'
1913
1914 ELSIF (p_trans_object_type_id = -1003) THEN
1915 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
1916 l_trans_target := 'JTF_TAE_1001_LEAD_NM_TRANS';
1917 ELSE
1918 l_trans_target := 'JTF_TAE_1001_LEAD_TRANS';
1919 END IF;
1920 l_matches_target := 'JTF_TAE_1001_LEAD_MATCHES';
1921 l_winners_target := 'JTF_TAE_1001_LEAD_WINNERS';
1922
1923 l_terr_L1_target := 'JTF_TAE_1001_LEAD_L1';
1924 l_terr_L2_target := 'JTF_TAE_1001_LEAD_L2';
1925 l_terr_L3_target := 'JTF_TAE_1001_LEAD_L3';
1926 l_terr_L4_target := 'JTF_TAE_1001_LEAD_L4';
1927 l_terr_L5_target := 'JTF_TAE_1001_LEAD_L5';
1928 l_terr_WT_target := 'JTF_TAE_1001_LEAD_WT';
1929
1930 l_access_list := ' ''LEAD'', 1 ';
1931
1932 ELSIF (p_trans_object_type_id = -1004) THEN
1933 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
1934 l_trans_target := 'JTF_TAE_1001_OPPOR_NM_TRANS';
1935 ELSE
1936 l_trans_target := 'JTF_TAE_1001_OPPOR_TRANS';
1937 END IF;
1938 l_matches_target := 'JTF_TAE_1001_OPPOR_MATCHES';
1939 l_winners_target := 'JTF_TAE_1001_OPPOR_WINNERS';
1940
1941 l_terr_L1_target := 'JTF_TAE_1001_OPPOR_L1';
1942 l_terr_L2_target := 'JTF_TAE_1001_OPPOR_L2';
1943 l_terr_L3_target := 'JTF_TAE_1001_OPPOR_L3';
1944 l_terr_L4_target := 'JTF_TAE_1001_OPPOR_L4';
1945 l_terr_L5_target := 'JTF_TAE_1001_OPPOR_L5';
1946 l_terr_WT_target := 'JTF_TAE_1001_OPPOR_WT';
1947
1948 l_access_list := ' ''OPPOR'', 1 ';
1949
1950 END IF; -- what tx type
1951 END IF; -- what usage
1952
1953 -- (-1 indentation)
1954
1955 /* set NOLOGGING on JTF_TAE_..._MATCHES and JTF_TAE_..._WINNERS tables */
1956 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_trans_target);
1957 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_matches_target);
1958 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_winners_target);
1959
1960 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L1_target);
1961 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L2_target);
1962 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L3_target);
1963 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L4_target);
1964 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_L5_target);
1965 JTF_TAE_CONTROL_PVT.set_table_nologging(p_table_name => l_terr_WT_target);
1966
1967 -------------
1968 --- [P00] ---
1969 -------------
1970 BEGIN
1971
1972 /* Check for territories for this Usage/Transaction Type */
1973 SELECT COUNT(*)
1974 INTO num_of_terr
1975 FROM jtf_terr_qtype_usgs_all jtqu
1976 , jtf_terr_usgs_all jtu
1977 , jtf_terr_all jt1
1978 , jtf_qual_type_usgs jqtu
1979 WHERE jtqu.terr_id = jt1.terr_id
1980 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
1981 AND jqtu.qual_type_id = p_trans_object_type_id
1982 AND jtu.source_id = p_source_id
1983 AND jtu.terr_id = jt1.terr_id
1984 AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
1985 AND jt1.start_date_active <= lp_sysdate
1986 AND EXISTS (
1987 SELECT jtrs.terr_rsc_id
1988 FROM jtf_terr_rsc_all jtrs
1989 WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
1990 AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
1991 AND jtrs.terr_id = jt1.terr_id )
1992 AND NOT EXISTS (
1993 SELECT jt.terr_id
1994 FROM jtf_terr_all jt
1995 WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
1996 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
1997 START WITH jt.terr_id = jt1.terr_id)
1998 AND jqtu.qual_type_id <> -1001;
1999
2000 EXCEPTION
2001 WHEN NO_DATA_FOUND THEN
2002 num_of_terr := 0;
2003 END;
2004
2005 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2006 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P00] Number of valid ' ||
2007 'Territories with Resources for this Transaction: ' ||
2008 num_of_terr );
2009
2010 /* 2357180: ERROR HANDLING FOR NO TERRITORY DATA:
2011 ** AFTER MATCHES AND WINNERS TABLES HAVE BEEN TRUNCATED */
2012 IF (num_of_terr = 0) THEN
2013 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P00] There are NO Active Territories with Active ' ||
2014 'Resources existing for this Usage/Transaction combination, so no assignments ' ||
2015 'can take place.';
2016
2017 RAISE NO_TAE_DATA_FOUND;
2018
2019 END IF;
2020
2021 -------------
2022 --- [P01] ---
2023 -------------
2024 BEGIN
2025 d_statement := ' SELECT COUNT(*) FROM ' ||
2026 l_trans_target ||
2027 ' WHERE rownum < 2 ';
2028 EXECUTE IMMEDIATE d_statement INTO num_of_trans;
2029
2030 EXCEPTION
2031 WHEN NO_DATA_FOUND THEN
2032 num_of_trans := 0;
2033 END;
2034
2035 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2036 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P01] ' ||
2037 'There are valid Transaction Objects to be assigned.');
2038
2039 IF (num_of_trans = 0) THEN
2040 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P01] There are NO valid Transaction Objects to assign.';
2041 RAISE NO_TAE_DATA_FOUND;
2042 END IF;
2043
2044 -------------
2045 --- [P02] ---
2046 -------------
2047 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2048 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P02] Call to ' ||
2049 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API BEGINS ' ||
2050 'for ' || l_trans_target);
2051
2052 /* Analyze _TRANS table */
2053 /* JDOCHERT: 04/10/03: bug#2896552 */
2054 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2055 p_table_name => l_trans_target
2056 , p_percent => 99
2057 , x_return_status => x_return_status );
2058
2059 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2060 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P02] Call to ' ||
2061 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' ||
2062 l_trans_target;
2063 RAISE FND_API.G_EXC_ERROR;
2064 END IF;
2065
2066 -------------
2067 --- [P03] ---
2068 -------------
2069 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2070 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03] Call to ' ||
2071 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS API BEGINS...');
2072 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2073
2074 JTF_TAE_CONTROL_PVT.Decompose_Terr_Defns
2075 (p_Api_Version_Number => 1.0,
2076 p_Init_Msg_List => FND_API.G_FALSE,
2077 p_Commit => FND_API.G_FALSE,
2078 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2079 x_Return_Status => x_return_status,
2080 x_Msg_Count => x_msg_count,
2081 x_Msg_Data => x_msg_data,
2082 p_run_mode => p_target_type,
2083 p_classify_terr_comb => 'N',
2084 p_process_tx_oin_sel => 'Y',
2085 p_generate_indexes => 'Y',
2086 p_source_id => p_source_id,
2087 p_trans_id => p_trans_object_type_id,
2088 errbuf => ERRBUF,
2089 retcode => RETCODE );
2090
2091 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2092 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [P03] Call to ' ||
2093 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS API has failed.';
2094 RAISE FND_API.G_EXC_ERROR;
2095 END IF;
2096
2097
2098 -----------
2099 --- [P04.1] ---
2100 -----------
2101 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2102 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.1] Call to ' ||
2103 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2104 'for ' || l_matches_target);
2105
2106 /* Build Index on Winners table */
2107 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_matches_target
2108 , p_trans_object_type_id
2109 , p_source_id
2110 , l_return_status
2111 , p_target_type);
2112
2113 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2114 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.1] Call to ' ||
2115 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2116 l_matches_target;
2117 RAISE FND_API.G_EXC_ERROR;
2118 END IF;
2119
2120 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2121 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.2] Call to ' ||
2122 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2123 'for ' || l_winners_target);
2124
2125 /* Build Index on Winners table */
2126 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_winners_target
2127 , p_trans_object_type_id
2128 , p_source_id
2129 , l_return_status
2130 , p_target_type );
2131
2132 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2133 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.2] Call to ' ||
2134 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2135 l_winners_target;
2136 RAISE FND_API.G_EXC_ERROR;
2137 END IF;
2138
2139
2140 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2141 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.3] Call to ' ||
2142 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2143 'for ' || l_terr_L1_target);
2144
2145 /* Build Index on Winners table */
2146 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_terr_L1_target
2147 , p_trans_object_type_id
2148 , p_source_id
2149 , l_return_status
2150 , p_target_type );
2151
2152 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2153 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.3] Call to ' ||
2154 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2155 l_terr_L1_target;
2156 RAISE FND_API.G_EXC_ERROR;
2157 END IF;
2158
2159
2160 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2161 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.4] Call to ' ||
2162 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2163 'for ' || l_terr_L2_target);
2164
2165 /* Build Index on Winners table */
2166 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_terr_L2_target
2167 , p_trans_object_type_id
2168 , p_source_id
2169 , l_return_status
2170 , p_target_type );
2171
2172 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2173 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.4] Call to ' ||
2174 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2175 l_terr_L2_target;
2176 RAISE FND_API.G_EXC_ERROR;
2177 END IF;
2178
2179
2180
2181 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2182 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.5] Call to ' ||
2183 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2184 'for ' || l_terr_L3_target);
2185
2186 /* Build Index on Winners table */
2187 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_terr_L3_target
2188 , p_trans_object_type_id
2189 , p_source_id
2190 , l_return_status
2191 , p_target_type );
2192
2193 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2194 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.5] Call to ' ||
2195 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2196 l_terr_L3_target;
2197 RAISE FND_API.G_EXC_ERROR;
2198 END IF;
2199
2200
2201
2202 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2203 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.6] Call to ' ||
2204 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2205 'for ' || l_terr_L4_target);
2206
2207 /* Build Index on Winners table */
2208 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_terr_L4_target
2209 , p_trans_object_type_id
2210 , p_source_id
2211 , l_return_status
2212 , p_target_type );
2213
2214 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2215 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.6] Call to ' ||
2216 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2217 l_terr_L4_target;
2218 RAISE FND_API.G_EXC_ERROR;
2219 END IF;
2220
2221
2222 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2223 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.7] Call to ' ||
2224 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2225 'for ' || l_terr_L5_target);
2226
2227 /* Build Index on Winners table */
2228 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_terr_L5_target
2229 , p_trans_object_type_id
2230 , p_source_id
2231 , l_return_status
2232 , p_target_type );
2233
2234 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2235 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.7] Call to ' ||
2236 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2237 l_terr_L5_target;
2238 RAISE FND_API.G_EXC_ERROR;
2239 END IF;
2240
2241
2242 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2243 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.8] Call to ' ||
2244 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API BEGINS ' ||
2245 'for ' || l_terr_WT_target);
2246
2247 /* Build Index on Winners table */
2248 JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX( l_terr_WT_target
2249 , p_trans_object_type_id
2250 , p_source_id
2251 , l_return_status
2252 , p_target_type );
2253
2254 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2255 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P04.8] Call to ' ||
2256 'JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX API has failed for ' ||
2257 l_terr_WT_target;
2258 RAISE FND_API.G_EXC_ERROR;
2259 END IF;
2260
2261
2262 EXCEPTION
2263 WHEN NO_TAE_DATA_FOUND THEN
2264 x_return_status := FND_API.G_RET_STS_SUCCESS;
2265 RETCODE := 1;
2266 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
2267
2268 WHEN FND_API.G_EXC_ERROR THEN
2269 x_return_status := FND_API.G_RET_STS_ERROR;
2270 RETCODE := 2;
2271 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
2272
2273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2275 RETCODE := 2;
2276 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
2277
2278 WHEN utl_file.invalid_path OR
2279 utl_file.invalid_mode OR
2280 utl_file.invalid_filehandle OR
2281 utl_file.invalid_operation OR
2282 utl_file.write_error THEN
2283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2284 RETCODE := 2;
2285 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [END] UTL_FILE: ' ||
2286 SQLERRM;
2287 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
2288 WHEN OTHERS THEN
2289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2290 RETCODE := 2;
2291 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: [END] OTHERS: ' ||
2292 SQLERRM;
2293 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
2294
2295 END get_winners_parallel_setup;
2296
2297
2298 -- ***************************************************
2299 -- API Specifications
2300 -- ***************************************************
2301 -- api name : GET_WINNERS_PARALLEL
2302 -- type : public.
2303 -- function :
2304 -- pre-reqs :
2305 -- notes: API designed to be called from multiple sessions
2306 -- to parallel process assignment of transactions to territories
2307 --
2308
2309 PROCEDURE GET_WINNERS_PARALLEL
2310 ( p_api_version_number IN NUMBER,
2311 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2312 p_SQL_Trace IN VARCHAR2,
2313 p_Debug_Flag IN VARCHAR2,
2314 x_return_status OUT NOCOPY VARCHAR2,
2315 x_msg_count OUT NOCOPY NUMBER,
2316 x_msg_data OUT NOCOPY VARCHAR2,
2317 p_request_id IN NUMBER,
2318 p_source_id IN NUMBER,
2319 p_trans_object_type_id IN NUMBER,
2320 p_target_type IN VARCHAR2 := 'TAP',
2321 ERRBUF OUT NOCOPY VARCHAR2,
2322 RETCODE OUT NOCOPY VARCHAR2,
2323 p_worker_id IN NUMBER := 1,
2324 p_total_workers IN NUMBER
2325 )
2326 AS
2327
2328 l_api_name CONSTANT VARCHAR2(30) := 'GET_WINNERS_PARALLEL';
2329 l_api_version_number CONSTANT NUMBER := 1.0;
2330 l_return_status VARCHAR2(1);
2331 l_count1 NUMBER := 0;
2332 l_count2 NUMBER := 0;
2333 l_RscCounter NUMBER := 0;
2334 l_NumberOfWinners NUMBER ;
2335
2336 lp_sysdate DATE := SYSDATE;
2337 l_rsc_counter NUMBER := 0;
2338 l_dyn_str VARCHAR2(32767);
2339 num_of_terr NUMBER;
2340 num_of_trans NUMBER;
2341 l_num_of_trans NUMBER;
2342 d_statement VARCHAR2(2000);
2343 l_trans_count_sql VARCHAR2(2000);
2344
2345 l_trans_target VARCHAR2(30);
2346 l_matches_target VARCHAR2(30);
2347 l_winners_target VARCHAR2(30);
2348 l_terr_L1_target VARCHAR2(30);
2349 l_terr_L2_target VARCHAR2(30);
2350 l_terr_L3_target VARCHAR2(30);
2351 l_terr_L4_target VARCHAR2(30);
2352 l_terr_L5_target VARCHAR2(30);
2353 l_terr_WT_target VARCHAR2(30);
2354
2355
2356 l_matches_idx VARCHAR2(30);
2357
2358 l_access_list VARCHAR2(300);
2359 l_truncate_matches_rtn_val NUMBER;
2360 l_create_idx_match_rtn_val NUMBER;
2361
2362 lX_Msg_Data VARCHAR2(2000);
2363 lx_runtime VARCHAR2(300);
2364
2365 BEGIN
2366
2367 x_return_status := FND_API.G_RET_STS_SUCCESS;
2368
2369 /* If the debug flag is set, Then turn on the debug message logging */
2370 If UPPER( rtrim(p_Debug_Flag) ) = 'Y' Then
2371 JTF_TAE_CONTROL_PVT.G_DEBUG := TRUE;
2372 End If;
2373
2374 /* ARPATEL: 12/15/2003: Bug#3305019 */
2375 --If UPPER(p_SQL_Trace) = 'Y' Then
2376 -- dbms_session.set_sql_trace(TRUE);
2377 --ARPATEL: 10/02/03: removed this for performance team
2378 --Else
2379 -- dbms_session.set_sql_trace(FALSE);
2380 -- End If;
2381
2382 IF ( p_source_id = -1001 ) THEN
2383 IF (p_trans_object_type_id = -1002) THEN
2384
2385 --ARPATEL 09/12/2003 OIC requirements
2386 IF ( p_target_type = 'OIC_TAP' ) THEN
2387 l_trans_target := 'JTF_TAE_1001_SC_TRANS';
2388 l_matches_target := 'JTF_TAE_1001_SC_MATCHES';
2389 l_winners_target := 'JTF_TAE_1001_SC_WINNERS';
2390
2391 l_terr_L1_target := 'JTF_TAE_1001_SC_L1';
2392 l_terr_L2_target := 'JTF_TAE_1001_SC_L2';
2393 l_terr_L3_target := 'JTF_TAE_1001_SC_L3';
2394 l_terr_L4_target := 'JTF_TAE_1001_SC_L4';
2395 l_terr_L5_target := 'JTF_TAE_1001_SC_L5';
2396 l_terr_WT_target := 'JTF_TAE_1001_SC_WT';
2397 l_access_list := ' ''ACCOUNT'' ';
2398 ELSE
2399 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
2400 l_trans_target := 'JTF_TAE_1001_ACCOUNT_NM_TRANS';
2401 ELSE
2402 l_trans_target := 'JTF_TAE_1001_ACCOUNT_TRANS';
2403 END IF;
2404 l_matches_target := 'JTF_TAE_1001_ACCOUNT_MATCHES';
2405 l_winners_target := 'JTF_TAE_1001_ACCOUNT_WINNERS';
2406
2407 l_terr_L1_target := 'JTF_TAE_1001_ACCOUNT_L1';
2408 l_terr_L2_target := 'JTF_TAE_1001_ACCOUNT_L2';
2409 l_terr_L3_target := 'JTF_TAE_1001_ACCOUNT_L3';
2410 l_terr_L4_target := 'JTF_TAE_1001_ACCOUNT_L4';
2411 l_terr_L5_target := 'JTF_TAE_1001_ACCOUNT_L5';
2412 l_terr_WT_target := 'JTF_TAE_1001_ACCOUNT_WT';
2413
2414 l_access_list := ' ''ACCOUNT'' ';
2415
2416 END IF; --p_target_type = 'OIC_TAP'
2417
2418 ELSIF (p_trans_object_type_id = -1003) THEN
2419 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
2420 l_trans_target := 'JTF_TAE_1001_LEAD_NM_TRANS';
2421 ELSE
2422 l_trans_target := 'JTF_TAE_1001_LEAD_TRANS';
2423 END IF;
2424 l_matches_target := 'JTF_TAE_1001_LEAD_MATCHES';
2425 l_winners_target := 'JTF_TAE_1001_LEAD_WINNERS';
2426
2427 l_terr_L1_target := 'JTF_TAE_1001_LEAD_L1';
2428 l_terr_L2_target := 'JTF_TAE_1001_LEAD_L2';
2429 l_terr_L3_target := 'JTF_TAE_1001_LEAD_L3';
2430 l_terr_L4_target := 'JTF_TAE_1001_LEAD_L4';
2431 l_terr_L5_target := 'JTF_TAE_1001_LEAD_L5';
2432 l_terr_WT_target := 'JTF_TAE_1001_LEAD_WT';
2433
2434 l_access_list := ' ''LEAD'' ';
2435
2436 ELSIF (p_trans_object_type_id = -1004) THEN
2437 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
2438 l_trans_target := 'JTF_TAE_1001_OPPOR_NM_TRANS';
2439 ELSE
2440 l_trans_target := 'JTF_TAE_1001_OPPOR_TRANS';
2441 END IF;
2442 l_matches_target := 'JTF_TAE_1001_OPPOR_MATCHES';
2443 l_winners_target := 'JTF_TAE_1001_OPPOR_WINNERS';
2444
2445 l_terr_L1_target := 'JTF_TAE_1001_OPPOR_L1';
2446 l_terr_L2_target := 'JTF_TAE_1001_OPPOR_L2';
2447 l_terr_L3_target := 'JTF_TAE_1001_OPPOR_L3';
2448 l_terr_L4_target := 'JTF_TAE_1001_OPPOR_L4';
2449 l_terr_L5_target := 'JTF_TAE_1001_OPPOR_L5';
2450 l_terr_WT_target := 'JTF_TAE_1001_OPPOR_WT';
2451
2452 l_access_list := ' ''OPPOR'' ';
2453
2454 END IF; -- what tx type
2455 END IF; -- what usage
2456 --dbms_output.put_line('GET_WINNERS_PARALLEL l_trans_target = ' || l_trans_target);
2457
2458 -------------
2459 --- [P00] --- DO ANY TRANSACTIONS EXIST FOR THIS WORKER?
2460 -------------
2461 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2462 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
2463 p_worker_id ||
2464 ' [P00] Check if accounts exist for this worker... ');
2465 BEGIN
2466
2467 l_trans_count_sql := 'SELECT COUNT(*) FROM ' || l_trans_target ||
2468 ' where worker_id = ' || p_worker_id || ' and rownum < 2 ';
2469 EXECUTE IMMEDIATE l_trans_count_sql INTO l_num_of_trans;
2470
2471 EXCEPTION
2472 WHEN NO_DATA_FOUND THEN
2473 l_num_of_trans := 0;
2474 END;
2475
2476 IF (l_num_of_trans = 0) THEN
2477 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
2478 p_worker_id ||
2479 ' [P00] NO TRANSACTIONS EXIST FOR THIS WORKER...Quitting.');
2480 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL_SETUP: worker_id= ' || p_worker_id ||
2481 ' [P00] There are NO valid Transaction Objects to assign.';
2482 RAISE NO_TAE_DATA_FOUND;
2483 END IF;
2484
2485 -------------
2486 --- [P01] --- CALL DYN PACKAGE
2487 -------------
2488 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2489 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
2490 p_worker_id ||
2491 ' [P01] Call to ' ||
2492 'JTF_TAE_1001_..._DYN.SEARCH_TERR_RULES API BEGINS... ');
2493
2494 /* Start MATCH processing */
2495 IF ( p_source_id = -1001 ) THEN
2496 IF (p_trans_object_type_id = -1002) THEN
2497 --ARPATEL 09/12/2003 OIC requirements
2498 IF ( p_target_type = 'OIC_TAP' ) THEN
2499 --dbms_output.put_line('GET_WINNERS_PARALLEL Calling JTF_TAE_1001_ACCOUNT_NM_DYN');
2500 JTF_TAE_1001_SCREDIT_DYN.Search_Terr_Rules(
2501 p_source_id => p_source_id ,
2502 p_trans_object_type_id => p_trans_object_type_id,
2503 x_Return_Status => x_return_status,
2504 x_Msg_Count => x_msg_count,
2505 x_Msg_Data => x_msg_data,
2506 p_worker_id => p_worker_id);
2507
2508 ELSIF ( p_target_type = 'NEW_MODE_TAP' ) THEN
2509 --dbms_output.put_line('GET_WINNERS_PARALLEL Calling JTF_TAE_1001_ACCOUNT_NM_DYN');
2510 JTF_TAE_1001_ACCOUNT_NM_DYN.Search_Terr_Rules(
2511 p_source_id => p_source_id ,
2512 p_trans_object_type_id => p_trans_object_type_id,
2513 x_Return_Status => x_return_status,
2514 x_Msg_Count => x_msg_count,
2515 x_Msg_Data => x_msg_data,
2516 p_worker_id => p_worker_id);
2517
2518 ELSE
2519 --dbms_output.put_line('GET_WINNERS_PARALLEL Calling JTF_TAE_1001_ACCOUNT_DYN');
2520 JTF_TAE_1001_ACCOUNT_DYN.Search_Terr_Rules(
2521 p_source_id => p_source_id ,
2522 p_trans_object_type_id => p_trans_object_type_id,
2523 x_Return_Status => x_return_status,
2524 x_Msg_Count => x_msg_count,
2525 x_Msg_Data => x_msg_data,
2526 p_worker_id => p_worker_id);
2527 END IF;
2528
2529 ELSIF (p_trans_object_type_id = -1003) THEN
2530 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
2531 JTF_TAE_1001_LEAD_NM_DYN.Search_Terr_Rules(
2532 p_source_id => p_source_id ,
2533 p_trans_object_type_id => p_trans_object_type_id,
2534 x_Return_Status => x_return_status,
2535 x_Msg_Count => x_msg_count,
2536 x_Msg_Data => x_msg_data,
2537 p_worker_id => p_worker_id);
2538
2539 ELSE
2540 JTF_TAE_1001_LEAD_DYN.Search_Terr_Rules(
2541 p_source_id => p_source_id ,
2542 p_trans_object_type_id => p_trans_object_type_id,
2543 x_Return_Status => x_return_status,
2544 x_Msg_Count => x_msg_count,
2545 x_Msg_Data => x_msg_data,
2546 p_worker_id => p_worker_id);
2547 END IF;
2548
2549 ELSIF (p_trans_object_type_id = -1004) THEN
2550 IF ( p_target_type = 'NEW_MODE_TAP' ) THEN
2551 JTF_TAE_1001_OPPOR_NM_DYN.Search_Terr_Rules(
2552 p_source_id => p_source_id ,
2553 p_trans_object_type_id => p_trans_object_type_id,
2554 x_Return_Status => x_return_status,
2555 x_Msg_Count => x_msg_count,
2556 x_Msg_Data => x_msg_data,
2557 p_worker_id => p_worker_id);
2558 ELSE
2559 JTF_TAE_1001_OPPOR_DYN.Search_Terr_Rules(
2560 p_source_id => p_source_id ,
2561 p_trans_object_type_id => p_trans_object_type_id,
2562 x_Return_Status => x_return_status,
2563 x_Msg_Count => x_msg_count,
2564 x_Msg_Data => x_msg_data,
2565 p_worker_id => p_worker_id);
2566 END IF;
2567 END IF; -- what tx type
2568 END IF; -- what source
2569 --dbms_output.put_line('GET_WINNERS_PARALLEL x_return_status of DYN PACKAGE: ' || x_return_status);
2570
2571 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2572 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
2573 p_worker_id ||
2574 '[P01] Call to ' ||
2575 'JTF_TAE_1001_..._DYN.SEARCH_TERR_RULES API has failed.';
2576 RAISE FND_API.G_EXC_ERROR;
2577 END IF;
2578
2579 -------------
2580 --- [P02] --- ANALYZE MATCHES TABLE
2581 -------------
2582 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2583 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
2584 p_worker_id ||
2585 '[P02] Call to ' ||
2586 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API BEGINS ' ||
2587 'for ' || l_matches_target);
2588 /* Analyze Matches table */
2589 /* JDOCHERT: 04/10/03: bug#2896552 */
2590 --dbms_output.put_line('GET_WINNERS_PARALLEL ANALYZING MATCHES TABLE ');
2591
2592 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2593 p_table_name => l_matches_target
2594 , p_percent => 20
2595 , x_return_status => x_return_status );
2596 --dbms_output.put_line('GET_WINNERS_PARALLEL ANALYZING MATCHES TABLE ... COMPLETE ');
2597
2598 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2599 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
2600 p_worker_id || '[P02] Call to ' ||
2601 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' ||
2602 l_matches_target;
2603 RAISE FND_API.G_EXC_ERROR;
2604 END IF;
2605
2606 -------------
2607 --- [P03] --- PARALLEL MULTI_VARIABLE NUMBER OF WINNERS PROCESSING
2608 -------------
2609
2610 --ARPATEL 09/22/03 add commit to avoid large rollback segments
2611 COMMIT;
2612
2613 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2614 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [P03] Call to ' ||
2615 'PARALLEL MULTI_VARIABLE NUMBER OF WINNERS PROCESSING BEGINS...');
2616
2617 --dbms_output.put_line('10.2: Process Level Winners ');
2618 -- 11: Process Level Winners
2619 -- NOTE: p_terr_PARENT_LEVEL_tbl arg ingnored here as we process top level
2620 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L1_target,
2621 p_terr_PARENT_LEVEL_tbl => l_terr_L1_target,
2622 p_UPPER_LEVEL_FROM_ROOT => 1,
2623 p_LOWER_LEVEL_FROM_ROOT => 1,
2624 p_matches_target => l_matches_target,
2625 p_source_id => p_source_id,
2626 p_qual_type_id => p_trans_object_type_id,
2627 x_return_status => l_return_status,
2628 p_worker_id => p_worker_id
2629 );
2630
2631 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2632 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.1] Call to ' ||
2633 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
2634 l_terr_L1_target;
2635 RAISE FND_API.G_EXC_ERROR;
2636 END IF;
2637
2638 --ARPATEL 09/22/03 add commit to avoid large rollback segments
2639 COMMIT;
2640
2641 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2642 p_table_name => l_terr_L1_target
2643 , p_percent => 20
2644 , x_return_status => x_return_status );
2645
2646 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L2_target,
2647 p_terr_PARENT_LEVEL_tbl => l_terr_L1_target,
2648 p_UPPER_LEVEL_FROM_ROOT => 1,
2649 p_LOWER_LEVEL_FROM_ROOT => 2,
2650 p_matches_target => l_matches_target,
2651 p_source_id => p_source_id,
2652 p_qual_type_id => p_trans_object_type_id,
2653 x_return_status => l_return_status,
2654 p_worker_id => p_worker_id
2655 );
2656
2657 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2658 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.2] Call to ' ||
2659 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
2660 l_terr_L2_target;
2661 RAISE FND_API.G_EXC_ERROR;
2662 END IF;
2663
2664 --ARPATEL 09/22/03 add commit to avoid large rollback segments
2665 COMMIT;
2666
2667 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2668 p_table_name => l_terr_L2_target
2669 , p_percent => 20
2670 , x_return_status => x_return_status );
2671
2672 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L3_target,
2673 p_terr_PARENT_LEVEL_tbl => l_terr_L2_target,
2674 p_UPPER_LEVEL_FROM_ROOT => 2,
2675 p_LOWER_LEVEL_FROM_ROOT => 3,
2676 p_matches_target => l_matches_target,
2677 p_source_id => p_source_id,
2678 p_qual_type_id => p_trans_object_type_id,
2679 x_return_status => l_return_status,
2680 p_worker_id => p_worker_id
2681 );
2682
2683 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2684 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [P03] Call to ' ||
2685 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
2686 l_terr_L3_target;
2687 RAISE FND_API.G_EXC_ERROR;
2688 END IF;
2689
2690 --ARPATEL 09/22/03 add commit to avoid large rollback segments
2691 COMMIT;
2692
2693 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2694 p_table_name => l_terr_L3_target
2695 , p_percent => 20
2696 , x_return_status => x_return_status );
2697
2698 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L4_target,
2699 p_terr_PARENT_LEVEL_tbl => l_terr_L3_target,
2700 p_UPPER_LEVEL_FROM_ROOT => 3,
2701 p_LOWER_LEVEL_FROM_ROOT => 4,
2702 p_matches_target => l_matches_target,
2703 p_source_id => p_source_id,
2704 p_qual_type_id => p_trans_object_type_id,
2705 x_return_status => l_return_status,
2706 p_worker_id => p_worker_id
2707 );
2708
2709 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2710 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.4] Call to ' ||
2711 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
2712 l_terr_L4_target;
2713 RAISE FND_API.G_EXC_ERROR;
2714 END IF;
2715
2716 --ARPATEL 09/22/03 add commit to avoid large rollback segments
2717 COMMIT;
2718
2719 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2720 p_table_name => l_terr_L4_target
2721 , p_percent => 20
2722 , x_return_status => x_return_status );
2723
2724 Process_Level_Winners ( p_terr_LEVEL_target_tbl => l_terr_L5_target,
2725 p_terr_PARENT_LEVEL_tbl => l_terr_L4_target,
2726 p_UPPER_LEVEL_FROM_ROOT => 4,
2727 p_LOWER_LEVEL_FROM_ROOT => 5,
2728 p_matches_target => l_matches_target,
2729 p_source_id => p_source_id,
2730 p_qual_type_id => p_trans_object_type_id,
2731 x_return_status => l_return_status,
2732 p_worker_id => p_worker_id
2733 );
2734
2735
2736 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2737 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.5] Call to ' ||
2738 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
2739 l_terr_L5_target;
2740 RAISE FND_API.G_EXC_ERROR;
2741 END IF;
2742
2743 --ARPATEL 09/22/03 add commit to avoid large rollback segments
2744 COMMIT;
2745
2746 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2747 p_table_name => l_terr_L5_target
2748 , p_percent => 20
2749 , x_return_status => x_return_status );
2750
2751 Process_Final_Level_Winners (
2752 p_terr_LEVEL_target_tbl => l_terr_WT_target,
2753 p_terr_L5_target_tbl => l_terr_L5_target,
2754 p_matches_target => l_matches_target,
2755 p_source_id => p_source_id,
2756 p_qual_type_id => p_trans_object_type_id,
2757 x_return_status => l_return_status,
2758 p_worker_id => p_worker_id
2759 );
2760
2761 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2762 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: [11.6] Call to ' ||
2763 'JTF_TAE_INDEX_CREATION_PVT.Process_Level_Winners API has failed for ' ||
2764 l_terr_WT_target;
2765 RAISE FND_API.G_EXC_ERROR;
2766 END IF;
2767
2768 --ARPATEL 09/22/03 add commit to avoid large rollback segments
2769 COMMIT;
2770
2771 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
2772 p_table_name => l_terr_WT_target
2773 , p_percent => 20
2774 , x_return_status => x_return_status );
2775
2776 -------------
2777 --- [P03] --- MULTI-LEVEL NUMBER OF WINNERS PROCESSING
2778 -------------
2779 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
2780 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
2781 p_worker_id ||
2782 '[P03] Call to ' ||
2783 'NUMBER OF WINNERS PROCESSING BEGINS...');
2784
2785 --dbms_output.put_line('12: Fetch Winners and Resources ');
2786 -- 12.0: Fetch Winners and Resources
2787
2788 /* 05/16/02: JDOCHERT:
2789 ** No longer create MATCHES INDEX, so INDEX hint is obsolete
2790 ** l_matches_idx := REPLACE(UPPER(l_matches_target), 'ES', NULL);
2791 */
2792
2793 l_dyn_str :=
2794 ' INSERT INTO ' ||
2795 l_winners_target || ' i ' ||
2796 ' ( ' ||
2797 ' TRANS_OBJECT_ID ' ||
2798 ' ,TRANS_DETAIL_OBJECT_ID ' ||
2799 ' ,WORKER_ID ' ||
2800
2801 /*
2802 ** 07/17/03 JDOCHERT: NOT USED
2803 **' ,HEADER_ID1 ' ||
2804 **' ,HEADER_ID2 ' ||
2805 */
2806
2807 ' ,SOURCE_ID ' ||
2808 ' ,TRANS_OBJECT_TYPE_ID ' ||
2809 ' ,LAST_UPDATE_DATE ' ||
2810 ' ,LAST_UPDATED_BY ' ||
2811 ' ,CREATION_DATE ' ||
2812 ' ,CREATED_BY ' ||
2813 ' ,LAST_UPDATE_LOGIN ' ||
2814 ' ,REQUEST_ID ' ||
2815 ' ,PROGRAM_APPLICATION_ID ' ||
2816 ' ,PROGRAM_ID ' ||
2817 ' ,PROGRAM_UPDATE_DATE ' ||
2818 ' ,TERR_ID ' ||
2819 ' ,ABSOLUTE_RANK ' ||
2820 ' ,TOP_LEVEL_TERR_ID ' ||
2821 ' ,RESOURCE_ID ' ||
2822 ' ,RESOURCE_TYPE ' ||
2823 ' ,GROUP_ID ' ||
2824 ' ,ROLE ' ||
2825 ' ,PRIMARY_CONTACT_FLAG ' ||
2826 ' ,PERSON_ID ' ||
2827 ' ,ORG_ID ' ||
2828 ' ,TERR_RSC_ID ' ||
2829 ' ,FULL_ACCESS_FLAG ' ||
2830 ' ) ' ||
2831 ' ( ' ||
2832
2833 --
2834 -- 10/02/02: JDOCHERT: BUG#2594526 and BUG#2602646
2835 --
2836 --' SELECT /*+ ' ||
2837 --' INDEX (jtr JTF_TERR_RSC_N1) ' ||
2838 --' INDEX (jtra JTF_TERR_RSC_ACCESS_N1) ' ||
2839 --' */ ' ||
2840 --
2841
2842 /* remove the distinct clause as suggested by appsperf : bug 4322586 */
2843 ' SELECT ' || -- DISTINCT ' ||
2844 ' WINNERS.trans_object_id ' ||
2845 ' , WINNERS.trans_detail_object_id ' ||
2846 ' , :bv_worker_id ' || --p_worker_id ||
2847 /*
2848 ** 07/17/03 JDOCHERT: NOT USED
2849 **' , 0 header_id1 ' || -- o_dttm.header_id1 ' ||
2850 **' , 0 header_id2 ' || -- o_dttm.header_id2 ' ||
2851 */
2852
2853 ' , :BV1_SOURCE_ID ' ||
2854 ' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
2855 ' , :BV1_LAST_UPDATE_DATE ' ||
2856 ' , :BV1_LAST_UPDATED_BY ' ||
2857 ' , :BV1_CREATION_DATE ' ||
2858 ' , :BV1_CREATED_BY ' ||
2859 ' , :BV1_LAST_UPDATE_LOGIN ' ||
2860 ' , :BV1_REQUEST_ID ' ||
2861 ' , :BV1_PROGRAM_APPLICATION_ID ' ||
2862 ' , :BV1_PROGRAM_ID ' ||
2863 ' , :BV1_PROGRAM_UPDATE_DATE ' ||
2864 ' , WINNERS.WIN_terr_id ' ||
2865 ' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
2866 ' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
2867 ' , jtr.resource_id ' ||
2868 ' , jtr.resource_type ' ||
2869 ' , jtr.group_id ' ||
2870 ' , jtr.role ' ||
2871 ' , jtr.primary_contact_flag ' ||
2872 ' , jtr.PERSON_ID ' ||
2873 ' , jtr.org_id ' ||
2874 ' , jtr.terr_rsc_id ' ||
2875 ' , jtr.full_access_flag ' ||
2876 ' FROM ( /* WINNERS ILV */ ' ||
2877 ' SELECT LX.trans_object_id ' ||
2878 ' , LX.trans_detail_object_id ' ||
2879 ' , LX.WIN_TERR_ID ' ||
2880 ' FROM ' || l_terr_L1_target || ' LX ' ||
2881 ' , ( SELECT trans_object_id ' ||
2882 ' , trans_detail_object_id ' ||
2883 ' , WIN_TERR_ID WIN_TERR_ID ' ||
2884 ' FROM ' || l_terr_L1_target ||
2885 ' WHERE WORKER_ID = :bv_worker_id ' ||
2886 ' MINUS ' ||
2887 ' SELECT trans_object_id ' ||
2888 ' , trans_detail_object_id ' ||
2889 ' , ul_terr_id WIN_TERR_ID ' ||
2890 ' FROM ' || l_terr_L2_target ||
2891 ' WHERE WORKER_ID = :bv_worker_id ' ||
2892 ' ) ILV ' ||
2893 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
2894 ' LX.trans_detail_object_id IS NULL ) ' ||
2895 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
2896 ' AND LX.WORKER_ID = :bv_worker_id ' ||
2897 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
2898
2899 ' UNION ALL ' ||
2900
2901 ' SELECT LX.trans_object_id ' ||
2902 ' , LX.trans_detail_object_id ' ||
2903 ' , LX.WIN_TERR_ID ' ||
2904 ' FROM ' || l_terr_L2_target || ' LX ' ||
2905 ' , ( SELECT trans_object_id ' ||
2906 ' , trans_detail_object_id ' ||
2907 ' , WIN_TERR_ID WIN_TERR_ID ' ||
2908 ' FROM ' || l_terr_L2_target ||
2909 ' WHERE WORKER_ID = :bv_worker_id ' ||
2910 ' MINUS ' ||
2911 ' SELECT trans_object_id ' ||
2912 ' , trans_detail_object_id ' ||
2913 ' , ul_terr_id WIN_TERR_ID ' ||
2914 ' FROM ' || l_terr_L3_target ||
2915 ' WHERE WORKER_ID = :bv_worker_id ' ||
2916 ' ) ILV ' ||
2917 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
2918 ' LX.trans_detail_object_id IS NULL ) ' ||
2919 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
2920 ' AND LX.WORKER_ID = :bv_worker_id ' ||
2921 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
2922
2923 ' UNION ALL ' ||
2924
2925 ' SELECT LX.trans_object_id ' ||
2926 ' , LX.trans_detail_object_id ' ||
2927 ' , LX.WIN_TERR_ID ' ||
2928 ' FROM ' || l_terr_L3_target || ' LX ' ||
2929 ' , ( SELECT trans_object_id ' ||
2930 ' , trans_detail_object_id ' ||
2931 ' , WIN_TERR_ID WIN_TERR_ID ' ||
2932 ' FROM ' || l_terr_L3_target ||
2933 ' WHERE WORKER_ID = :bv_worker_id ' ||
2934 ' MINUS ' ||
2935 ' SELECT trans_object_id ' ||
2936 ' , trans_detail_object_id ' ||
2937 ' , ul_terr_id WIN_TERR_ID ' ||
2938 ' FROM ' || l_terr_L4_target ||
2939 ' WHERE WORKER_ID = :bv_worker_id ' ||
2940 ' ) ILV ' ||
2941 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
2942 ' LX.trans_detail_object_id IS NULL ) ' ||
2943 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
2944 ' AND LX.WORKER_ID = :bv_worker_id ' ||
2945 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
2946
2947 ' UNION ALL ' ||
2948
2949 ' SELECT LX.trans_object_id ' ||
2950 ' , LX.trans_detail_object_id ' ||
2951 ' , LX.WIN_TERR_ID ' ||
2952 ' FROM ' || l_terr_L4_target || ' LX ' ||
2953 ' , ( SELECT trans_object_id ' ||
2954 ' , trans_detail_object_id ' ||
2955 ' , WIN_TERR_ID WIN_TERR_ID ' ||
2956 ' FROM ' || l_terr_L4_target ||
2957 ' WHERE WORKER_ID = :bv_worker_id ' ||
2958 ' MINUS ' ||
2959 ' SELECT trans_object_id ' ||
2960 ' , trans_detail_object_id ' ||
2961 ' , ul_terr_id WIN_TERR_ID ' ||
2962 ' FROM ' || l_terr_L5_target ||
2963 ' WHERE WORKER_ID = :bv_worker_id ' ||
2964 ' ) ILV ' ||
2965 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
2966 ' LX.trans_detail_object_id IS NULL ) ' ||
2967 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
2968 ' AND LX.WORKER_ID = :bv_worker_id ' ||
2969 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
2970
2971 ' UNION ALL ' ||
2972
2973 ' SELECT LX.trans_object_id ' ||
2974 ' , LX.trans_detail_object_id ' ||
2975 ' , LX.WIN_TERR_ID ' ||
2976 ' FROM ' || l_terr_L5_target || ' LX ' ||
2977 ' , ( SELECT trans_object_id ' ||
2978 ' , trans_detail_object_id ' ||
2979 ' , WIN_TERR_ID WIN_TERR_ID ' ||
2980 ' FROM ' || l_terr_L5_target ||
2981 ' WHERE WORKER_ID = :bv_worker_id ' ||
2982 ' MINUS ' ||
2983 ' SELECT trans_object_id ' ||
2984 ' , trans_detail_object_id ' ||
2985 ' , ul_terr_id WIN_TERR_ID ' ||
2986 ' FROM ' || l_terr_WT_target ||
2987 ' WHERE WORKER_ID = :bv_worker_id ' ||
2988 ' ) ILV ' ||
2989 ' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
2990 ' LX.trans_detail_object_id IS NULL ) ' ||
2991 ' AND LX.trans_object_id = ILV.trans_object_id ' ||
2992 ' AND LX.WORKER_ID = :bv_worker_id ' ||
2993 ' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
2994
2995 ' UNION ALL ' ||
2996
2997 ' SELECT trans_object_id ' ||
2998 ' , trans_detail_object_id ' ||
2999 ' , WIN_TERR_ID ' ||
3000 ' FROM ' || l_terr_WT_target ||
3001 ' WHERE WORKER_ID = :bv_worker_id ' ||
3002 ' ) WINNERS ' ||
3003 ' , jtf_terr_rsc_all jtr ' ||
3004 ' , jtf_terr_rsc_access_all jtra ' ||
3005 ' WHERE WINNERS.WIN_terr_id = jtr.terr_id ' ||
3006 ' AND ( ( jtr.end_date_active IS NULL OR jtr.end_date_active >= :BV1_SYSDATE ) AND ' ||
3007 ' ( jtr.start_date_active IS NULL OR jtr.start_date_active <= :BV2_SYSDATE ) ' ||
3008 ' ) ' ||
3009 ' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
3010 ' AND jtra.access_type = ' || l_access_list ||
3011 ' ) ';
3012
3013
3014 BEGIN
3015
3016 EXECUTE IMMEDIATE l_dyn_str USING
3017 p_worker_id /* :bv_worker_id */
3018 , p_source_id /* :BV1_SOURCE_ID */
3019 , p_trans_object_type_id /* :BV1_TRANS_OBJECT_TYPE_ID */
3020 , lp_sysdate /* :BV1_LAST_UPDATE_DATE */
3021 , G_USER_ID /* :BV1_LAST_UPDATED_BY */
3022 , lp_sysdate /* :BV1_CREATION_DATE */
3023 , G_USER_ID /* :BV1_CREATED_BY */
3024 , G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
3025 , p_request_id /* :BV1_REQUEST_ID */
3026 , G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
3027 , G_PROGRAM_ID /* :BV1_PROGRAM_ID */
3028 , lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
3029 , p_worker_id /* :bv_worker_id */ --1
3030 , p_worker_id /* :bv_worker_id */
3031 , p_worker_id /* :bv_worker_id */
3032 , p_worker_id /* :bv_worker_id */
3033 , p_worker_id /* :bv_worker_id */ --5
3034 , p_worker_id /* :bv_worker_id */
3035 , p_worker_id /* :bv_worker_id */
3036 , p_worker_id /* :bv_worker_id */
3037 , p_worker_id /* :bv_worker_id */
3038 , p_worker_id /* :bv_worker_id */ --10
3039 , p_worker_id /* :bv_worker_id */
3040 , p_worker_id /* :bv_worker_id */
3041 , p_worker_id /* :bv_worker_id */
3042 , p_worker_id /* :bv_worker_id */
3043 , p_worker_id /* :bv_worker_id */ --15
3044 , p_worker_id /* :bv_worker_id */
3045 , lp_sysdate /* :BV1_SYSDATE */
3046 , lp_sysdate; /* :BV2_SYSDATE */
3047
3048 COMMIT; -- after modifying table in parallel
3049
3050 EXCEPTION
3051 WHEN NO_DATA_FOUND THEN
3052 NULL;
3053 END;
3054
3055 x_return_status := FND_API.G_RET_STS_SUCCESS;
3056
3057 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
3058 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
3059 p_worker_id ||
3060 'Number of records inserted into ' || l_winners_target ||
3061 ' = ' || SQL%ROWCOUNT );
3062 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
3063 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: worker_id= ' ||
3064 p_worker_id || '[P03] Call to ' ||
3065 'NUMBER OF WINNERS PROCESSING COMPLETE.');
3066
3067
3068 -------------
3069 --- [P04] --- ANALYZE WINNERS TABLE
3070 -------------
3071 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
3072 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: [P05] Call to ' ||
3073 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API BEGINS ' ||
3074 'for ' || l_winners_target);
3075
3076 /* Analyze Winners table */
3077 /* JDOCHERT: 04/10/03: bug#2896552 */
3078 JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
3079 p_table_name => l_winners_target
3080 , p_percent => 20
3081 , x_return_status => x_return_status );
3082
3083 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3084 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: [P05] Call to ' ||
3085 'JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX API has failed for ' ||
3086 l_winners_target;
3087 RAISE FND_API.G_EXC_ERROR;
3088 END IF;
3089
3090 --------------------------------
3091 -- END API
3092 --------------------------------
3093 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
3094 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, '/***************** END: TERRITORY ASSIGNMENT STATUS *********************/');
3095 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
3096
3097 /* Program completed successfully */
3098 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: Successfully completed.';
3099 x_return_status := FND_API.G_RET_STS_SUCCESS;
3100 RETCODE := 0;
3101
3102 EXCEPTION
3103
3104 WHEN NO_TAE_DATA_FOUND THEN
3105 x_return_status := FND_API.G_RET_STS_SUCCESS;
3106 RETCODE := 1;
3107 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3108
3109 WHEN FND_API.G_EXC_ERROR THEN
3110 x_return_status := FND_API.G_RET_STS_ERROR;
3111 RETCODE := 2;
3112 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3113
3114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3116 RETCODE := 2;
3117 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3118
3119 WHEN utl_file.invalid_path OR
3120 utl_file.invalid_mode OR
3121 utl_file.invalid_filehandle OR
3122 utl_file.invalid_operation OR
3123 utl_file.write_error THEN
3124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3125 RETCODE := 2;
3126 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: [END] UTL_FILE: ' ||
3127 SQLERRM;
3128 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3129
3130 WHEN OTHERS THEN
3131
3132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3133 RETCODE := 2;
3134 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS_PARALLEL: [END] OTHERS: ' ||
3135 SQLERRM;
3136
3137 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3138
3139 END GET_WINNERS_PARALLEL;
3140
3141 -- ***************************************************
3142 -- API Specifications
3143 -- ***************************************************
3144 PROCEDURE Process_Level_Winners (
3145 p_terr_LEVEL_target_tbl IN VARCHAR2,
3146 p_terr_PARENT_LEVEL_tbl IN VARCHAR2,
3147 p_UPPER_LEVEL_FROM_ROOT IN NUMBER,
3148 p_LOWER_LEVEL_FROM_ROOT IN NUMBER,
3149 p_matches_target IN VARCHAR2,
3150 p_source_id IN NUMBER,
3151 p_qual_type_id IN NUMBER,
3152 x_return_status OUT NOCOPY VARCHAR2,
3153 p_worker_id IN NUMBER := 1
3154 )
3155 AS
3156
3157 l_dyn_str VARCHAR2(32767);
3158 errbuf VARCHAR2(2000);
3159 l_qual_type_id NUMBER;
3160
3161 l_level_num_rows NUMBER;
3162 l_matches_num_rows NUMBER;
3163 l_denorm_num_rows NUMBER;
3164
3165 BEGIN
3166
3167 /* ARPATEL: 12/09/2003, For Oracle Sales denorm records are no longer striped by transaction type */
3168 IF p_source_id = -1001
3169 THEN
3170 l_qual_type_id := -1;
3171 ELSE
3172 l_qual_type_id := p_qual_type_id;
3173 END IF;
3174
3175 l_level_num_rows := 0;
3176 l_matches_num_rows := 0;
3177 l_denorm_num_rows := 0;
3178
3179 IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 1) THEN
3180
3181 l_dyn_str := ' ' ||
3182 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
3183 ' ( ' ||
3184 ' trans_object_id ' ||
3185 ' , trans_detail_object_id ' ||
3186 ' , WIN_TERR_ID ' ||
3187 ' , UL_TERR_ID ' ||
3188 ' , LL_TERR_ID ' ||
3189 ' , LL_NUM_WINNERS ' ||
3190 ' , WORKER_ID ' ||
3191 ' ) ' ||
3192 ' ( SELECT ' ||
3193 ' TL.trans_object_id ' ||
3194 ' , TL.trans_detail_object_id ' ||
3195 ' , TL.CL_WIN_TERR_ID ' ||
3196 ' , TL.UL_terr_id ' ||
3197 ' , TL.LL_terr_id ' ||
3198 ' , TL.LL_num_winners ' ||
3199 ' , :B_WORKER_ID ' || --p_worker_id || bug#3391453
3200 ' FROM ( ' ||
3201 ' SELECT ';
3202
3203 /* JDOCHERT: 10/26/03: bug#3209968
3204 ** LEADING hint helps batch TAE as it makes
3205 ** the CBO drive from the JTF_TERR_DENORM_RULES_ALL (JTDR)
3206 ** table since there will be many more records in the TAE
3207 ** tables than JTDR. However, this causes very bad performance
3208 ** in the rea-time API so we switch it off there
3209 */
3210 IF (p_matches_target LIKE 'JTF_TAE%') THEN
3211
3212 /* Batch TAE */
3213 --l_dyn_str := l_dyn_str || ' /*+ LEADING(LL) */ '; Bug#3373462
3214 l_dyn_str := l_dyn_str || ' /*+ FULL(M) */ ';
3215 NULL;
3216
3217 ELSE
3218
3219 /* Real-time TAE */
3220 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
3221
3222 END IF;
3223
3224 l_dyn_str := l_dyn_str ||
3225 ' DISTINCT ' ||
3226 ' m.trans_object_id ' ||
3227 ' , m.trans_detail_object_id ' ||
3228 ' , LL.RELATED_TERR_ID CL_WIN_TERR_ID ' ||
3229 ' , UL.related_terr_id UL_TERR_ID ' ||
3230
3231 /* JDOCHERT: 10/26/03: bug#3207099
3232 ** Following NVL supports backward compatibilty of
3233 ** Sales territories where the number of winners
3234 ** has not been set at the Top-Level Territory
3235 */
3236 ' , NVL(UL.num_winners, 1) UL_NUM_WINNERS ' ||
3237 ' , LL.related_terr_id LL_TERR_ID ' ||
3238
3239 /* JDOCHERT: 10/26/03: bug#3207099
3240 ** Following NVL supports backward compatibilty of
3241 ** Sales territories where the number of winners
3242 ** has not been set at the Top-Level Territory
3243 */
3244 ' , NVL(LL.num_winners, 1) LL_NUM_WINNERS ' ||
3245
3246 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
3247 ' m.trans_object_id ' ||
3248 ' , m.trans_detail_object_id ' ||
3249 ' , UL.related_terr_id ' ||
3250 ' ORDER BY LL.absolute_rank DESC ' ||
3251 ' , LL.related_terr_id ) ' ||
3252 ' AS LL_TERR_RANK ' ||
3253 ' FROM ' || p_matches_target || ' M ' ||
3254 ' , jtf_terr_denorm_rules_all UL ' ||
3255 ' , jtf_terr_denorm_rules_all LL ' ||
3256 ' WHERE UL.level_from_root = :b1_UPPER_LEVEL +1 ' || /* UPPER level territory */
3257 ' AND UL.source_id = :b1_source_id ' ||
3258 ' AND UL.qual_type_id = :b1_qual_type_id ' ||
3259 ' AND UL.terr_id = M.TERR_ID ' ||
3260 ' AND LL.level_from_root = :b1_LOWER_LEVEL +1 ' || /* LOWER level territory */
3261 ' AND LL.source_id = :b2_source_id ' ||
3262 ' AND LL.qual_type_id = :b2_qual_type_id ' ||
3263 ' AND LL.terr_id = M.TERR_ID ' ||
3264
3265 /* BEGIN PARALLEL WORKERS SUPPORT */
3266 /* JDOCHERT: 09/21/03 */
3267 ' AND M.worker_id = :B_WORKER_ID ' ||
3268 /* END PARALLEL WORKERS SUPPORT */
3269
3270 ' ) TL ' ||
3271 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
3272 ' ) ';
3273
3274 BEGIN
3275
3276 EXECUTE IMMEDIATE l_dyn_str USING
3277 p_worker_id
3278 , p_UPPER_LEVEL_FROM_ROOT
3279 , p_source_id
3280 , l_qual_type_id
3281 , p_LOWER_LEVEL_FROM_ROOT
3282 , p_source_id
3283 , l_qual_type_id
3284 , p_worker_id;
3285
3286 EXCEPTION
3287 WHEN NO_DATA_FOUND THEN
3288 NULL;
3289 END;
3290
3291 ELSE
3292
3293 /* for batch TAE get the number of rows for matches , level and denorm tables */
3294 /* so that the winning sql can have the appropiate ordered hint */
3295 IF (p_matches_target LIKE 'JTF_TAE%') THEN
3296
3297 GET_NUM_ROWS( p_table_name => p_matches_target
3298 , x_num_rows => l_matches_num_rows
3299 , x_return_status => x_return_status );
3300
3301 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3302 RAISE FND_API.G_EXC_ERROR;
3303 END IF;
3304
3305 GET_NUM_ROWS( p_table_name => p_terr_PARENT_LEVEL_tbl
3306 , x_num_rows => l_level_num_rows
3307 , x_return_status => x_return_status );
3308
3309 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3310 RAISE FND_API.G_EXC_ERROR;
3311 END IF;
3312
3313 GET_NUM_ROWS( p_table_name => 'JTF_TERR_DENORM_RULES_ALL'
3314 , x_num_rows => l_denorm_num_rows
3315 , x_return_status => x_return_status );
3316
3317 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3318 RAISE FND_API.G_EXC_ERROR;
3319 END IF;
3320
3321 END IF;
3322
3323
3324 l_dyn_str := ' ' ||
3325 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
3326 ' ( ' ||
3327 ' trans_object_id ' ||
3328 ' , trans_detail_object_id ' ||
3329 ' , WIN_TERR_ID ' ||
3330 ' , UL_TERR_ID ' ||
3331 ' , LL_TERR_ID ' ||
3332 ' , LL_NUM_WINNERS ' ||
3333 ' , WORKER_ID ' ||
3334 ' ) ' ||
3335 ' ( SELECT ' ||
3336 ' TL.trans_object_id ' ||
3337 ' , TL.trans_detail_object_id ' ||
3338 ' , TL.CL_WIN_TERR_ID ' ||
3339 ' , TL.UL_terr_id ' ||
3340 ' , TL.LL_terr_id ' ||
3341 ' , TL.LL_num_winners ' ||
3342 ' , :B_WORKER_ID ' || --p_worker_id || bug#3391453
3343 ' FROM ( ' || /* NL */
3344 ' SELECT ' ||
3345 ' CL.trans_object_id ' ||
3346 ' , CL.trans_detail_object_id ' ||
3347 ' , CL.CL_WIN_TERR_ID ' ||
3348 --' , CL.CL_ABS_RANK ' ||
3349 ' , CL.UL_terr_id ';
3350
3351 /* JDOCHERT: 10/26/03: bug#3207099
3352 ** Following supports backward compatibilty of Sales territories
3353 ** number of winners has not been set at the Top-Level Territory
3354 */
3355 IF ( p_UPPER_LEVEL_FROM_ROOT = 1 AND p_LOWER_LEVEL_FROM_ROOT = 2) THEN
3356
3357 l_dyn_str := l_dyn_str ||
3358 ' , NVL(CL.UL_NUM_WINNERS, 1) UL_NUM_WINNERS ';
3359
3360 ELSE
3361
3362 l_dyn_str := l_dyn_str ||
3363 ' , CL.UL_NUM_WINNERS UL_NUM_WINNERS ';
3364
3365 END IF;
3366
3367 l_dyn_str := l_dyn_str ||
3368 ' , CL.LL_TERR_ID ' ||
3369 ' , CL.LL_NUM_WINNERS ' ||
3370 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
3371 ' CL.trans_object_id ' ||
3372 ' , CL.trans_detail_object_id ' ||
3373 ' , CL.UL_TERR_ID ' ||
3374 ' ORDER BY ' ||
3375 --' CL.CL_ABS_RANK DESC ' ||
3376 ' CL.M_ABS_RANK DESC ' ||
3377 ' , CL.CL_WIN_TERR_ID ) ' ||
3378 ' AS LL_TERR_RANK ' || /* CL */
3379 ' FROM ( ' ||
3380 ' SELECT ';
3381
3382 /* JDOCHERT: 10/26/03: bug#3209968
3383 ** LEADING hint helps batch TAE as it makes
3384 ** the CBO drive from the JTF_TERR_DENORM_RULES_ALL (JTDR)
3385 ** table since there will be many more records in the TAE
3386 ** tables than JTDR. However, this causes very bad performance
3387 ** in the rea-time API so we switch it off there
3388 */
3389 IF (p_matches_target LIKE 'JTF_TAE%') THEN
3390
3391 /* Batch TAE */
3392 IF (p_qual_type_id = -1002) THEN
3393 l_dyn_str := l_dyn_str || ' /*+ USE_HASH(ML) USE_HASH(LL) USE_HASH(UL) USE_HASH(M) ORDERED */ ';
3394 ELSE
3395 l_dyn_str := l_dyn_str || ' /*+ ORDERED USE_HASH(ML) USE_HASH(M) USE_HASH(UL) USE_HASH(LL) */ ';
3396 END IF;
3397 ELSE
3398
3399 /* Real-time TAE */
3400 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
3401
3402 END IF;
3403
3404 l_dyn_str := l_dyn_str ||
3405 --' DISTINCT ' ||
3406 ' m.trans_object_id ' ||
3407 ' , m.trans_detail_object_id ' ||
3408 --' , m.terr_id M_WIN_TERR_ID ' ||
3409 ' , LL.related_terr_id CL_WIN_TERR_ID ' ||
3410 --' , XX.absolute_rank CL_ABS_RANK ' ||
3411 ' , UL.related_terr_id UL_TERR_ID ' ||
3412 ' , UL.num_winners UL_NUM_WINNERS ' ||
3413 ' , LL.related_terr_id LL_TERR_ID ' ||
3414 ' , LL.num_winners LL_NUM_WINNERS ' ||
3415 ' , max(m.absolute_rank) M_ABS_RANK ' ||
3416 ' FROM ';
3417
3418 IF (p_matches_target LIKE 'JTF_TAE%') THEN
3419 IF (((l_level_num_rows <= l_matches_num_rows) AND (l_matches_num_rows <= l_denorm_num_rows)) OR
3420 ((l_level_num_rows <= l_denorm_num_rows) AND (l_denorm_num_rows <= l_matches_num_rows))) THEN
3421 l_dyn_str := l_dyn_str ||
3422 ' ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
3423 ' , ' || p_matches_target || ' M ' ||
3424 ' , jtf_terr_denorm_rules_all UL ' ||
3425 ' , jtf_terr_denorm_rules_all LL ';
3426 ELSIF (((l_matches_num_rows <= l_level_num_rows) AND (l_level_num_rows <= l_denorm_num_rows)) OR
3427 ((l_matches_num_rows <= l_denorm_num_rows) AND (l_denorm_num_rows <= l_level_num_rows))) THEN
3428 l_dyn_str := l_dyn_str ||
3429 ' ' || p_matches_target || ' M ' ||
3430 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
3431 ' , jtf_terr_denorm_rules_all UL ' ||
3432 ' , jtf_terr_denorm_rules_all LL ';
3433 ELSIF (((l_denorm_num_rows <= l_level_num_rows) AND (l_level_num_rows <= l_matches_num_rows)) OR
3434 ((l_denorm_num_rows <= l_matches_num_rows) AND (l_matches_num_rows <= l_level_num_rows))) THEN
3435 l_dyn_str := l_dyn_str ||
3436 ' jtf_terr_denorm_rules_all UL ' ||
3437 ' , ' || p_matches_target || ' M ' ||
3438 ' , jtf_terr_denorm_rules_all LL ' ||
3439 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ';
3440 END IF;
3441 ELSE
3442 l_dyn_str := l_dyn_str ||
3443 ' jtf_terr_denorm_rules_all LL ' ||
3444 ' , ' || p_matches_target || ' M ' ||
3445 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
3446 ' , jtf_terr_denorm_rules_all UL ';
3447 END IF;
3448 l_dyn_str := l_dyn_str ||
3449 ' WHERE UL.level_from_root = :b1_UPPER_LEVEL +1 ' || /* UPPER level territory */
3450 ' AND UL.source_id = :b2_source_id ' ||
3451 ' AND UL.qual_type_id = :b3_qual_type_id ' ||
3452 ' AND UL.terr_id = M.TERR_ID ' ||
3453 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
3454 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
3455 ' M.trans_detail_object_id IS NULL ) ' ||
3456 ' AND M.trans_object_id = ML.trans_object_id ' ||
3457
3458 /* BEGIN PARALLEL WORKERS SUPPORT */
3459 /* JDOCHERT: 09/21/03 */
3460 ' AND M.worker_id = ML.WORKER_ID' ||
3461 ' AND M.worker_id = :B_WORKER_ID ' ||
3462 ' AND ML.worker_id = :B_WORKER_ID ' ||
3463 /* END PARALLEL WORKERS SUPPORT */
3464
3465 ' AND LL.level_from_root = :b4_LOWER_LEVEL +1 ' || /* LOWER level territory */
3466 ' AND LL.source_id = :b5_source_id ' ||
3467 ' AND LL.qual_type_id = :b6_qual_type_id ' ||
3468 --' AND XX.terr_id = LL.related_terr_id ' ||
3469 --' AND XX.related_terr_id = LL.related_terr_id ' ||
3470 --' AND XX.source_id = :b7_source_id ' ||
3471 --' AND XX.qual_type_id = :b8_qual_type_id ' ||
3472 ' AND ML.LL_NUM_WINNERS IS NOT NULL ' ||
3473 ' AND LL.NUM_WINNERS IS NOT NULL ' ||
3474 ' AND LL.terr_id = M.TERR_ID ' ||
3475 ' GROUP BY ' ||
3476 ' m.trans_object_id ' ||
3477 ' , m.trans_detail_object_id ' ||
3478 ' , LL.related_terr_id ' ||
3479 ' , UL.related_terr_id ' ||
3480 ' , UL.num_winners ' ||
3481 ' , LL.related_terr_id ' ||
3482 ' , LL.num_winners ' ||
3483
3484 ' UNION ALL ' ||
3485
3486 ' SELECT ';
3487
3488 /* JDOCHERT: 10/26/03: bug#3209968
3489 ** LEADING hint helps batch TAE as it makes
3490 ** the CBO drive from the JTF_TERR_DENORM_RULES_ALL (JTDR)
3491 ** table since there will be many more records in the TAE
3492 ** tables than JTDR. However, this causes very bad performance
3493 ** in the rea-time API so we switch it off there
3494 */
3495 IF (p_matches_target LIKE 'JTF_TAE%') THEN
3496
3497 /* Batch TAE */
3498 l_dyn_str := l_dyn_str || ' /*+ ORDERED USE_HASH(ML) USE_HASH(M) USE_HASH(UL) USE_HASH(LL) */ ';
3499
3500 ELSE
3501
3502 /* Real-time TAE */
3503 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
3504
3505 END IF;
3506
3507 l_dyn_str := l_dyn_str ||
3508 ' DISTINCT ' ||
3509 ' m.trans_object_id ' ||
3510 ' , m.trans_detail_object_id ' ||
3511 --' , m.terr_id M_WIN_TERR_ID ' ||
3512 ' , m.terr_id CL_WIN_TERR_ID ' ||
3513 --' , m.absolute_rank CL_ABS_RANK ' ||
3514 ' , UL.related_terr_id UL_TERR_ID ' ||
3515 ' , UL.num_winners UL_NUM_WINNERS ' ||
3516 ' , LL.related_terr_id LL_TERR_ID ' ||
3517 ' , LL.num_winners LL_NUM_WINNERS ' ||
3518 ' , m.absolute_rank M_ABS_RANK ' ||
3519 ' FROM ';
3520
3521 IF (p_matches_target LIKE 'JTF_TAE%') THEN
3522 IF (((l_level_num_rows <= l_matches_num_rows) AND (l_matches_num_rows <= l_denorm_num_rows)) OR
3523 ((l_level_num_rows <= l_denorm_num_rows) AND (l_denorm_num_rows <= l_matches_num_rows))) THEN
3524 l_dyn_str := l_dyn_str ||
3525 ' ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
3526 ' , ' || p_matches_target || ' M ' ||
3527 ' , jtf_terr_denorm_rules_all UL ' ||
3528 ' , jtf_terr_denorm_rules_all LL ';
3529 ELSIF (((l_matches_num_rows <= l_level_num_rows) AND (l_level_num_rows <= l_denorm_num_rows)) OR
3530 ((l_matches_num_rows <= l_denorm_num_rows) AND (l_denorm_num_rows <= l_level_num_rows))) THEN
3531 l_dyn_str := l_dyn_str ||
3532 ' ' || p_matches_target || ' M ' ||
3533 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
3534 ' , jtf_terr_denorm_rules_all UL ' ||
3535 ' , jtf_terr_denorm_rules_all LL ';
3536 ELSIF (((l_denorm_num_rows <= l_level_num_rows) AND (l_level_num_rows <= l_matches_num_rows)) OR
3537 ((l_denorm_num_rows <= l_matches_num_rows) AND (l_matches_num_rows <= l_level_num_rows))) THEN
3538 l_dyn_str := l_dyn_str ||
3539 ' jtf_terr_denorm_rules_all UL ' ||
3540 ' , ' || p_matches_target || ' M ' ||
3541 ' , jtf_terr_denorm_rules_all LL ' ||
3542 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ';
3543 END IF;
3544 ELSE
3545 l_dyn_str := l_dyn_str ||
3546 ' jtf_terr_denorm_rules_all LL ' ||
3547 ' , ' || p_matches_target || ' M ' ||
3548 ' , ' || p_terr_PARENT_LEVEL_tbl || ' ML ' ||
3549 ' , jtf_terr_denorm_rules_all UL ';
3550 END IF;
3551
3552 l_dyn_str := l_dyn_str ||
3553 ' WHERE UL.level_from_root = :b9_UPPER_LEVEL +1 ' || /* UPPER level territory */
3554 ' AND UL.source_id = :b10_source_id ' ||
3555 ' AND UL.qual_type_id = :b11_qual_type_id ' ||
3556 ' AND UL.terr_id = M.TERR_ID ' ||
3557 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
3558 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
3559 ' M.trans_detail_object_id IS NULL ) ' ||
3560 ' AND M.trans_object_id = ML.trans_object_id ' ||
3561
3562 /* BEGIN PARALLEL WORKERS SUPPORT */
3563 /* JDOCHERT: 09/21/03 */
3564 ' AND M.worker_id = ML.WORKER_ID' ||
3565 ' AND M.worker_id = :B_WORKER_ID ' ||
3566 ' AND ML.worker_id = :B_WORKER_ID ' ||
3567 /* END PARALLEL WORKERS SUPPORT */
3568
3569 ' AND LL.level_from_root = :b12_LOWER_LEVEL +1 ' || /* LOWER level territory */
3570 ' AND LL.source_id = :b13_source_id ' ||
3571 ' AND LL.qual_type_id = :b14_qual_type_id ' ||
3572
3573 ' AND ML.LL_NUM_WINNERS IS NOT NULL ' ||
3574 ' AND LL.NUM_WINNERS IS NULL ' ||
3575
3576 ' AND LL.terr_id = M.TERR_ID ' ||
3577
3578 ' ) CL ' ||
3579 ' ) TL ' ||
3580 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
3581 ' ) ';
3582
3583 BEGIN
3584
3585 EXECUTE IMMEDIATE l_dyn_str USING
3586 p_worker_id
3587 , p_UPPER_LEVEL_FROM_ROOT
3588 , p_source_id
3589 , l_qual_type_id
3590 , p_worker_id
3591 , p_worker_id
3592 , p_LOWER_LEVEL_FROM_ROOT
3593 , p_source_id
3594 , l_qual_type_id
3595 , p_UPPER_LEVEL_FROM_ROOT
3596 , p_source_id
3597 , l_qual_type_id
3598 , p_worker_id
3599 , p_worker_id
3600 , p_LOWER_LEVEL_FROM_ROOT
3601 , p_source_id
3602 , l_qual_type_id ;
3603
3604 EXCEPTION
3605 WHEN NO_DATA_FOUND THEN
3606 NULL;
3607 END;
3608
3609 END IF;
3610
3611 /* Program completed successfully */
3612 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: ' ||
3613 'UPPER LEVEL = ' || p_UPPER_LEVEL_FROM_ROOT || ' ' ||
3614 'Successfully completed.';
3615 x_return_status := FND_API.G_RET_STS_SUCCESS;
3616
3617 EXCEPTION
3618
3619 WHEN FND_API.G_EXC_ERROR THEN
3620
3621 x_return_status := FND_API.G_RET_STS_ERROR;
3622 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: ' ||
3623 '[END] FND_API.G_RET_STS_ERROR: ' ||
3624 'UPPER LEVEL = ' || p_UPPER_LEVEL_FROM_ROOT || ' ' ||
3625 'LOWER LEVEL = ' || p_LOWER_LEVEL_FROM_ROOT || ' :' ||
3626 SQLERRM;
3627 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3628
3629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3630
3631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3632 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Level_Winners: ' ||
3633 '[END] FND_API.G_EXC_UNEXPECTED_ERROR: ' ||
3634 'UPPER LEVEL = ' || p_UPPER_LEVEL_FROM_ROOT || ' ' ||
3635 'LOWER LEVEL = ' || p_LOWER_LEVEL_FROM_ROOT || ' :' ||
3636 SQLERRM;
3637 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3638
3639 WHEN utl_file.invalid_path OR
3640 utl_file.invalid_mode OR
3641 utl_file.invalid_filehandle OR
3642 utl_file.invalid_operation OR
3643 utl_file.write_error THEN
3644
3645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3646 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [END] UTL_FILE: ' ||
3647 'UPPER LEVEL = ' || p_UPPER_LEVEL_FROM_ROOT || ' ' ||
3648 'LOWER LEVEL = ' || p_LOWER_LEVEL_FROM_ROOT || ' :' ||
3649 SQLERRM;
3650 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3651
3652 WHEN OTHERS THEN
3653
3654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3655 ERRBUF := 'JTF_TAE_ASSIGN_PUB.GET_WINNERS: [END] OTHERS: ' ||
3656 'UPPER LEVEL = ' || p_UPPER_LEVEL_FROM_ROOT || ' ' ||
3657 'LOWER LEVEL = ' || p_LOWER_LEVEL_FROM_ROOT || ' :' ||
3658 SQLERRM;
3659
3660 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3661
3662 END Process_Level_Winners;
3663
3664
3665
3666 -- ***************************************************
3667 -- API Specifications
3668 -- ***************************************************
3669 PROCEDURE Process_Final_Level_Winners (
3670 p_terr_LEVEL_target_tbl IN VARCHAR2,
3671 p_terr_L5_target_tbl IN VARCHAR2,
3672 p_matches_target IN VARCHAR2,
3673 p_source_id IN NUMBER,
3674 p_qual_type_id IN NUMBER,
3675 x_return_status OUT NOCOPY VARCHAR2,
3676 p_worker_id IN NUMBER := 1
3677 )
3678 AS
3679
3680 l_dyn_str VARCHAR2(32767);
3681 errbuf VARCHAR2(2000);
3682 l_qual_type_id NUMBER;
3683
3684 BEGIN
3685
3686 /* ARPATEL: 12/09/2003, For Oracle Sales denorm records are no longer striped by transaction type */
3687 IF p_source_id = -1001
3688 THEN
3689 l_qual_type_id := -1;
3690 ELSE
3691 l_qual_type_id := p_qual_type_id;
3692 END IF;
3693
3694 l_dyn_str := ' ' ||
3695 'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
3696 ' ( ' ||
3697 ' trans_object_id ' ||
3698 ' , trans_detail_object_id ' ||
3699 ' , WIN_TERR_ID ' ||
3700 ' , UL_TERR_ID ' ||
3701 ' , LL_TERR_ID ' ||
3702 ' , worker_id ' ||
3703 ' ) ' ||
3704 ' ( SELECT ' ||
3705 ' TL.trans_object_id ' ||
3706 ' , TL.trans_detail_object_id ' ||
3707 ' , TL.WIN_TERR_ID ' ||
3708 ' , TL.UL_terr_id ' ||
3709 ' , TL.terr_id ' ||
3710 ' , :B_WORKER_ID ' || --p_worker_id ||
3711 ' FROM ( ' ||
3712 ' SELECT ';
3713
3714 /* JDOCHERT: 10/26/03: bug#3209968
3715 ** LEADING hint helps batch TAE as it makes
3716 ** the CBO drive from the JTF_TERR_DENORM_RULES_ALL (JTDR)
3717 ** table since there will be many more records in the TAE
3718 ** tables than JTDR. However, this causes very bad performance
3719 ** in the rea-time API so we switch it off there
3720 */
3721 IF (p_matches_target LIKE 'JTF_TAE%') THEN
3722
3723 /* Batch TAE */
3724 --l_dyn_str := l_dyn_str || ' /*+ LEADING(LL) */ ';
3725 NULL;
3726
3727 ELSE
3728
3729 /* Real-time TAE */
3730 l_dyn_str := l_dyn_str || ' /*+ LEADING(M) */ ';
3731
3732 END IF;
3733
3734 l_dyn_str := l_dyn_str ||
3735 ' DISTINCT ' ||
3736 ' m.trans_object_id ' ||
3737 ' , m.trans_detail_object_id ' ||
3738 ' , M.TERR_ID WIN_TERR_ID ' ||
3739 ' , UL.related_terr_id UL_TERR_ID ' ||
3740 ' , UL.num_winners UL_NUM_WINNERS ' ||
3741 ' , M.terr_id TERR_ID ' ||
3742 ' , DENSE_RANK() OVER ( PARTITION BY ' ||
3743 ' m.trans_object_id ' ||
3744 ' , m.trans_detail_object_id ' ||
3745 ' , UL.related_terr_id ' ||
3746 ' ORDER BY M.absolute_rank DESC, M.TERR_ID ) AS LL_TERR_RANK ' ||
3747 ' FROM ' || p_matches_target || ' M ' ||
3748 ' , jtf_terr_denorm_rules_all UL ' ||
3749 ' , ' || p_terr_L5_target_tbl || ' ML ' || /* FINAL LEVEL TABLE */
3750 ' , jtf_terr_all jt ' ||
3751 ' , jtf_terr_denorm_rules_all LL ' ||
3752 ' WHERE UL.level_from_root = 6 ' || /* UPPER level */
3753 ' AND UL.source_id = :b1_source_id ' ||
3754 ' AND UL.qual_type_id = :b1_qual_type_id ' ||
3755 ' AND UL.terr_id = M.TERR_ID ' ||
3756 ' AND UL.related_terr_id = ML.LL_terr_id ' ||
3757 ' AND ( M.trans_detail_object_id = ML.trans_detail_object_id OR ' ||
3758 ' M.trans_detail_object_id IS NULL ) ' ||
3759 ' AND M.trans_object_id = ML.trans_object_id ' ||
3760
3761 /* BEGIN PARALLEL WORKERS SUPPORT */
3762 /* JDOCHERT: 09/21/03 */
3763 ' AND M.worker_id = ML.WORKER_ID' ||
3764 ' AND M.worker_id = :B_WORKER_ID ' ||
3765 ' AND ML.worker_id = :B_WORKER_ID ' ||
3766 /* END PARALLEL WORKERS SUPPORT */
3767
3768 ' AND jt.terr_id = LL.related_terr_id ' ||
3769 ' AND LL.level_from_root >= 6 ' || /* FINAL LEVEL(S) */
3770 ' AND LL.source_id = :b2_source_id ' ||
3771 ' AND LL.qual_type_id = :b2_qual_type_id ' ||
3772 ' AND LL.terr_id = M.TERR_ID ' ||
3773 ' ) TL ' ||
3774 ' WHERE TL.LL_TERR_RANK <= TL.UL_num_winners ' ||
3775 ' ) ';
3776
3777
3778 BEGIN
3779
3780
3781 EXECUTE IMMEDIATE l_dyn_str USING
3782 p_worker_id
3783 , p_source_id
3784 , l_qual_type_id
3785 , p_worker_id
3786 , p_worker_id
3787 , p_source_id
3788 , l_qual_type_id ;
3789
3790 EXCEPTION
3791 WHEN NO_DATA_FOUND THEN
3792 NULL;
3793
3794 END;
3795
3796 /* Program completed successfully */
3797 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Final_Level_Winners: ' ||
3798 'Successfully completed.';
3799 x_return_status := FND_API.G_RET_STS_SUCCESS;
3800
3801 EXCEPTION
3802
3803 WHEN FND_API.G_EXC_ERROR THEN
3804
3805 x_return_status := FND_API.G_RET_STS_ERROR;
3806 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Final_Level_Winners: ' ||
3807 '[END] FND_API.G_RET_STS_ERROR: ' ||
3808 SQLERRM;
3809 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3810
3811 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3812
3813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Final_Level_Winners: ' ||
3815 '[END] FND_API.G_EXC_UNEXPECTED_ERROR: ' ||
3816 SQLERRM;
3817 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3818
3819 WHEN utl_file.invalid_path OR
3820 utl_file.invalid_mode OR
3821 utl_file.invalid_filehandle OR
3822 utl_file.invalid_operation OR
3823 utl_file.write_error THEN
3824
3825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3826 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Final_Level_Winners: [END] UTL_FILE: ' ||
3827 SQLERRM;
3828 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3829
3830 WHEN OTHERS THEN
3831
3832 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3833 ERRBUF := 'JTF_TAE_ASSIGN_PUB.Process_Final_Level_Winners: [END] OTHERS: ' ||
3834 SQLERRM;
3835 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
3836
3837 END Process_Final_Level_Winners;
3838
3839
3840 -- ***************************************************
3841 -- API Specifications
3842 -- ***************************************************
3843 -- api name : TRUNCATE_DROP_TABLE_INDEX
3844 -- type : public.
3845 -- function : TRUNCATE_DROP_TABLE_INDEX
3846 -- pre-reqs :
3847 -- notes :
3848 --
3849
3850 PROCEDURE NM_TABLE_TRUNCATE_DROP_INDEX
3851 ( p_api_version_number IN NUMBER,
3852 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3853 p_SQL_Trace IN VARCHAR2,
3854 p_Debug_Flag IN VARCHAR2,
3855 p_table_name IN VARCHAR2,
3856 x_return_status OUT NOCOPY VARCHAR2,
3857 x_msg_count OUT NOCOPY NUMBER,
3858 x_msg_data OUT NOCOPY VARCHAR2,
3859 ERRBUF OUT NOCOPY VARCHAR2,
3860 RETCODE OUT NOCOPY VARCHAR2
3861 )
3862 AS
3863 l_source_id NUMBER;
3864 l_trans_object_type_id NUMBER;
3865 l_matches_target VARCHAR2(30);
3866 l_winners_target VARCHAR2(30);
3867 l_terr_L1_target VARCHAR2(30);
3868 l_terr_L2_target VARCHAR2(30);
3869 l_terr_L3_target VARCHAR2(30);
3870 l_terr_L4_target VARCHAR2(30);
3871 l_terr_L5_target VARCHAR2(30);
3872 l_terr_WT_target VARCHAR2(30);
3873 BEGIN
3874
3875 IF UPPER(p_table_name) LIKE 'JTF_TAE%NM_TRANS' THEN
3876 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE( p_table_name => p_table_name
3877 , x_return_status => x_return_status);
3878 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES( p_table_name => p_table_name
3879 , x_return_status => x_return_status);
3880 END IF;
3881
3882 l_source_id := -1001;
3883
3884 IF UPPER(p_table_name) LIKE 'JTF_TAE_1001_ACCOUNT%' THEN
3885 l_trans_object_type_id := -1002;
3886 ELSIF UPPER(p_table_name) LIKE 'JTF_TAE_1001_LEAD%' THEN
3887 l_trans_object_type_id := -1003;
3888 ELSIF UPPER(p_table_name) LIKE 'JTF_TAE_1001_OPPOR%' THEN
3889 l_trans_object_type_id := -1004;
3890 END IF;
3891
3892
3893 IF (l_source_id = -1001) THEN
3894 IF (l_trans_object_type_id = -1002) THEN
3895 l_matches_target := 'JTF_TAE_1001_ACCOUNT_MATCHES';
3896 l_winners_target := 'JTF_TAE_1001_ACCOUNT_WINNERS';
3897
3898 l_terr_L1_target := 'JTF_TAE_1001_ACCOUNT_L1';
3899 l_terr_L2_target := 'JTF_TAE_1001_ACCOUNT_L2';
3900 l_terr_L3_target := 'JTF_TAE_1001_ACCOUNT_L3';
3901 l_terr_L4_target := 'JTF_TAE_1001_ACCOUNT_L4';
3902 l_terr_L5_target := 'JTF_TAE_1001_ACCOUNT_L5';
3903 l_terr_WT_target := 'JTF_TAE_1001_ACCOUNT_WT';
3904
3905 ELSIF (l_trans_object_type_id = -1003) THEN
3906 l_matches_target := 'JTF_TAE_1001_LEAD_MATCHES';
3907 l_winners_target := 'JTF_TAE_1001_LEAD_WINNERS';
3908
3909 l_terr_L1_target := 'JTF_TAE_1001_LEAD_L1';
3910 l_terr_L2_target := 'JTF_TAE_1001_LEAD_L2';
3911 l_terr_L3_target := 'JTF_TAE_1001_LEAD_L3';
3912 l_terr_L4_target := 'JTF_TAE_1001_LEAD_L4';
3913 l_terr_L5_target := 'JTF_TAE_1001_LEAD_L5';
3914 l_terr_WT_target := 'JTF_TAE_1001_LEAD_WT';
3915
3916 ELSIF (l_trans_object_type_id = -1004) THEN
3917 l_matches_target := 'JTF_TAE_1001_OPPOR_MATCHES';
3918 l_winners_target := 'JTF_TAE_1001_OPPOR_WINNERS';
3919
3920 l_terr_L1_target := 'JTF_TAE_1001_OPPOR_L1';
3921 l_terr_L2_target := 'JTF_TAE_1001_OPPOR_L2';
3922 l_terr_L3_target := 'JTF_TAE_1001_OPPOR_L3';
3923 l_terr_L4_target := 'JTF_TAE_1001_OPPOR_L4';
3924 l_terr_L5_target := 'JTF_TAE_1001_OPPOR_L5';
3925 l_terr_WT_target := 'JTF_TAE_1001_OPPOR_WT';
3926
3927 END IF; -- what tx type
3928 END IF; -- what usage
3929
3930 -------------
3931 --- TRUNCATE MATCHES
3932 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3933 p_table_name => l_matches_target
3934 , x_return_status => x_return_status );
3935
3936 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3937 p_table_name => l_matches_target
3938 , x_return_status => x_return_status);
3939 -------------
3940 --- TRUNCATE WINNERS
3941 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3942 p_table_name => l_winners_target
3943 , x_return_status => x_return_status );
3944
3945 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3946 p_table_name => l_winners_target
3947 , x_return_status => x_return_status);
3948 -------------
3949 --- Level Tables
3950 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3951 p_table_name => l_terr_L1_target
3952 , x_return_status => x_return_status );
3953
3954 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3955 p_table_name => l_terr_L1_target
3956 , x_return_status => x_return_status);
3957
3958 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3959 p_table_name => l_terr_L2_target
3960 , x_return_status => x_return_status );
3961
3962 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3963 p_table_name => l_terr_L2_target
3964 , x_return_status => x_return_status);
3965
3966 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3967 p_table_name => l_terr_L3_target
3968 , x_return_status => x_return_status );
3969
3970 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3971 p_table_name => l_terr_L3_target
3972 , x_return_status => x_return_status);
3973
3974 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3975 p_table_name => l_terr_L4_target
3976 , x_return_status => x_return_status );
3977
3978 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3979 p_table_name => l_terr_L4_target
3980 , x_return_status => x_return_status);
3981
3982 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3983 p_table_name => l_terr_L5_target
3984 , x_return_status => x_return_status );
3985
3986 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3987 p_table_name => l_terr_L5_target
3988 , x_return_status => x_return_status);
3989
3990 JTF_TAE_INDEX_CREATION_PVT.TRUNCATE_TABLE(
3991 p_table_name => l_terr_WT_target
3992 , x_return_status => x_return_status );
3993
3994 JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES(
3995 p_table_name => l_terr_WT_target
3996 , x_return_status => x_return_status);
3997
3998 x_return_status := FND_API.G_RET_STS_SUCCESS;
3999 EXCEPTION
4000 WHEN OTHERS THEN
4001
4002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4003 ERRBUF := 'JTF_TAE_ASSIGN_PUB.TABLE_TRUNCATE_DROP_INDEX: [END] OTHERS: ' ||
4004 SQLERRM;
4005 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
4006 END NM_TABLE_TRUNCATE_DROP_INDEX;
4007
4008
4009 -- ***************************************************
4010 -- API Specifications
4011 -- ***************************************************
4012 -- api name : FETCH_REPROCESSED_TRANSACTIONS
4013 -- type : public.
4014 -- function : FETCH_NM_REASSIGN_TRANS
4015 -- pre-reqs :
4016 -- notes :
4017 --
4018
4019 PROCEDURE FETCH_NM_REASSIGN_TRANS
4020 ( p_api_version_number IN NUMBER,
4021 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4022 p_SQL_Trace IN VARCHAR2,
4023 p_Debug_Flag IN VARCHAR2,
4024 p_destination_table IN VARCHAR2,
4025 p_source_id IN NUMBER,
4026 p_qual_type_id IN NUMBER,
4027 p_request_id IN NUMBER,
4028 x_return_status OUT NOCOPY VARCHAR2,
4029 x_msg_count OUT NOCOPY NUMBER,
4030 x_msg_data OUT NOCOPY VARCHAR2,
4031 ERRBUF OUT NOCOPY VARCHAR2,
4032 RETCODE OUT NOCOPY VARCHAR2
4033 )
4034 AS
4035 BEGIN
4036
4037 --dbms_output.put_line('JTF_TAE_ASSIGN_PUB.FETCH_NM_REASSIGN_TRANS START');
4038 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ' ');
4039 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'JTF_TAE_ASSIGN_PUB.FETCH_NM_REASSIGN_TRANS: Begins... ');
4040
4041
4042 -- Clear Matches, Winners, and Level tables
4043
4044 -- Start MATCH processing
4045 IF (p_qual_type_id = -1002) THEN
4046
4047 -- ARPATEL: bug#3180665
4048 /* Bug 4213107 : commented out as this is taking too long to complete
4049 UPDATE JTF_TAE_1001_ACCOUNT_TRANS
4050 SET WORKER_ID = 1
4051 WHERE WORKER_ID <> 1;
4052 */
4053
4054 JTF_TAE_1001_ACCOUNT_NMC_DYN.Search_Terr_Rules(
4055 p_source_id => p_source_id ,
4056 p_trans_object_type_id => p_qual_type_id,
4057 x_Return_Status => x_return_status,
4058 x_Msg_Count => x_msg_count,
4059 x_Msg_Data => x_msg_data,
4060 p_worker_id => 1 );
4061
4062 ELSIF (p_qual_type_id = -1003) THEN
4063
4064 /* Bug 4213107 : commented out as this is taking too long to complete
4065 UPDATE JTF_TAE_1001_LEAD_TRANS
4066 SET WORKER_ID = 1
4067 WHERE WORKER_ID <> 1;
4068 */
4069
4070 JTF_TAE_1001_LEAD_NMC_DYN.Search_Terr_Rules(
4071 p_source_id => p_source_id ,
4072 p_trans_object_type_id => p_qual_type_id,
4073 x_Return_Status => x_return_status,
4074 x_Msg_Count => x_msg_count,
4075 x_Msg_Data => x_msg_data,
4076 p_worker_id => 1 );
4077
4078 ELSIF (p_qual_type_id = -1004) THEN
4079
4080 /* Bug 4213107 : commented out as this is taking too long to complete
4081 UPDATE JTF_TAE_1001_OPPOR_TRANS
4082 SET WORKER_ID = 1
4083 WHERE WORKER_ID <> 1;
4084 */
4085
4086 JTF_TAE_1001_OPPOR_NMC_DYN.Search_Terr_Rules(
4087 p_source_id => p_source_id ,
4088 p_trans_object_type_id => p_qual_type_id,
4089 x_Return_Status => x_return_status,
4090 x_Msg_Count => x_msg_count,
4091 x_Msg_Data => x_msg_data,
4092 p_worker_id => 1 );
4093
4094 END IF; -- what tx type
4095
4096 x_return_status := FND_API.G_RET_STS_SUCCESS;
4097
4098 --dbms_output.put_line('JTF_TAE_ASSIGN_PUB.FETCH_NM_REASSIGN_TRANS END');
4099
4100 EXCEPTION
4101 WHEN OTHERS THEN
4102
4103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4104 ERRBUF := 'JTF_TAE_ASSIGN_PUB.FETCH_NM_REASSIGN_TRANS: [END] OTHERS: ' ||
4105 SQLERRM;
4106 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
4107
4108
4109 END FETCH_NM_REASSIGN_TRANS;
4110
4111
4112
4113
4114 -- ***************************************************
4115 -- API Specifications
4116 -- ***************************************************
4117 -- api name : DELETE_CHANGED_TERR_RECS
4118 -- type : public.
4119 -- function : DELETE_CHANGED_TERR_RECS
4120 -- pre-reqs :
4121 -- notes : Deletes all records in JTF_CHANGED_TERR_ALL
4122 -- for given request_id.
4123 --
4124
4125 PROCEDURE DELETE_CHANGED_TERR_RECS
4126 ( p_api_version_number IN NUMBER,
4127 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4128 p_SQL_Trace IN VARCHAR2,
4129 p_Debug_Flag IN VARCHAR2,
4130 p_request_id IN VARCHAR2,
4131 x_return_status OUT NOCOPY VARCHAR2,
4132 x_msg_count OUT NOCOPY NUMBER,
4133 x_msg_data OUT NOCOPY VARCHAR2,
4134 ERRBUF OUT NOCOPY VARCHAR2,
4135 RETCODE OUT NOCOPY VARCHAR2
4136 )
4137 AS
4138 BEGIN
4139
4140 DELETE FROM JTF_CHANGED_TERR_ALL
4141 WHERE REQUEST_ID = p_request_id;
4142
4143 x_return_status := FND_API.G_RET_STS_SUCCESS;
4144 EXCEPTION
4145 WHEN OTHERS THEN
4146
4147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4148 ERRBUF := 'JTF_TAE_ASSIGN_PUB.DELETE_CHANGED_TERR_RECS: [END] OTHERS: ' ||
4149 SQLERRM;
4150 JTF_TAE_CONTROL_PVT.WRITE_LOG(2, ERRBUF);
4151 END DELETE_CHANGED_TERR_RECS;
4152
4153 END JTF_TAE_ASSIGN_PUB;
4154