[Home] [Help]
PACKAGE BODY: APPS.JTF_TAE_CONTROL_PVT
Source
1 PACKAGE BODY jtf_tae_control_pvt AS
2 /* $Header: jtftaecb.pls 120.2 2006/06/23 21:25:32 solin ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TAE_CONTROL
7 -- ---------------------------------------------------
8 -- PURPOSE
9 --
10 -- Control Packages for JTF_TERR_AE packages.
11 -- Replacement of TAP.
12 --
13 -- Procedures:
14 -- (see below for specification)
15 --
16 -- NOTES
17 -- This package is for public use
18 --
19 -- HISTORY
23 -- 02/18/2002 EIHSU Analyze_Territory Added
20 -- 02/10/2002 EIHSU Edward Hsu Created.
21 -- 02/12/2002 EIHSU Add AE CONTROL
22 -- build_qual_rel_multiple
24 -- Cursor build control Added
25 -- 02/19/2002 EIHSU Index creation algorithm added
26 -- Set reduction algorithm added
27 -- 02/19/2002 EIHSU Modify Analyze_Territory to
28 -- analyze only terrs with rsc
29 -- 02/19/2002 EIHSU Edit Index creation
30 -- 03/14/2002 EIHSU Change Set Reduction To Default
31 -- to 'N' for all sets
32 -- 03/15/2002 EIHSU Add Exception handling in case
33 -- Index creation and Selectivity Calc Fails
34 -- 03/18/2002 EIHSU Fix Ordinal Subset Set reduction
35 -- algorithm
36 -- 03/19/2002 EIHSU Added phases description and pseudocode
37 -- for calling JTF_TAE_GEN, JTF_TAE%DYN
38 -- 03/21/2002 EIHSU relation product is different for
39 -- same territory with diff tx quals types
40 -- 04/02/2002 EIHSU terr analysis for set transactiontype
41 -- 04/04/2002 EIHSU parameterized input table
42 -- 04/08/2002 EIHSU renamed a lot of stuff
43 -- 04/15/2002 SBEHERA added source_id and tran_id to update
44 -- jtf_terr_denorm_rules_all
45 -- 04/16/2002 SBEHERA added source_id and tran_id to delete
46 -- jtf_tae_qual_prod_factors
47 -- 05/03/2002 EIHSU set build_index_flag to 'N' if the index
48 -- does not have any columns in it
49 -- 03/08/2004 ACHANDA Fix bug # 3373687
50 -- 11/17/2004 ACHANDA Fix bug # 4009495
51 -- 06/23/2006 SOLIN Bug 5355020
52 --
53 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_TAE_CONTROL_PVT';
54 g_file_name CONSTANT VARCHAR2(12) := 'jtftaecb.pls';
55
56 --
57 -- write_log procedure JDOCHERT: 4/21/02
58 --
59 PROCEDURE Write_Log(which number, mssg varchar2 )
60 IS
61
62 l_mssg VARCHAR2(32767);
63 l_sub_mssg VARCHAR2(255);
64 l_begin NUMBER := 1;
65 l_mssg_length NUMBER := 0;
66 l_time VARCHAR2(60) := TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss');
67
68 BEGIN
69
70 IF (JTF_TAE_CONTROL_PVT.G_DEBUG) Then
71
72 l_mssg := mssg;
73
74 /* If the output message and if debug flag is set then also write
75 ** to the log file
76 */
77 If Which = 2 Then
78 FND_FILE.PUT(1, mssg);
79 FND_FILE.NEW_LINE(1, 1);
80 End IF;
81
82 l_sub_mssg := 'Time = ' || l_time;
83 --FND_FILE.PUT_LINE(FND_FILE.LOG, l_sub_mssg);
84 --dbms_output.put_line('LOG: ' || l_sub_mssg);
85
86 l_mssg := l_sub_mssg || ' => ' || l_mssg;
87
88 /* get total message length */
89 l_mssg_length := LENGTH(l_mssg);
90
91 /* Output message in 250 maximum character lines */
92 WHILE ( l_mssg_length > 250 ) LOOP
93
94 /* get message substring */
95 l_sub_mssg := SUBSTR(l_mssg, l_begin, 250);
96
97 /* write message to log file */
98 FND_FILE.PUT_LINE(FND_FILE.LOG, l_sub_mssg);
99 --dbms_output.put_line('LOG: ' || l_mssg );
100
101 /* Increment message start position to output from */
102 l_begin := l_begin + 250;
103
104 /* Decrement message length to be output */
105 l_mssg_length := l_mssg_length - 250;
106
107 END LOOP;
108
109 /* get last remaining part of message, i.e, when
110 ** there is less than 250 characters left to be output
111 */
112 l_sub_mssg := SUBSTR(l_mssg, l_begin);
113 FND_FILE.PUT_LINE(FND_FILE.LOG, l_sub_mssg);
114 --dbms_output.put_line('LOG: ' || l_mssg );
115
116 END IF;
117
118 --
119 END Write_Log;
120
121 ------------------------------------------------------------------
122
123
124 -- ---------------------------------------------------
125 -- Start of Comments
126 -- ---------------------------------------------------
127 -- Procedure (Private): Classify_Territories
128 -- ---------------------------------------------------
129 -- PURPOSE
130 -- Implements Aggregate/Component Relation Key analysis of
131 -- territory/qualifier relations.
132 --
133 -- REQIRES / DEPENDENCIES
134 -- jtf_terr_denorm_rules_all
135 -- jtf_terr_qual_all
136 --
137 -- MODIFIES
138 -- Rows in JTF_TAE_QUAL_FACTORS/JTF_TAE_QUAL_PRODUCTS
139 --
140 -- EFFECTS
141 -- Allows TAP to run with select statements of terr-qual relation
142 -- combinations.
143 --
144
145 PROCEDURE Classify_Territories
146 (p_Api_Version_Number IN NUMBER,
147 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
148 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
149 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
150
151 x_Return_Status OUT NOCOPY VARCHAR2,
152 x_Msg_Count OUT NOCOPY NUMBER,
156 ERRBUF OUT NOCOPY VARCHAR2,
153 x_Msg_Data OUT NOCOPY VARCHAR2,
154 p_source_id IN NUMBER,
155 p_trans_id IN NUMBER,
157 RETCODE OUT NOCOPY VARCHAR2 )
158 IS
159
160 l_return_status VARCHAR2(1);
161 l_api_name CONSTANT VARCHAR2(30) := 'Analyze_Territories';
162 l_api_version_number CONSTANT NUMBER := 1.0;
163
164 l_source_id NUMBER := p_source_id;
165 l_trans_id NUMBER := p_trans_id;
166
167 l_terr_analyze_id number;
168 l_qual_factor_id number;
169 l_qual_product_id number;
170 l_qual_prod_factor_id number;
171 l_counter number := 0;
172 l_exist_qual_detail_count number;
173 startime date;
174 looptime date;
175 l_terr_analyze_id_arr DBMS_SQL.NUMBER_TABLE;
176 l_qual_prod_counter number;
177 l_counter_qtype_offset number;
178 l_char_tx_id varchar2(30);
179
180 -- BUILD QUAL_RELATION_PRODUCT
181 cursor qual_rel_facts(cl_source_id number, cl_qual_type_id number) is
182 SELECT jqua.qual_usg_id, jqua.qual_relation_factor, jqua.org_id
183 FROM jtf_qual_usgs_all jqua,
184 jtf_qual_type_usgs jqtu
185 , jtf_qual_type_denorm_v v
186 WHERE qual_relation_factor IS NOT NULL
187 and jqua.org_id = -3113
188 and jqua.qual_type_usg_id = jqtu.qual_type_usg_id
189 AND jqtu.source_id = cl_source_id ---1001
190 and jqtu.qual_type_id = v.related_id
191 AND v.qual_type_id = cl_qual_type_id -- -1002
192 AND EXISTS ( SELECT iq.qual_usg_id
193 FROM jtf_qual_usgs_all iq
194 WHERE enabled_flag = 'Y'
195 AND iq.qual_usg_id = jqua.qual_usg_id );
196
197 /* SELECT jqua.qual_usg_id, jqua.qual_relation_factor, jqua.org_id
198 FROM jtf_qual_usgs_all jqua,
199 jtf_qual_type_usgs jqtu
200 WHERE
201 jqua.qual_type_usg_id = jqtu.qual_type_usg_id
202 and qual_relation_factor is not null
203 and jqua.org_id = -3113
204 and jqtu.qual_type_id in (SELECT related_id
205 FROM jtf_qual_type_denorm_v
206 WHERE qual_type_id = cl_qual_type_id);
207 */
208 -- POPULATION OF QUAL_PRODUCT/FACTOR TABLES
209 -- ( CURSOR BUILD CONTROL )
210 ll_counter number := 0;
211 total_terr_all_orgs number := 0;
212
213 /* ARPATEL: 01/06/2004 bug#3337382
214 ** now use qual_relation_product in jtf_terr_qtype_usgs_all
215 */
216 cursor qual_rel_sets(cl_qual_type_id number) is
217 SELECT count(*) total_count, jtqu.qual_relation_product
218 FROM jtf_terr_denorm_rules_all jtdr
219 ,jtf_terr_qtype_usgs_all jtqu
220 ,jtf_qual_type_usgs_all jqtu
221 WHERE 1=1
222 --and org_id = -3113
223 /* JDOCHERT: 07/29/03: BUG# :
224 ** JTF_TAE_QUAL_PRODUCTS NOT BEING CORRECTLY POPULATED SINCE
225 ** POPULATION OF jtdr.resource_exists_flag = 'Y' NOW TAKES
226 ** PLACE AT TOWARDS END OF GTP BEFORE MV REFRESH.
227 */
228 --AND resource_exists_flag = 'Y'
229 --
230 AND jtdr.source_id = p_source_id
231 AND jqtu.source_id = jtdr.source_id
232 AND jqtu.qual_type_id = cl_qual_type_id
233 AND jtdr.terr_id = jtqu.terr_id
234 AND jtdr.terr_id = jtdr.related_terr_id
235 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
236
237 GROUP BY jtqu.qual_relation_product
238 ORDER BY total_count DESC;
239
240 cursor quals_used(qual_relation_product number) is
241 select distinct qual_usg_id -- distinct not really needed
242 from jtf_qual_usgs_all jqua
243 where mod(qual_relation_product, jqua.qual_relation_factor) = 0
244 and org_id = -3113;
245
246 cursor qual_details(cl_qual_usg_id number) is
247 select * from jtf_qual_usgs_all
248 where qual_usg_id = cl_qual_usg_id
249 and org_id = -3113;
250
251 -- cursor non_rsc_qual_types(cl_source_id number) is
252 -- select * from jtf_qual_type_usgs_all
253 -- where source_id = cl_source_id
254 -- and qual_type_id <> -1001;
255
256 BEGIN
257
258 -- Standard call to check for call compatibility.
259 IF NOT fnd_api.compatible_api_call (
260 l_api_version_number,
261 p_api_version_number,
262 l_api_name,
263 g_pkg_name
264 )
265 THEN
266 RAISE fnd_api.g_exc_unexpected_error;
267 END IF;
268 -- Initialize API return status to success
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 ---------------------------------------------------------------
272 -- API BODY BEGINS
273 ---------------------------------------------------------------
274 --dbms_output.put_line('JTF_TERR_AE_CONTROL.Analyze_Territories: BEGIN ');
275
276 BEGIN
277
278 --dbms_output.put_line(' Initializing QUAL_RELATION_PRODUCT values... ');
279
280 /* Initializing Relevant QUAL_RELATION_PRODUCT values
284 /* ARPATEL: 01/06/2004 bug#3337382
281 ** added source and trans id sbehera 04/15/2002 */
282 /* ARPATEL: 12/09/2003 Bug#3307414 Sales denorm record no longer striped by TX type, qual_type_id = -1 */
283
285 ** now use qual_relation_product in jtf_terr_qtype_usgs_all
286 */
287
288 /* ACHANDA 03/08/04 Bug 3373687 : disable the trigger before update */
289 BEGIN
290 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
291 EXCEPTION
292 WHEN OTHERS THEN
293 NULL;
294 END;
295
296 UPDATE jtf_terr_qtype_usgs_all jtqu
297 SET jtqu.QUAL_RELATION_PRODUCT = 1
298 WHERE jtqu.qual_type_usg_id = (
299 SELECT jqtu.qual_type_usg_id
300 FROM jtf_qual_type_usgs_all jqtu
301 WHERE jqtu.source_id = p_source_id
302 AND jqtu.qual_type_id = p_trans_id
303 );
304
305 /* ACHANDA 03/08/04 Bug 3373687 : enable the trigger after update */
306 BEGIN
307 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
308 EXCEPTION
309 WHEN OTHERS THEN
310 NULL;
311 END;
312
313 --UPDATE jtf_terr_denorm_rules_all jtdr
314 -- SET jtdr.QUAL_RELATION_PRODUCT = 1
315 -- WHERE jtdr.terr_id = related_terr_id
316 -- AND jtdr.source_id = p_source_id
317 -- AND jtdr.qual_type_id = -1; --p_trans_id;
318
319
320 NULL;
321
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [1] NO_DATA_FOUND: ' ||
325 'NO territories exist for this Usage/Transaction combination.';
326 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
327 RAISE FND_API.G_EXC_ERROR;
328
329 WHEN OTHERS THEN
330 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [1] OTHERS: ' ||
331 SQLERRM;
332 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334 END;
335
336
337 --dbms_output.put_line('*** Rel Prod Init Complete - Time: ' || trunc((sysdate - startime) * 86400, 5) || ' seconds');
338
339 -----------------------------------------------------------------
340 -- Build QUAL_RELATION_PRODUCT per terr (Incls Inherited quals)
341 -----------------------------------------------------------------
342 --dbms_output.put_line(' Setting QUAL_RELATION_PRODUCT: (Incls Inherited quals) ');
343
344 l_counter := 0;
345
346 FOR qual_rel in qual_rel_facts(l_source_id, l_trans_id) LOOP
347
348 l_counter := l_counter + 1;
349 looptime := sysdate;
350 --dbms_output.put_line(' ' || l_counter || ' qual_usg_id: ' || qual_rel.qual_usg_id || ' / qual_relation_factor: ' || qual_rel.qual_relation_factor);
351
352 BEGIN
353
354 /* ARPATEL: 12/09/2003 Bug#3307414 Sales denorm record no longer striped by TX type, qual_type_id = -1 */
355
356 /* ARPATEL: 01/06/2004 bug#3337382
357 ** now use qual_relation_product in jtf_terr_qtype_usgs_all
358 */
359 /* ACHANDA 03/08/04 Bug 3373687 : disable the trigger before update */
360 BEGIN
361 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
362 EXCEPTION
363 WHEN OTHERS THEN
364 NULL;
365 END;
366
367 UPDATE jtf_terr_qtype_usgs_all jtqu
368 SET jtqu.QUAL_RELATION_PRODUCT =
369 jtqu.QUAL_RELATION_PRODUCT * qual_rel.qual_relation_factor
370 WHERE jtqu.terr_id IN
371 ( SELECT ijtdr.terr_id
372 FROM jtf_terr_denorm_rules_all ijtdr
373 ,jtf_terr_qtype_usgs_all ijtqu
374 ,jtf_qual_type_usgs_all ijqtu
375 ,jtf_terr_qual_all jtq
376 WHERE ijtdr.source_id = l_source_id
377 AND ijqtu.source_id = ijtdr.source_id
378 AND ijqtu.qual_type_id = l_trans_id
379 AND ijtdr.terr_id = ijtqu.terr_id
380 AND ijtqu.qual_type_usg_id = ijqtu.qual_type_usg_id
381 AND ijtdr.related_terr_id = jtq.terr_id
382 AND jtq.qual_usg_id = qual_rel.qual_usg_id
383 )
384 AND jtqu.qual_type_usg_id = (
385 SELECT jqtu.qual_type_usg_id
386 FROM jtf_qual_type_usgs_all jqtu
387 WHERE jqtu.source_id = l_source_id
388 AND jqtu.qual_type_id = l_trans_id
389 );
390
391 /* ACHANDA 03/08/04 Bug 3373687 : enable the trigger after update */
392 BEGIN
393 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
394 EXCEPTION
395 WHEN OTHERS THEN
396 NULL;
397 END;
398
399 /*
400 UPDATE jtf_terr_denorm_rules_all jtdr
401 SET jtdr.QUAL_RELATION_PRODUCT =
402 jtdr.QUAL_RELATION_PRODUCT * qual_rel.qual_relation_factor
403 WHERE jtdr.terr_id IN
404 ( SELECT ijtdr.terr_id
405 FROM jtf_terr_denorm_rules_all ijtdr,
406 jtf_terr_qual_all jtq
407 WHERE ijtdr.source_id = l_source_id
408 and ijtdr.qual_type_id = -1
409 and ijtdr.related_terr_id = jtq.terr_id
413 ** JTF_TAE_QUAL_PRODUCTS NOT BEING CORRECTLY POPULATED SINCE
410 and jtq.qual_usg_id = qual_rel.qual_usg_id )
411 */
412 /* JDOCHERT: 07/29/03: BUG# :
414 ** POPULATION OF jtdr.resource_exists_flag = 'Y' NOW TAKES
415 ** PLACE AT TOWARDS END OF GTP BEFORE MV REFRESH.
416 */
417 --AND jtdr.resource_exists_flag = 'Y'
418 --
419 /*
420 and jtdr.related_terr_id = jtdr.terr_id
421 and jtdr.qual_type_id = -1 --l_trans_id
422 and jtdr.source_id = l_source_id;
423 */
424
425 EXCEPTION
426 WHEN NO_DATA_FOUND THEN
427 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [2] NO_DATA_FOUND: ' ||
428 'UPDATE JTF_TERR_DENORM_RULES_ALL.QUAL_RELATION_PRODUCT.';
429 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
430 RAISE FND_API.G_EXC_ERROR;
431
432 WHEN OTHERS THEN
433 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [2] OTHERS: ' ||
434 SQLERRM;
435 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 END;
438 --dbms_output.put_line(' Update time: ' ||
439 --trunc((sysdate - looptime) * 86400, 3) || ' seconds');
440
441 END LOOP;
442
443
444 --dbms_output.put_line('*** Set Qual Rel Product Complete - Total time: ' ||
445 --trunc((sysdate - startime) * 86400, 3) || ' seconds');
446
447 -----------------------------------------------------------------
448 -- Create Combinations in Product and Factor Tables
449 -----------------------------------------------------------------
450 --dbms_output.put_line(' Populate Algorithm Tables - Cursor build control: BEGIN ');
451 --startime := sysdate;
452
453
454 SELECT JTF_TAE_ANALYZE_TERR_S.NEXTVAL
455 INTO l_terr_analyze_id
456 FROM dual;
457 --dbms_output.put_line(' l_terr_analyze_id= ' || l_terr_analyze_id);
458
459 BEGIN
460
461 -- CLEAR AGGREGATE/COMPONENT RELATION KEY ALGORITHM STORAGE TABLES
462 DELETE FROM jtf_tae_qual_prod_factors
463 where qual_product_id in
464 (select qual_product_id from jtf_tae_qual_products
465 where source_id = l_source_id
466 and trans_object_type_id = l_trans_id);
467
468 delete from JTF_TAE_QUAL_products
469 where source_id = l_source_id and trans_object_type_id = l_trans_id;
470
471 --added sbehera 04/16/2002
472 DELETE FROM jtf_tae_qual_factors o
473 WHERE NOT EXISTS ( SELECT NULL
474 FROM jtf_tae_qual_products i
475 WHERE MOD(i.relation_product, o.relation_factor) = 0 );
476
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [3] NO_DATA_FOUND: ' ||
481 'Clearing Qualifier Combination data.';
482 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
483 RAISE FND_API.G_EXC_ERROR;
484
485 WHEN OTHERS THEN
486 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [3] OTHERS: ' ||
487 SQLERRM;
488 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END;
491
492 l_counter_qtype_offset := 0;
493 l_counter := 0;
494
495 for rel_set in qual_rel_sets(l_trans_id) loop
496 l_counter := l_counter + 1;
497
498 l_char_tx_id := ABS(l_trans_id);
499
500 SELECT JTF_TAE_QUAL_PRODUCTS_S.NEXTVAL
501 INTO l_qual_product_id
502 FROM dual;
503
504 --dbms_output.put_line(' CURSOR NUMBER ' || l_counter || ' total_terr_count: ' || rel_set.total_count || ' / qual_relation_product: ' || rel_set.qual_relation_product);
505 total_terr_all_orgs := total_terr_all_orgs + rel_set.total_count;
506
507 -- POPULATE PRODUCTS
508 if rel_set.qual_relation_product <> 1 then
509
510 BEGIN
511
512 INSERT INTO JTF_TAE_QUAL_products
513 ( QUAL_PRODUCT_ID,
514 RELATION_PRODUCT,
515 SOURCE_ID,
516 TRANS_OBJECT_TYPE_ID,
517 INDEX_NAME,
518 FIRST_CHAR_FLAG,
519 BUILD_INDEX_FLAG,
520 LAST_UPDATE_DATE,
521 LAST_UPDATED_BY,
522 CREATION_DATE,
523 CREATED_BY,
524 LAST_UPDATE_LOGIN,
525 TERR_ANALYZE_ID
526 )
527 VALUES
528 ( l_qual_product_id, --QUAL_PRODUCT_ID,
529 rel_set.qual_relation_product, --RELATION_PRODUCT,
530 l_source_id, --SOURCE_ID,
531 l_trans_id, --TRANS_OBJECT_TYPE_ID,
532 'JTF_TAE_TN' || l_char_tx_id || '_DYN_N'|| TO_CHAR(l_counter), --INDEX_NAME,
533 'N', --FIRST_CHAR,
534 'Y', --BUILD_INDEX_FLAG,
535 sysdate, --LAST_UPDATE_DATE,
536 1, --LAST_UPDATED_BY,
540 l_terr_analyze_id --TERR_ANALYZE_ID,
537 sysdate, --CREATION_DATE,
538 1, --CREATED_BY,
539 1, --LAST_UPDATE_LOGIN)
541 );
542
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN
545 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [4] NO_DATA_FOUND: ' ||
546 'Populating JTF_TAE_QUAL_PRODUCTS table.';
547 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
548 RAISE FND_API.G_EXC_ERROR;
549
550 WHEN OTHERS THEN
551 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [4] OTHERS: ' ||
552 SQLERRM;
553 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555 END;
556
557 --dbms_output.put_line(' Qualifier used in this cursor: ');
558 ll_counter := 0;
559 l_qual_prod_counter := 0;
560
561 for qual_name in quals_used(rel_set.qual_relation_product) loop
562 ll_counter := ll_counter + 1;
563 --dbms_output.put_line(' ' || ll_counter || ' qual_usg_id: ' || qual_name.qual_usg_id);
564
565 for q_detail in qual_details(qual_name.qual_usg_id) loop
566 -- there should only be one record here
567 l_qual_prod_counter := l_qual_prod_counter + 1;
568
569 select count(*) into l_exist_qual_detail_count
570 from JTF_TAE_QUAL_factors
571 where qual_usg_id = q_detail.qual_usg_id;
572
573 if l_exist_qual_detail_count = 0 then
574
575 BEGIN
576
577 SELECT JTF_TAE_QUAL_factors_s.NEXTVAL
578 INTO l_qual_factor_id
579 FROM dual;
580
581 INSERT INTO JTF_TAE_QUAL_factors
582 ( QUAL_FACTOR_ID ,
583 RELATION_FACTOR ,
584 QUAL_USG_ID ,
585 LAST_UPDATED_BY ,
586 LAST_UPDATE_DATE ,
587 CREATED_BY ,
588 CREATION_DATE ,
589 LAST_UPDATE_LOGIN ,
590 TERR_ANALYZE_ID ,
591 TAE_COL_MAP ,
592 TAE_REC_MAP ,
593 USE_TAE_COL_IN_INDEX_FLAG,
594 UPDATE_SELECTIVITY_FLAG ,
595 INPUT_SELECTIVITY ,
596 INPUT_ORDINAL_SELECTIVITY,
597 INPUT_DEVIATION ,
598 ORG_ID ,
599 OBJECT_VERSION_NUMBER
600 )
601 VALUES
602 ( l_qual_factor_id, -- QUAL_FACTOR_ID
603 q_detail.qual_relation_factor, -- RELATION_FACTOR
604 q_detail.qual_usg_id, -- QUAL_USG_ID
605 0, -- LAST_UPDATED_BY
606 sysdate, -- LAST_UPDATE_DATE
607 0, -- CREATED_BY
608 sysdate, -- CREATION_DATE
609 0, -- LAST_UPDATE_LOGIN
610 l_terr_analyze_id, -- TERR_ANALYZE_ID
611 q_detail.qual_col1, -- TAE_COL_MAP
612 q_detail.qual_col1_alias, -- TAE_REC_MAP
613 'Y', -- USE_TAE_COL_IN_INDEX_FLAG
614 'Y', -- UPDATE_SELECTIVITY_FLAG
615 null, -- INPUT_SELECTIVITY
616 null, -- INPUT_ORDINAL_SELECTIVITY
617 null, -- INPUT_DEVIATION
618 null, -- ORG_ID
619 null -- OBJECT_VERSION_NUMBER
620 );
621
622 /* BUG#2990404: JDOCHERT: 09/02/03:
623 ** make records created in this session
624 ** immediately visible to other sessions
625 */
626 COMMIT;
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [5] NO_DATA_FOUND: ' ||
631 'Populating JTF_TAE_QUAL_FACTORS table.';
632 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
633 RAISE FND_API.G_EXC_ERROR;
634
635 WHEN OTHERS THEN
636 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [5] OTHERS: ' ||
637 SQLERRM;
638 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
642 end if;
639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
640 END;
641
643
644 end loop; -- should only be one record: insert details to qual_factor table
645 end loop; -- factors in this product
646
647 for qual_name in quals_used(rel_set.qual_relation_product) loop
648
649 BEGIN
650
651 /* BUG#2990404: JDOCHERT: 09/02/03:
652 ** add rownum restriction to avoid GTP failing
653 */
654 select qual_factor_id into l_qual_factor_id
655 from JTF_TAE_QUAL_factors
656 where qual_usg_id = qual_name.qual_usg_id
657 AND rownum < 2;
658
659 SELECT JTF_TAE_QUAL_PROD_FACTORS_S.NEXTVAL
660 INTO l_qual_prod_factor_id
661 FROM dual;
662
663 INSERT INTO JTF_TAE_QUAL_prod_factors
664 ( QUAL_PROD_FACTOR_ID,
665 QUAL_PRODUCT_ID,
666 QUAL_FACTOR_ID,
667 LAST_UPDATE_DATE,
668 LAST_UPDATED_BY,
669 CREATION_DATE,
670 CREATED_BY,
671 LAST_UPDATE_LOGIN,
672 TERR_ANALYZE_ID,
673 ORG_ID,
674 OBJECT_VERSION_NUMBER
675 )
676 VALUES
677 ( l_qual_prod_factor_id, --QUAL_PROD_FACTOR_ID,
678 l_qual_product_id, --QUAL_PRODUCT_ID,
679 l_qual_factor_id, --QUAL_FACTOR_ID
680 sysdate, --LAST_UPDATE_DATE,
681 0, --LAST_UPDATED_BY,
682 sysdate, --CREATION_DATE,
683 0, --CREATED_BY,
684 0, --LAST_UPDATE_LOGIN,
685 l_terr_analyze_id, --TERR_ANALYZE_ID,
686 null, --ORG_ID,
687 null --OBJECT_VERSION_NUMBER
688 );
689
690 EXCEPTION
691 WHEN NO_DATA_FOUND THEN
692 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [6] NO_DATA_FOUND: ' ||
693 'Populating JTF_TAE_QUAL_PROD_FACTORS table.';
694 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
695 RAISE FND_API.G_EXC_ERROR;
696
697 WHEN OTHERS THEN
698 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [6] OTHERS: ' ||
699 SQLERRM;
700 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END;
703
704 end loop; -- all factors per combination
705 end if; -- product combination <> 1 (for srinibas)
706 end loop; -- all product combinations
707 l_counter_qtype_offset := l_counter;
708 --end loop; -- per transaction type
709
710 -- completion:API return status to success
711 x_return_status := FND_API.G_RET_STS_SUCCESS;
712 --dbms_output.put_line(' ');
713
714
715 EXCEPTION
716
717 WHEN FND_API.G_EXC_ERROR THEN
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 RETCODE := 1;
720
721 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
722
723 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725 RETCODE := 2;
726
727 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
728
729
730 WHEN utl_file.invalid_path OR
731 utl_file.invalid_mode OR
732 utl_file.invalid_filehandle OR
733 utl_file.invalid_operation OR
734 utl_file.write_error THEN
735
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
737 RETCODE := 2;
738 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [END] UTL_FILE: ' ||
739 SQLERRM;
740
741 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
742
743
744 WHEN OTHERS THEN
745
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
747 RETCODE := 2;
748 ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [END] OTHERS: ' ||
749 SQLERRM;
750
751 JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
752
753
754 END Classify_Territories;
755
756
757 -- ---------------------------------------------------
758 -- Start of Comments
759 -- ---------------------------------------------------
760 -- Procedure (Private): Reduce_TX_OIN_Index_Set
761 -- ---------------------------------------------------
762 -- PURPOSE (SET REDUCTION)
763 -- Determines what qualifier relation combinations need to
764 -- have corresponding index built, by reducing sets of combinations
765 -- to the smallest number collectively exhaustive sets that are
766 -- ordinal supersets of all sets reduced.
767 --
768 --
769 -- REQIRES / DEPENDENCIES
770 -- JTF_TAE_QUAL_FACTORS
771 -- JTF_TAE_QUAL_PRODUCTS
772 --
773 -- MODIFIES
774 -- Rows in JTF_TAE_QUAL_PRODUCTS
778 -- EFFECTS
775 -- Sets value of BUILD_INDEX_FLAG
776 -- Sets value of FIRST_CHAR (A FLAG) ## NOT YET COMPLETED
777 --
779 -- Allows TAP to efficiently run by noting what indicies need
780 -- to be created.
781 --
782
783 PROCEDURE Reduce_TX_OIN_Index_Set
784 (p_Api_Version_Number IN NUMBER,
785 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
786 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
787 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
788 p_source_id IN NUMBER,
789 p_trans_id IN NUMBER,
790 x_Return_Status OUT NOCOPY VARCHAR2,
791 x_Msg_Count OUT NOCOPY NUMBER,
792 x_Msg_Data OUT NOCOPY VARCHAR2
793 )
794 IS
795
796 l_counter number := 0;
797 startime date;
798 l_first_char_flag VARCHAR2(1) := 'N';
799 l_first_char_flag_count number;
800 l_trans_id NUMBER;
801
802 Cursor all_sets is
803 select p.trans_object_type_id, count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
804 from JTF_TAE_QUAL_products p,
805 JTF_TAE_QUAL_prod_factors pf,
806 JTF_TAE_QUAL_factors f
807 where p.qual_product_id = pf.qual_product_id
808 and pf.qual_factor_id = f.qual_factor_id
809 and f.tae_col_map is not null
810 and p.source_id = p_source_id
811 and p.trans_object_type_id = p_trans_id
812 group by p.trans_object_type_id, p.qual_product_id, p.relation_product
813 order by p.relation_product;
814
815 /* select count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
816 from JTF_TAE_QUAL_products p,
817 JTF_TAE_QUAL_prod_factors pf
818 where p.qual_product_id = pf.qual_product_id
819 group by p.qual_product_id, p.relation_product
820 order by 1;
821 */
822
823 Cursor larger_or_eq_sets( cl_size NUMBER
824 , cl_qual_product_id NUMBER
825 , cl_relation_product NUMBER ) is
826 select * from (
827 select count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
828 from JTF_TAE_QUAL_products p,
829 JTF_TAE_QUAL_prod_factors pf
830 where p.qual_product_id = pf.qual_product_id
831 and p.source_id = p_source_id
832 and p.trans_object_type_id = p_trans_id
833 group by p.qual_product_id, p.relation_product
834 )
835 where num_components >= cl_size
836 and relation_product > cl_relation_product
837 order by 1 DESC, qual_product_id ASC;
838
839 Cursor all_empty_column_indexes is
840 select p.trans_object_type_id, p.qual_product_id, p.relation_product
841 from JTF_TAE_QUAL_products p
842 where not exists (select *
843 from JTF_TAE_QUAL_products ip,
844 JTF_TAE_QUAL_prod_factors ipf,
845 JTF_TAE_QUAL_factors ifc
846
847 where use_tae_col_in_index_flag = 'Y'
848 and ip.qual_product_id = ipf.qual_product_id
849 and ipf.qual_factor_id = ifc.qual_factor_id
850 and ip.qual_product_id = p.qual_product_id)
851 and p.source_id = p_source_id
852 and p.trans_object_type_id = p_trans_id;
853
854
855 cl_count NUMBER := 0;
856 S_element_ord_subset_L_count NUMBER := 0;
857 S_subset_L VARCHAR2(1) := 'N';
858 L_current_product NUMBER;
859
860 retcode VARCHAR2(100);
861 errbuf varchar2(3000);
862
863 BEGIN
864 --dbms_output.put_line(' JTF_TERR_AE_CONTROL.Set_Input_Qual_Indices : BEGIN ');
865 startime := sysdate;
866 l_trans_id := p_trans_id;
867
868
869 for cl_set_S in all_sets loop -- OK
870 S_subset_L := 'N';
871 --dbms_output.put_line(' SIZE ' || cl_set_S.num_components || ' set elements. : ' || cl_set_S.qual_product_id);
872
873 -- set REDUCTION (done on input column)
874
875 FOR cl_set_L IN larger_or_eq_sets( cl_set_S.num_components
876 , cl_set_S.qual_product_id
877 , cl_set_S.relation_product ) LOOP
878
879 --dbms_output.put_line(' Larger set ' || cl_set_L.qual_product_id);
880
881 select COUNT(*) into S_element_ord_subset_L_count
882 from (
883 select rownum row_count, tae_col_map, input_selectivity
884 from (
885 select distinct p.relation_product, f.tae_col_map, f.input_selectivity
886 from JTF_TAE_QUAL_products p,
887 JTF_TAE_QUAL_prod_factors pf,
888 JTF_TAE_QUAL_factors f
889 where f.qual_factor_id = pf.qual_factor_id
890 and pf.qual_product_id = p.qual_product_id
891 and p.relation_product = cl_set_S.relation_product
892 and f.tae_col_map is not null
896 )
893 and p.source_id = p_source_id
894 and p.trans_object_type_id = p_trans_id
895 order by input_selectivity
897 ) S,
898 (
899 select rownum row_count, tae_col_map, input_selectivity
900 from (
901 select distinct p.relation_product, f.tae_col_map, f.input_selectivity
902 from JTF_TAE_QUAL_products p,
903 JTF_TAE_QUAL_prod_factors pf,
904 JTF_TAE_QUAL_factors f
905 where f.qual_factor_id = pf.qual_factor_id
906 and pf.qual_product_id = p.qual_product_id
907 and p.relation_product = cl_set_L.relation_product
908 and f.tae_col_map is not null
909 and p.source_id = p_source_id
910 and p.trans_object_type_id = p_trans_id
911 order by input_selectivity
912 )
913 ) L
914 where S.tae_col_map = L.tae_col_map
915 and S.row_count = L.row_count;
916
917 -- for info only
918 L_current_product := cl_set_L.relation_product;
919 --dbms_output.put_line(' S_element_ord_subset_L_count ' || S_element_ord_subset_L_count);
920 --dbms_output.put_line(' cl_set_S.num_components ' || cl_set_S.num_components);
921
922 if S_element_ord_subset_L_count = cl_set_S.num_components then
923 S_subset_L := 'Y';
924 exit;
925 else
926 S_subset_L := 'N';
927 end if;
928
929 end loop; -- all larger sets L
930
931 -- set FIRST_CHAR_FLAG for created index
932 select count(*) into l_first_char_flag_count
933 from
934 (select qual_usg_id, tae_col_map, rownum row_count
935 from ( select f.qual_usg_id, f.relation_factor, f.tae_col_map
936 from JTF_TAE_QUAL_prod_factors pf,
937 JTF_TAE_QUAL_factors f
938 where pf.qual_factor_id = f.qual_factor_id
939 and pf.qual_product_id = cl_set_S.qual_product_id
940 order by f.input_selectivity
941 )
942 ) ilv1,
943 (select qual_usg_id, 1 row_count
944 from jtf_qual_usgs_all
945 where org_id = -3113
946 and seeded_qual_id = -1012
947 ) ilv2
948 where ilv1.qual_usg_id = ilv2.qual_usg_id
949 and ilv1.row_count = ilv2.row_count;
950
951 if l_first_char_flag_count > 0 then
952 l_first_char_flag := 'Y';
953 else
954 l_first_char_flag := 'N';
955 end if;
956
957 if S_subset_L = 'Y' then
958 --dbms_output.put_line(' ' || cl_set_S.relation_product || ' IS ordinal subset of ' || L_current_product);
959 UPDATE JTF_TAE_QUAL_PRODUCTS
960 SET BUILD_INDEX_FLAG = 'N', FIRST_CHAR_FLAG = l_first_char_flag
961 WHERE qual_product_id = cl_set_S.qual_product_id
962
963 /* JDOCHERT: 10/12/03: INDEX SHOULD ALWAYS BE BUILT
964 ** ON THESE COMBINATIONS: ASSUMES THAT IN PROCEDURE,
965 ** Classify_Territories, THAT JTF_QUAL_PRODUCTS.BUILD_INDEX_FLAG
966 ** IS ALWAYS INITIALIZED TO 'Y'.
967 */
968 AND RELATION_PRODUCT NOT IN (4841, 324347);
969
970 /* ARPATEL: 10/20, still need to make sure that first_char_flag is set for 4841 and 324347 */
971 UPDATE JTF_TAE_QUAL_PRODUCTS
972 SET FIRST_CHAR_FLAG = l_first_char_flag
973 WHERE qual_product_id = cl_set_S.qual_product_id
974 AND RELATION_PRODUCT IN (4841, 324347);
975
976
977 else
978 --dbms_output.put_line(' ' || cl_set_S.relation_product || ' NOT an ordinal subset');
979 UPDATE JTF_TAE_QUAL_PRODUCTS
980 SET BUILD_INDEX_FLAG = 'Y', FIRST_CHAR_FLAG = l_first_char_flag
981 WHERE qual_product_id = cl_set_S.qual_product_id;
982
983 end if;
984
985 /* ARPATEL: 04/16/2004 For Qual_Relation_Product = 353393 (Cust Name Range Group + Postal Code + Country)
986 ** we need to set first_char_flag to 'Y' to ensure that reverse index
987 ** (JTF_TAE_TN1002_324347X_ND) for 324347
988 ** is created, regardless of whether 324347 territories exist.
989 */
990 UPDATE JTF_TAE_QUAL_PRODUCTS
991 SET FIRST_CHAR_FLAG = 'Y'
992 WHERE qual_product_id = cl_set_S.qual_product_id
993 AND RELATION_PRODUCT = 353393;
994
995 end loop; -- all sets S
996
997 -- Set reduction complete
998 -- Set build_index_flag = 'N' for all empty column indexes combinations
999 --dbms_output.put_line('Set reduction code running ');
1000 for empty_column_index in all_empty_column_indexes loop
1001 --dbms_output.put_line('BUILD_INDEX_FLAG = ''N'' for product_id: ' || empty_column_index.qual_product_id);
1005 end loop;
1002 update JTF_TAE_QUAL_PRODUCTS p
1003 set BUILD_INDEX_FLAG = 'N'
1004 where p.qual_product_id = empty_column_index.qual_product_id;
1006
1007 COMMIT;
1008
1009 -- completion:API return status to success
1010 x_return_status := FND_API.G_RET_STS_SUCCESS;
1011
1012 EXCEPTION
1013
1014 WHEN FND_API.G_EXC_ERROR THEN
1015 x_return_status := FND_API.G_RET_STS_ERROR;
1016
1017 IF G_Debug THEN
1018 Write_Log(2, ERRBUF);
1019 END IF;
1020
1021 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1023
1024 IF G_Debug THEN
1025 Write_Log(2, ERRBUF);
1026 END IF;
1027
1028 WHEN utl_file.invalid_path OR
1029 utl_file.invalid_mode OR
1030 utl_file.invalid_filehandle OR
1031 utl_file.invalid_operation OR
1032 utl_file.write_error THEN
1033
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1035 RETCODE := 2;
1036 ERRBUF := 'JTF_TAE_CONTROL.Reduce_TX_OIN_Index_Set: [END] UTL_FILE: ' ||
1037 SQLERRM;
1038
1039 If G_Debug Then
1040 Write_Log(2, ERRBUF);
1041 End If;
1042
1043 WHEN OTHERS THEN
1044
1045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1046 RETCODE := 2;
1047 ERRBUF := 'JTF_TAE_CONTROL.Reduce_TX_OIN_Index_Set: [END] OTHERS: ' ||
1048 SQLERRM;
1049
1050 If G_Debug Then
1051 Write_Log(2, ERRBUF);
1052 End If;
1053
1054 end Reduce_TX_OIN_Index_Set;
1055
1056
1057 -- ---------------------------------------------------
1058 -- Start of Comments
1059 -- ---------------------------------------------------
1060 -- Procedure (Public): Decompose_Terr_Defns
1061 -- ---------------------------------------------------
1062 -- PURPOSE
1063 -- Main package API Interface. Calls all procedures of
1064 -- JTF_TERR_AE packages needed for running JTF Terr Assignment Engine,
1065 -- the productized replacement of TAP.
1066 --
1067 -- REQIRES / DEPENDENCIES
1068 -- JTF_TERR_AE_CONTROL Private procedures.
1069 --
1070 -- MODIFIES
1071 -- Rows in JTF_TAE_QUAL_FACTORS/JTF_TAE_QUAL_PRODUCTS
1072 --
1073 -- EFFECTS
1074 -- Allows TAP to run with select statements of terr-qual relation
1075 -- combinations.
1076 --
1077 PROCEDURE Decompose_Terr_Defns
1078 (p_Api_Version_Number IN NUMBER,
1079 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1080 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1081 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1082 x_Return_Status OUT NOCOPY VARCHAR2,
1083 x_Msg_Count OUT NOCOPY NUMBER,
1084 x_Msg_Data OUT NOCOPY VARCHAR2,
1085 p_run_mode IN VARCHAR2 := 'FULL',
1086 p_classify_terr_comb IN VARCHAR2 := 'Y',
1087 p_process_tx_oin_sel IN VARCHAR2 := 'Y',
1088 p_generate_indexes IN VARCHAR2 := 'Y',
1089 p_source_id IN NUMBER,
1090 p_trans_id IN NUMBER,
1091 ERRBUF OUT NOCOPY VARCHAR2,
1092 RETCODE OUT NOCOPY VARCHAR2 )
1093
1094 IS
1095 x_runtime VARCHAR2(30);
1096 l_return_status VARCHAR2(1);
1097 l_start_time date;
1098 l_run_time varchar2(300);
1099 l_dummy NUMBER;
1100 l_source_id NUMBER := p_source_id;
1101 l_trans_id NUMBER := p_trans_id;
1102 --l_run_time date;
1103 l_selectivity_return_val NUMBER;
1104 l_build_index_return_val NUMBER;
1105 l_dyn_str varchar2(32767);
1106 l_trans_input_target VARCHAR2(30);
1107 l_trans_matches_target VARCHAR2(30);
1108
1109 BEGIN
1110 l_start_time := sysdate;
1111 --dbms_output.put_line('JTF_TERR_AE_CONTROL.Run_Terr_Assign_Engine : BEGIN');
1112
1113
1114 IF (p_source_id = -1001) THEN
1115 --dbms_output.put_line(' p_source_id = -1001 ');
1116
1117 IF (p_trans_id = -1002) THEN
1118 --ARPATEL 09/12/2003 OIC requirement
1119 IF p_run_mode = 'OIC_TAP' THEN
1120
1121 l_trans_input_target := 'JTF_TAE_1001_SC_TRANS';
1122 l_trans_matches_target := 'JTF_TAE_1001_SC_MATCHES';
1123
1124 ELSE
1125
1126 IF p_run_mode = 'NEW_MODE_TAP' THEN
1127 l_trans_input_target := 'JTF_TAE_1001_ACCOUNT_NM_TRANS';
1128 ELSE
1129 l_trans_input_target := 'JTF_TAE_1001_ACCOUNT_TRANS';
1130 END IF;
1131
1132 l_trans_matches_target := 'JTF_TAE_1001_ACCOUNT_MATCHES';
1133
1134 END IF; --run_mode=OIC_TAP
1135
1136 ELSIF (p_trans_id = -1003) THEN
1137 IF p_run_mode = 'NEW_MODE_TAP' THEN
1138 l_trans_input_target := 'JTF_TAE_1001_LEAD_NM_TRANS';
1139 ELSE
1140 l_trans_input_target := 'JTF_TAE_1001_LEAD_TRANS';
1141 END IF;
1142 l_trans_matches_target := 'JTF_TAE_1001_LEAD_MATCHES';
1143
1144 ELSIF (p_trans_id = -1004) THEN
1145 IF p_run_mode = 'NEW_MODE_TAP' THEN
1146 l_trans_input_target := 'JTF_TAE_1001_OPPOR_NM_TRANS';
1147 ELSE
1148 l_trans_input_target := 'JTF_TAE_1001_OPPOR_TRANS';
1149 END IF;
1150 l_trans_matches_target := 'JTF_TAE_1001_OPPOR_MATCHES';
1151
1152 END IF;
1153
1154 END IF;
1155
1156 IF p_run_mode = 'TAE2' THEN
1157 l_trans_input_target := 'JTF_TAE_1001_ACCOUNT_TRANS';
1158 END IF;
1159
1160 -- check if source_id is valid
1161 if (l_source_id <> -1001) then
1162 raise FND_API.G_EXC_ERROR;
1163 end if;
1164
1165 /*
1166 --dbms_output.put_line('p_classify_terr_comb = ' || p_classify_terr_comb);
1167 --dbms_output.put_line('p_process_tx_oin_sel = ' || p_process_tx_oin_sel);
1168 --dbms_output.put_line('p_generate_indexes = ' || p_generate_indexes);
1169 */
1170
1171 -- ANALYSIS OF TERRITORY DEFINITION FOR DYN PACKAGE GENERATION
1172 if p_classify_terr_comb = 'Y' then
1173
1174 -- must do this for all qual_types
1175 -- Classify_Territories
1176 Classify_Territories
1177 (p_Api_Version_Number => 1.0,
1178 p_Init_Msg_List => FND_API.G_FALSE,
1179 p_Commit => FND_API.G_FALSE,
1180 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1181 x_Return_Status => l_return_status,
1182 x_Msg_Count => x_msg_count,
1183 x_Msg_Data => x_msg_data,
1184 p_source_id => l_source_id,
1185 p_trans_id => l_trans_id,
1186 errbuf => ERRBUF,
1187 retcode => RETCODE
1188 );
1189
1190 --dbms_output.put_line('JTF_TAE_CONTROL_PVT.Decompose_Terr_Defns: l_return_status= ' || l_return_status);
1191 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1192
1193 ERRBUF := 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS: [1]' ||
1194 'CALL to JTF_TAE_CONTROL.Classify_Territories API has failed.';
1195 RAISE FND_API.G_EXC_ERROR;
1196
1197 END IF;
1198
1199 update JTF_TAE_QUAL_factors
1200 set UPDATE_SELECTIVITY_FLAG = 'N', USE_TAE_COL_IN_INDEX_FLAG = 'N'
1201 where TAE_COL_MAP is null;
1202
1203 end if; -- p_classify_terr_comb?
1204
1205 -- OPTIMIZATION OF DATABASE OBJECTS
1206 if p_process_tx_oin_sel = 'Y' or p_process_tx_oin_sel = 'R' then
1207 if p_process_tx_oin_sel = 'Y' then
1208 -- Analyze Selectivity and Get ordinals
1209 --dbms_output.put_line(' Analyze Selectivity and Get ordinals ');
1210 l_selectivity_return_val := jtf_tae_index_creation_pvt.selectivity(l_trans_input_target);
1211
1212 IF l_selectivity_return_val <> 1 THEN
1213 RAISE FND_API.G_EXC_ERROR;
1214 END IF;
1215
1216 end if;
1217
1218 --dbms_output.put_line(' Reducing Sets ');
1219 -- Reduce Sets
1220 Reduce_TX_OIN_Index_Set
1221 (p_Api_Version_Number => 1.0,
1222 p_Init_Msg_List => FND_API.G_FALSE,
1223 p_Commit => FND_API.G_FALSE,
1224 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1225 p_source_id => l_source_id,
1226 p_trans_id => l_trans_id,
1227 x_Return_Status => l_return_status,
1228 x_Msg_Count => x_msg_count,
1229 x_Msg_Data => x_msg_data
1230 );
1231
1232 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1233
1234 ERRBUF := 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS: [2]' ||
1235 'CALL to JTF_TAE_CONTROL.Reduce_TX_OIN_Index_Set API has failed.';
1236 RAISE FND_API.G_EXC_ERROR;
1237
1238 END IF;
1239
1240 end if; -- p_process_tx_oin_sel?
1241
1242 if p_generate_indexes = 'Y' then
1243 --dbms_output.put_line('Dropping and Generating Indexes');
1244 -- Drop Indexes
1245 jtf_tae_index_creation_pvt.drop_table_indexes(
1246 p_table_name => l_trans_input_target
1247 , x_return_status => l_return_status);
1248
1249 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1250
1251 ERRBUF := 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS: [3]' ||
1252 'CALL to JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES ' ||
1256 END IF;
1253 'API has failed.';
1254 RAISE FND_API.G_EXC_ERROR;
1255
1257
1258 -- Build Indexes
1259 jtf_tae_index_creation_pvt.create_index(
1260 p_table_name => l_trans_input_target
1261 , p_trans_object_type_id => l_trans_id
1262 , p_source_id => l_source_id
1263 , x_Return_Status => l_return_status
1264 , p_run_mode => p_run_mode );
1265
1266 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1267
1268 ERRBUF := 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS: [4]' ||
1269 'CALL to JTF_TAE_INDEX_CREATION_PVT.CREATE_INDEX ' ||
1270 'API has failed.';
1271 RAISE FND_API.G_EXC_ERROR;
1272
1273 END IF;
1274
1275 -- Analyze Trans Input Target
1276 /* JDOCHERT: 04/10/03: bug#2896552 */
1277 --JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
1278 -- p_table_name => l_trans_input_target
1279 -- , p_percent => 5
1280 -- , x_Return_Status => l_return_status );
1281 -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1282 -- ERRBUF := 'JTF_TAE_CONTROL_PVT.DECOMPOSE_TERR_DEFNS: [5]' ||
1283 -- 'CALL to JTF_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX ' ||
1284 -- 'API has failed.';
1285 -- RAISE FND_API.G_EXC_ERROR;
1286 -- END IF;
1287 --
1288
1289
1290 END IF;
1291
1292 --dbms_output.put_line('Decompose_Terr_Defns ran to completion');
1293 x_return_status := l_return_status;
1294
1295 EXCEPTION
1296
1297 WHEN FND_API.G_EXC_ERROR THEN
1298 x_return_status := FND_API.G_RET_STS_ERROR;
1299
1300 RETCODE := 2;
1301
1302 IF G_Debug THEN
1303 Write_Log(2, ERRBUF);
1304 END IF;
1305
1306 WHEN utl_file.invalid_path OR
1307 utl_file.invalid_mode OR
1308 utl_file.invalid_filehandle OR
1309 utl_file.invalid_operation OR
1310 utl_file.write_error THEN
1311
1312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1313 RETCODE := 2;
1314 ERRBUF := 'JTF_TAE_CONTROL.Decompose_Terr_Defns: [END] UTL_FILE: ' ||
1315 SQLERRM;
1316
1317 If G_Debug Then
1318 Write_Log(2, ERRBUF);
1319 End If;
1320
1321 WHEN OTHERS THEN
1322
1323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1324 RETCODE := 2;
1325 ERRBUF := 'JTF_TAE_CONTROL.Decompose_Terr_Defns: [END] OTHERS: ' ||
1326 SQLERRM;
1327
1328 If G_Debug Then
1329 Write_Log(2, ERRBUF);
1330 End If;
1331
1332 END Decompose_Terr_Defns;
1333
1334
1335 -- ---------------------------------------------------
1336 -- Start of Comments
1337 -- ---------------------------------------------------
1338 -- Procedure (Public): set_session_parameters
1339 -- ---------------------------------------------------
1340 -- PURPOSE
1341 --
1342 -- REQIRES / DEPENDENCIES
1343 --
1344 -- MODIFIES
1345 --
1346 -- EFFECTS
1347 --
1348 PROCEDURE set_session_parameters ( p_sort_area_size NUMBER
1349 , p_hash_area_size NUMBER ) AS
1350
1351 l_dyn_csr NUMBER;
1352 l_result NUMBER;
1353
1354 retcode VARCHAR2(100);
1355 errbuf varchar2(3000);
1356
1357 BEGIN
1358
1359 /*****
1360
1361 -- Commented out since OSO TAP
1362 -- sets these parameters
1363
1364 l_dyn_csr := dbms_sql.open_cursor;
1365 dbms_sql.parse( l_dyn_csr
1366 , 'ALTER SESSION SET sort_area_size=' || TO_CHAR(p_sort_area_size)
1367 , dbms_sql.native );
1368 l_result := dbms_sql.execute(l_dyn_csr);
1369 dbms_sql.close_cursor(l_dyn_csr);
1370
1371 l_dyn_csr := dbms_sql.open_cursor;
1372 dbms_sql.parse( l_dyn_csr
1373 , 'ALTER SESSION SET hash_area_size=' || TO_CHAR(p_hash_area_size)
1374 , dbms_sql.native);
1375 l_result := dbms_sql.execute(l_dyn_csr);
1376 dbms_sql.close_cursor(l_dyn_csr);
1377
1378 l_dyn_csr := dbms_sql.open_cursor;
1379 dbms_sql.parse( l_dyn_csr
1380 , 'ALTER SESSION ENABLE PARALLEL DML'
1381 , dbms_sql.native);
1382 l_result := dbms_sql.execute(l_dyn_csr);
1383 dbms_sql.close_cursor(l_dyn_csr);
1384
1385 *****/
1386
1387 NULL;
1388
1389 EXCEPTION
1390
1391 WHEN utl_file.invalid_path OR
1392 utl_file.invalid_mode OR
1393 utl_file.invalid_filehandle OR
1394 utl_file.invalid_operation OR
1395 utl_file.write_error THEN
1396
1397 RETCODE := 2;
1398 ERRBUF := 'JTF_TAE_CONTROL_PVT.set_session_parameters: [END] UTL_FILE: ' ||
1399 SQLERRM;
1400
1401 If G_Debug Then
1402 Write_Log(2, ERRBUF);
1403 End If;
1404
1405 RAISE;
1406
1407 WHEN OTHERS THEN
1408
1409 RETCODE := 2;
1410 ERRBUF := 'JTF_TAE_CONTROL_PVT.set_session_parameters: [END] OTHERS: ' ||
1411 SQLERRM;
1412
1413 If G_Debug Then
1414 Write_Log(2, ERRBUF);
1415 End If;
1416
1417 RAISE;
1418
1419 END set_session_parameters;
1420
1421
1422 -- ---------------------------------------------------
1423 -- Start of Comments
1424 -- ---------------------------------------------------
1425 -- Procedure (Public): set_table_nologging
1426 -- ---------------------------------------------------
1427 -- PURPOSE
1428 --
1429 -- REQIRES / DEPENDENCIES
1430 --
1431 -- MODIFIES
1432 --
1433 -- EFFECTS
1434 --
1435 PROCEDURE set_table_nologging( p_table_name VARCHAR2 ) AS
1436
1437 l_dyn_csr NUMBER;
1438 l_result NUMBER;
1439
1440 l_schema_name VARCHAR2(30) := 'JTF';
1441
1442 retcode VARCHAR2(100);
1443 errbuf varchar2(3000);
1444
1445 BEGIN
1446
1447 l_dyn_csr := dbms_sql.open_cursor;
1448 dbms_sql.parse( l_dyn_csr
1449 , 'ALTER TABLE ' || l_schema_name || '.' || p_table_name || ' NOLOGGING '
1450 , dbms_sql.native );
1451 l_result := dbms_sql.execute(l_dyn_csr);
1452 dbms_sql.close_cursor(l_dyn_csr);
1453
1454 EXCEPTION
1455
1456 WHEN utl_file.invalid_path OR
1457 utl_file.invalid_mode OR
1458 utl_file.invalid_filehandle OR
1459 utl_file.invalid_operation OR
1460 utl_file.write_error THEN
1461
1462 RETCODE := 2;
1463 ERRBUF := 'JTF_TAE_CONTROL_PVT.set_table_nologging: [END] UTL_FILE: ' ||
1464 SQLERRM;
1465
1466 If G_Debug Then
1467 Write_Log(2, ERRBUF);
1468 End If;
1469
1470 RAISE;
1471
1472 WHEN OTHERS THEN
1473
1474 RETCODE := 2;
1475 ERRBUF := 'JTF_TAE_CONTROL_PVT.set_table_nologging: [END] OTHERS: ' ||
1476 SQLERRM;
1477
1478 If G_Debug Then
1479 Write_Log(2, ERRBUF);
1480 End If;
1481
1482 RAISE;
1483
1484 END set_table_nologging;
1485
1486 END JTF_TAE_CONTROL_PVT;