[Home] [Help]
PACKAGE BODY: APPS.JTY_TERR_DENORM_RULES_PVT
Source
1 PACKAGE BODY JTY_TERR_DENORM_RULES_PVT AS
2 /* $Header: jtfytdrb.pls 120.56.12020000.3 2012/09/24 10:29:45 swpoddar ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTY_TERR_DENORM_RULES_PVT
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- This package is used for the following prposes :
9 -- a) denormalize the territory hierarchy
10 -- b) denormalize the territory qualifier values
11 -- c) calculate absolute rank, number of qualifiers and qual relation product
12 --
13 -- Procedures:
14 -- (see below for specification)
15 --
16 -- NOTES
17 -- This package is publicly available for use
18 --
19 -- HISTORY
20 -- 06/13/05 ACHANDA CREATED
21 --
22 -- End of Comments
23 --
24
25 G_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
26 G_PROGRAM_APPL_ID NUMBER := FND_GLOBAL.PROG_APPL_ID();
27 G_PROGRAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
28 G_USER_ID NUMBER := FND_GLOBAL.USER_ID();
29 G_SYSDATE DATE := SYSDATE;
30 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
31 G_NEW_LINE VARCHAR2(02) := fnd_global.local_chr(10);
32
33 G_COMMIT_SIZE CONSTANT NUMBER := 20000;
34
35 PROCEDURE jty_log(p_log_level IN NUMBER
36 ,p_module IN VARCHAR2
37 ,p_message IN VARCHAR2)
38 IS
39 pragma autonomous_transaction;
40 BEGIN
41 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
42 FND_LOG.string(p_log_level, p_module, p_message);
43 commit;
44 END IF;
45 END;
46
47 PROCEDURE CREATE_DNMVAL_INDEX ( p_table_name IN VARCHAR2,
48 p_source_id IN NUMBER,
49 p_mode IN VARCHAR2,
50 x_Return_Status OUT NOCOPY VARCHAR2)
51 IS
52
53 i integer;
54 j integer;
55
56 v_statement varchar2(9000);
57 s_statement varchar2(4000);
58 i_statement varchar2(2000);
59
60 alter_statement varchar2(2000);
61
62 l_table_tablespace varchar2(100);
63 l_idx_tablespace varchar2(100);
64 l_ora_username varchar2(100);
65 l_dop NUMBER;
66
67 Cursor getProductList(cl_source_id number) IS
68 SELECT A.terr_values_idx_header_id,
69 A.index_name,
70 A.qual_usg_id
71 FROM jty_terr_values_idx_header A
72 WHERE A.source_id = p_source_id
73 AND A.build_index_flag = 'Y'
74 -- the condition below is necessary for incremental mode where the index may be already present
75 AND NOT EXISTS (
76 SELECT 1
77 FROM dba_indexes B
78 WHERE B.index_name = A.index_name
79 AND B.owner = l_ora_username)
80 ORDER BY A.index_name;
81
82 Cursor getFactorList(cl_tvhidpid number) IS
83 SELECT DISTINCT B.VALUES_COL_MAP, B.INPUT_SELECTIVITY
84 FROM jty_terr_values_idx_details B
85 WHERE B.terr_values_idx_header_id = cl_tvhidpid
86 AND B.values_col_map is not null
87 ORDER BY input_selectivity;
88
89 Cursor getDeaProductList(cl_source_id number) IS
90 SELECT A.dea_values_idx_header_id,
91 A.index_name
92 FROM jty_dea_values_idx_header A
93 WHERE A.source_id = p_source_id
94 AND A.build_index_flag = 'Y';
95
96 Cursor getDeaFactorList(cl_tvhidpid number) IS
97 SELECT DISTINCT B.VALUES_COL_MAP, B.INPUT_SELECTIVITY
98 FROM jty_dea_values_idx_details B
99 WHERE B.dea_values_idx_header_id = cl_tvhidpid
100 AND B.values_col_map is not null
101 ORDER BY input_selectivity;
102
103 Cursor getDropIndexCandidates(cl_source_id in number, cl_owner in varchar2) IS
104 SELECT A.index_name,
105 B.owner
106 FROM jty_terr_values_idx_header A,
107 dba_indexes B
108 WHERE A.source_id = p_source_id
109 AND A.build_index_flag = 'N'
110 AND A.index_name = B.index_name
111 AND B.owner = cl_owner;
112
113 Cursor getDeaDropIndexCandidates(cl_source_id in number, cl_owner in varchar2) IS
114 SELECT A.index_name,
115 B.owner
116 FROM jty_dea_values_idx_header A,
117 dba_indexes B
118 WHERE A.source_id = p_source_id
119 AND A.build_index_flag = 'N'
120 AND A.index_name = B.index_name
121 AND B.owner = cl_owner;
122
123 BEGIN
124 -- debug message
125 jty_log(FND_LOG.LEVEL_PROCEDURE,
126 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.begin',
127 'Start of the procedure jty_terr_denorm_rules_pvt.create_dnmval_index ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
128
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130
131 /* In incremental mode mark all the qualifier combination present */
132 /* in jtf_tae_qual_products as being used by active territories */
133 IF (p_mode = 'INCREMENTAL') THEN
134 UPDATE jty_terr_values_idx_header a
135 SET a.delete_flag = 'N'
136 WHERE a.source_id = p_source_id
137 AND a.delete_flag = 'Y'
138 AND a.relation_product in (
139 SELECT relation_product
140 FROM jtf_tae_qual_products
141 WHERE source_id = p_source_id );
142 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
143 UPDATE jty_dea_values_idx_header a
144 SET a.delete_flag = 'N'
145 WHERE a.source_id = p_source_id
146 AND a.delete_flag = 'Y'
147 AND a.relation_product in (
148 SELECT attr_relation_product
149 FROM jty_dea_attr_products
150 WHERE source_id = p_source_id );
151 END IF;
152
153 /* Calculate the selectivity of the columns in the denorm value table */
154 jty_tae_index_creation_pvt.SELECTIVITY(p_TABLE_NAME => p_table_name,
155 p_mode => p_mode,
156 p_source_id => p_source_id,
157 x_return_status => x_return_status);
158
159 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
160 jty_log(FND_LOG.LEVEL_EXCEPTION,
161 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.selectivity',
162 'API jty_tae_index_creation_pvt.SELECTIVITY has failed');
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 /* Determine the indexes that need to be created */
167 IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
168 JTY_TAE_CONTROL_PVT.reduce_dnmval_idx_set (
169 p_source_id => p_source_id,
170 p_mode => p_mode,
171 x_Return_Status => x_return_status );
172 ELSIF ((p_mode = 'DATE EFFECTIVE') or (p_mode = 'DEA INCREMENTAL')) THEN
173 JTY_TAE_CONTROL_PVT.reduce_deaval_idx_set (
174 p_source_id => p_source_id,
175 p_mode => p_mode,
176 x_Return_Status => x_return_status );
177 END IF;
178
179 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
180 jty_log(FND_LOG.LEVEL_EXCEPTION,
181 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.reduce_dnmval_idx_set',
182 'API JTY_TAE_CONTROL_PVT.reduce_dnmval_idx_set has failed');
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 /* get default Degree of Parallelism */
187 SELECT MIN(TO_NUMBER(v.value))
188 INTO l_dop
189 FROM v$parameter v
190 WHERE v.name = 'parallel_max_servers'
191 OR v.name = 'cpu_count';
192
193 -- debug message
194 jty_log(FND_LOG.LEVEL_STATEMENT,
195 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.l_dop',
196 'Default degree of parallelism : ' || l_dop);
197
198 /* get tablespace information */
199 SELECT i.tablespace, i.index_tablespace, u.oracle_username
200 INTO l_table_tablespace, l_idx_tablespace, l_ora_username
201 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
202 WHERE a.application_short_name = 'JTF'
203 AND a.application_id = i.application_id
204 AND u.oracle_id = i.oracle_id;
205
206 -- debug message
207 jty_log(FND_LOG.LEVEL_STATEMENT,
208 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.tablespace',
209 'Table tablespace : ' || l_table_tablespace || ' Index tablespace : ' || l_idx_tablespace ||
210 ' Schema Name : ' || l_ora_username);
211
212 -- default INDEX STORAGE parameters
213 s_statement := s_statement || ' TABLESPACE ' || l_idx_tablespace ;
214 s_statement := s_statement || ' STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED ';
215 s_statement := s_statement || ' PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) ';
216 s_statement := s_statement || ' PCTFREE 10 INITRANS 10 MAXTRANS 255 ';
217 s_statement := s_statement || ' COMPUTE STATISTICS ';
218 s_statement := s_statement || ' NOLOGGING PARALLEL ' || l_dop;
219
220 /* Create Qualifier Combination Dynamic Indexes */
221 IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
222 FOR prd IN getProductList(p_source_id) LOOP
223
224 /*Changes done by swati for bug 12722552 */
225 -- v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || prd.index_name || ' ON ' || p_table_name || '( ';
226 v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || prd.index_name || ' ON ' || l_ora_username ||'.' ||p_table_name || '( ';
227 /*End of changes*/
228
229 j:=1;
230 i_statement := null;
231
232 -- for each factor of product
233 FOR factor IN getFactorList(prd.terr_values_idx_header_id) LOOP
234
235 IF j<>1 THEN
236 i_statement := i_statement || ',' ;
237 END IF;
238 i_statement := i_statement || factor.VALUES_COL_MAP;
239 j:=j+1;
240 END LOOP; /* end loop FOR factor IN getFactorList */
241
242 IF (j > 1) THEN
243 IF nvl(prd.qual_usg_id, -1) <> -1041 THEN --Bug 7645026
244 v_statement := v_statement || i_statement || ',source_id, trans_type_id, start_date, end_date, terr_id) ';
245 ELSE
246 v_statement := v_statement || 'source_id, trans_type_id,' || i_statement ||',start_date, end_date, terr_id)';
247 END IF;
248
249 /* Append Storage Parameter Information to Index Definition */
250 v_statement := v_statement || s_statement;
251
252 -- debug message
253 jty_log(FND_LOG.LEVEL_STATEMENT,
254 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.index_creation',
255 'Index created with the statement : ' || v_statement);
256 DECLARE --Bug 7645026
257 duplicate_index EXCEPTION;
258 pragma EXCEPTION_INIT(duplicate_index, -1408);
259 BEGIN
260 EXECUTE IMMEDIATE v_statement;
261 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || prd.index_name || ' NOPARALLEL';
262 EXECUTE IMMEDIATE alter_statement;
263 EXCEPTION
264 WHEN duplicate_index THEN
265 UPDATE jty_terr_values_idx_header
266 SET BUILD_INDEX_FLAG = 'N'
267 WHERE terr_values_idx_header_id = prd.terr_values_idx_header_id;
268 END;
269 END IF; /* end IF (j > 1) */
270
271 END LOOP; /* end loop FOR prd IN getProductList */
272
273 FOR idx IN getDropIndexCandidates(p_source_id, l_ora_username) LOOP
274 v_statement := 'DROP INDEX ' || idx.owner || '.' || idx.index_name;
275
276 BEGIN
277 EXECUTE IMMEDIATE v_statement;
278 EXCEPTION
279 WHEN OTHERS THEN NULL;
280 END;
281
282 END LOOP;
283
284 IF (p_mode = 'INCREMENTAL') THEN
285 DELETE jty_terr_values_idx_details dtl
286 WHERE EXISTS (
287 SELECT 1
288 FROM jty_terr_values_idx_header hdr
289 WHERE dtl.terr_values_idx_header_id = hdr.terr_values_idx_header_id
290 AND hdr.source_id = p_source_id
291 AND hdr.delete_flag = 'Y');
292
293 DELETE jty_terr_values_idx_header hdr
294 WHERE hdr.source_id = p_source_id
295 AND hdr.delete_flag = 'Y';
296
297 END IF;
298
299 ELSIF ((p_mode = 'DATE EFFECTIVE') or (p_mode = 'DEA INCREMENTAL')) THEN
300 FOR prd IN getDeaProductList(p_source_id) LOOP
301
302 /*Changes done by swati for bug 12722552 */
303 -- v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || prd.index_name || ' ON ' || p_table_name || '( ';
304 v_statement := 'CREATE INDEX '|| l_ora_username ||'.' || prd.index_name || ' ON ' || l_ora_username ||'.' ||p_table_name || '( ';
305 /*End of changes*/
306
307 j:=1;
308 -- for each factor of product
309 FOR factor IN getDeaFactorList(prd.dea_values_idx_header_id) LOOP
310
311 IF j<>1 THEN
312 v_statement := v_statement || ',' ;
313 END IF;
314 v_statement := v_statement || factor.VALUES_COL_MAP;
315 j:=j+1;
316 END LOOP; /* end loop FOR factor IN getDeaFactorList */
317
318 IF (j > 1) THEN
319 v_statement := v_statement || ',source_id, trans_type_id, start_date, end_date, terr_id) ';
320 /* Append Storage Parameter Information to Index Definition */
321 v_statement := v_statement || s_statement;
322
323 -- debug message
324 jty_log(FND_LOG.LEVEL_STATEMENT,
325 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.index_creation dea incr',
326 'Index created with the statement : ' || v_statement);
327 DECLARE --Bug 7645026
328 duplicate_index EXCEPTION;
329 index_exists EXCEPTION;
330 pragma EXCEPTION_INIT(duplicate_index, -1408);
331 pragma EXCEPTION_INIT(index_exists, -955);
332 BEGIN
333 EXECUTE IMMEDIATE v_statement;
334 alter_statement := 'ALTER INDEX ' || l_ora_username || '.' || prd.index_name || ' NOPARALLEL';
335 EXECUTE IMMEDIATE alter_statement;
336 EXCEPTION
337 WHEN duplicate_index THEN
338 UPDATE jty_dea_values_idx_header
339 SET BUILD_INDEX_FLAG = 'N'
340 WHERE dea_values_idx_header_id = prd.dea_values_idx_header_id;
341 WHEN index_exists THEN
342 jty_log(FND_LOG.LEVEL_STATEMENT,
343 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.index_creation dea incr',
344 'Index '|| prd.index_name ||'already exists and not created again');
345
346 END;
347 END IF; /* end IF (j > 1) */
348
349 END LOOP; /* end loop FOR prd IN getDeaProductList */
350
351 FOR idx IN getDeaDropIndexCandidates(p_source_id, l_ora_username) LOOP
352 v_statement := 'DROP INDEX ' || idx.owner || '.' || idx.index_name;
353
354 BEGIN
355 EXECUTE IMMEDIATE v_statement;
356 EXCEPTION
357 WHEN OTHERS THEN NULL;
358 END;
359
360 END LOOP;
361
362 IF (p_mode = 'DEA INCREMENTAL') THEN
363 DELETE jty_dea_values_idx_details dtl
364 WHERE EXISTS (
365 SELECT 1
366 FROM jty_dea_values_idx_header hdr
367 WHERE dtl.dea_values_idx_header_id = hdr.dea_values_idx_header_id
368 AND hdr.source_id = p_source_id
369 AND hdr.delete_flag = 'Y');
370
371 DELETE jty_dea_values_idx_header hdr
372 WHERE hdr.source_id = p_source_id
373 AND hdr.delete_flag = 'Y';
374
375 END IF;
376 END IF; /* end IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) */
377
378 -- debug message
379 jty_log(FND_LOG.LEVEL_PROCEDURE,
380 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.end',
381 'End of the procedure jty_terr_denorm_rules_pvt.create_dnmval_index ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
382
383 EXCEPTION
384 WHEN NO_DATA_FOUND THEN
385 x_return_status := FND_API.G_RET_STS_ERROR ;
386 jty_log(FND_LOG.LEVEL_EXCEPTION,
387 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.no_data_found',
388 'API jty_terr_denorm_rules_pvt.create_dnmval_index has failed with no_data_found');
389
390 WHEN FND_API.G_EXC_ERROR THEN
391 x_return_status := FND_API.G_RET_STS_ERROR ;
392 jty_log(FND_LOG.LEVEL_EXCEPTION,
393 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.g_exc_error',
394 'jty_terr_denorm_rules_pvt.create_dnmval_index has failed with G_EXC_ERROR exception');
395
396 WHEN OTHERS THEN
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
398 jty_log(FND_LOG.LEVEL_EXCEPTION,
399 'jtf.plsql.jty_terr_denorm_rules_pvt.create_dnmval_index.others',
400 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
401
402 END CREATE_DNMVAL_INDEX;
403
404 /* This function returns the level of the territory from root (terr_id = 1) */
405 FUNCTION get_level_from_root(p_terr_id IN number) RETURN NUMBER IS
406
407 l_level NUMBER;
408
409 BEGIN
410
411 l_level := 0;
412
413 IF (p_terr_id = 1) THEN
414 RETURN 1;
415 END IF;
416
417 select max(level)
418 into l_level
419 from jtf_terr_all
420 START WITH terr_id = p_terr_id
421 CONNECT BY PRIOR parent_territory_id = terr_id AND terr_id <> 1;
422
423 RETURN (l_level+1);
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 return 1;
428 END get_level_from_root;
429
430 /* This procedure inserts the denormalized territory hierarchy informations */
431 /* into the tables jtf_terr_denorm_rules_all, for total and incremental mode */
432 /* and the table jty_denorm_dea_rules_all for date effective mode */
433 PROCEDURE update_denorm_table (
434 p_source_id IN NUMBER,
435 p_mode IN VARCHAR2,
436 p_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
437 p_related_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
438 p_top_level_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
439 p_num_winners_tbl IN OUT NOCOPY jtf_terr_number_list,
440 p_level_from_root_tbl IN OUT NOCOPY jtf_terr_number_list,
441 p_level_from_parent_tbl IN OUT NOCOPY jtf_terr_number_list,
442 p_terr_rank_tbl IN OUT NOCOPY jtf_terr_number_list,
443 p_immediate_parent_flag_tbl IN OUT NOCOPY jtf_terr_char_1list,
444 p_org_id_tbl IN OUT NOCOPY jtf_terr_number_list,
445 p_start_date_tbl IN OUT NOCOPY jtf_terr_date_list,
446 p_end_date_tbl IN OUT NOCOPY jtf_terr_date_list,
447 errbuf OUT NOCOPY VARCHAR2,
448 retcode OUT NOCOPY VARCHAR2)
449 IS
450
451 l_no_of_records NUMBER;
452 BEGIN
453 -- debug message
454 jty_log(FND_LOG.LEVEL_PROCEDURE,
455 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.start',
456 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.update_denorm_table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
457
458 l_no_of_records := p_terr_id_tbl.COUNT;
459
460 IF (l_no_of_records > 0) THEN
461 /* if mode is total or incremental, insert the denormalized */
462 /* hierarchy information into jtf_terr_denorm_rules_all */
463 IF (p_mode IN ('TOTAL', 'INCREMENTAL')) THEN
464 FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
465 INSERT INTO jtf_terr_denorm_rules_all(
466 source_id
467 , qual_type_id
468 , terr_id
469 , rank
470 , level_from_root
471 , level_from_parent
472 , related_terr_id
473 , top_level_terr_id
474 , num_winners
475 , immediate_parent_flag
476 , start_date
477 , end_date
478 , LAST_UPDATE_DATE
479 , LAST_UPDATED_BY
480 , CREATION_DATE
481 , CREATED_BY
482 , LAST_UPDATE_LOGIN
483 , REQUEST_ID
484 , PROGRAM_APPLICATION_ID
485 , PROGRAM_ID
486 , PROGRAM_UPDATE_DATE
487 , ORG_ID
488 , RESOURCE_EXISTS_FLAG
489 -- , absolute_rank
490 )
491 VALUES (
492 p_source_id
493 , -1
494 , p_terr_id_tbl(i)
495 , p_terr_rank_tbl(i)
496 , p_level_from_root_tbl(i)
497 , p_level_from_parent_tbl(i)
498 , p_related_terr_id_tbl(i)
499 , p_top_level_terr_id_tbl(i)
500 , p_num_winners_tbl(i)
501 , p_immediate_parent_flag_tbl(i)
502 , p_start_date_tbl(i)
503 , p_end_date_tbl(i)
504 , G_SYSDATE
505 , G_USER_ID
506 , G_SYSDATE
507 , G_USER_ID
508 , G_USER_ID
509 , G_REQUEST_ID
510 , G_PROGRAM_APPL_ID
511 , G_PROGRAM_ID
512 , G_SYSDATE
513 , p_org_id_tbl(i)
514 , 'N'
515 -- , (SELECT absolute_rank from jtf_terr_all where terr_id = p_terr_id_tbl(i))
516 );
517
518 -- debug message
519 jty_log(FND_LOG.LEVEL_STATEMENT,
520 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.num_rows_inserted',
521 'Number of records inserted into jtf_terr_denorm_rules_all : ' || l_no_of_records);
522 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
523 /* if mode is date effective incremental , insert the denormalized */
524 /* hierarchy information into jty_denorm_dea_rules_all */
525 FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
526 INSERT INTO jty_denorm_dea_rules_all(
527 source_id
528 , terr_id
529 , rank
530 , level_from_root
531 , level_from_parent
532 , related_terr_id
533 , top_level_terr_id
534 , num_winners
535 , immediate_parent_flag
536 , start_date
537 , end_date
538 , LAST_UPDATE_DATE
539 , LAST_UPDATED_BY
540 , CREATION_DATE
541 , CREATED_BY
542 , LAST_UPDATE_LOGIN
543 , REQUEST_ID
544 , PROGRAM_APPLICATION_ID
545 , PROGRAM_ID
546 , PROGRAM_UPDATE_DATE
547 , ORG_ID
548 --, absolute_rank
549 )
550 VALUES (
551 p_source_id
552 , p_terr_id_tbl(i)
553 , p_terr_rank_tbl(i)
554 , p_level_from_root_tbl(i)
555 , p_level_from_parent_tbl(i)
556 , p_related_terr_id_tbl(i)
557 , p_top_level_terr_id_tbl(i)
558 , p_num_winners_tbl(i)
559 , p_immediate_parent_flag_tbl(i)
560 , p_start_date_tbl(i)
561 , p_end_date_tbl(i)
562 , G_SYSDATE
563 , G_USER_ID
564 , G_SYSDATE
565 , G_USER_ID
566 , G_USER_ID
567 , G_REQUEST_ID
568 , G_PROGRAM_APPL_ID
569 , G_PROGRAM_ID
570 , G_SYSDATE
571 , p_org_id_tbl(i)
572 -- , (SELECT absolute_rank from jtf_terr_all where terr_id = p_terr_id_tbl(i))
573 );
574
575 -- debug message
576 jty_log(FND_LOG.LEVEL_STATEMENT,
577 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.num_rows_inserted',
578 'Number of records inserted into jty_denorm_dea_rules_all : ' || l_no_of_records);
579
580 ELSE
581 /* if mode is date effective, insert the denormalized */
582 /* hierarchy information into jty_denorm_dea_rules_all */
583 FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
584 INSERT INTO jty_denorm_dea_rules_all(
585 source_id
586 , terr_id
587 , rank
588 , level_from_root
589 , level_from_parent
590 , related_terr_id
591 , top_level_terr_id
592 , num_winners
593 , immediate_parent_flag
594 , start_date
595 , end_date
596 , LAST_UPDATE_DATE
597 , LAST_UPDATED_BY
598 , CREATION_DATE
599 , CREATED_BY
600 , LAST_UPDATE_LOGIN
601 , REQUEST_ID
602 , PROGRAM_APPLICATION_ID
603 , PROGRAM_ID
604 , PROGRAM_UPDATE_DATE
605 , ORG_ID
606 --, absolute_rank
607 )
608 VALUES (
609 p_source_id
610 , p_terr_id_tbl(i)
611 , p_terr_rank_tbl(i)
612 , p_level_from_root_tbl(i)
613 , p_level_from_parent_tbl(i)
614 , p_related_terr_id_tbl(i)
615 , p_top_level_terr_id_tbl(i)
616 , p_num_winners_tbl(i)
617 , p_immediate_parent_flag_tbl(i)
618 , p_start_date_tbl(i)
619 , p_end_date_tbl(i)
620 , G_SYSDATE
621 , G_USER_ID
622 , G_SYSDATE
623 , G_USER_ID
624 , G_USER_ID
625 , G_REQUEST_ID
626 , G_PROGRAM_APPL_ID
627 , G_PROGRAM_ID
628 , G_SYSDATE
629 , p_org_id_tbl(i)
630 -- , (SELECT absolute_rank from jtf_terr_all where terr_id = p_terr_id_tbl(i))
631 );
632
633 -- debug message
634 jty_log(FND_LOG.LEVEL_STATEMENT,
635 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.num_rows_inserted',
636 'Number of records inserted into jty_denorm_dea_rules_all : ' || l_no_of_records);
637
638 END IF; /* end IF (p_mode IN ('TOTAL', 'INCREMENTAL')) */
639
640 END IF; /* end IF (l_no_of_records > 0) */
641
642 -- debug message
643 jty_log(FND_LOG.LEVEL_PROCEDURE,
644 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.end',
645 'End of the procedure JTY_TERR_DENORM_RULES_PVT.update_denorm_table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
646
647 retcode := 0;
648 errbuf := null;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 RETCODE := 2;
653 ERRBUF := SQLCODE || ' : ' || SQLERRM;
654 jty_log(FND_LOG.LEVEL_EXCEPTION,
655 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_denorm_table.others',
656 substr(errbuf, 1, 4000));
657
658 END update_denorm_table;
659
660 /* This procedure updates the relative rank in the table jtf_terr_all */
661 PROCEDURE update_relative_rank (
662 p_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
663 p_relative_rank_tbl IN OUT NOCOPY jtf_terr_number_list,
664 errbuf OUT NOCOPY VARCHAR2,
665 retcode OUT NOCOPY VARCHAR2)
666 IS
667
668 l_no_of_records NUMBER;
669 BEGIN
670 -- debug message
671 jty_log(FND_LOG.LEVEL_PROCEDURE,
672 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.start',
673 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.update_relative_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
674
675 l_no_of_records := p_terr_id_tbl.COUNT;
676
677 /* update the relative rank of the territory in jtf_terr_all */
678 IF (l_no_of_records > 0) THEN
679
680 /* disable the trigger before update */
681 BEGIN
682 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD DISABLE';
683 EXCEPTION
684 WHEN OTHERS THEN
685 NULL;
686 END;
687
688 FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
689 UPDATE jtf_terr_all
690 SET relative_rank = p_relative_rank_tbl(i)
691 WHERE terr_id = p_terr_id_tbl(i);
692
693 /* enable the trigger after update */
694 BEGIN
695 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD ENABLE';
696 EXCEPTION
697 WHEN OTHERS THEN
698 NULL;
699 END;
700
701 -- debug message
702 jty_log(FND_LOG.LEVEL_STATEMENT,
703 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.num_rows_updated',
704 'Number of records updated in jtf_terr_all for relative rank : ' || l_no_of_records);
705
706 END IF; /* end IF (l_no_of_records > 0) */
707
708 -- debug message
709 jty_log(FND_LOG.LEVEL_PROCEDURE,
710 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.end',
711 'End of the procedure JTY_TERR_DENORM_RULES_PVT.update_relative_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
712
713 retcode := 0;
714 errbuf := null;
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 RETCODE := 2;
719 ERRBUF := SQLCODE || ' : ' || SQLERRM;
720 jty_log(FND_LOG.LEVEL_EXCEPTION,
721 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_relative_rank.others',
722 substr(errbuf, 1, 4000));
723 END update_relative_rank;
724
725 /* This procedure updates the absolute rank in the table jtf_terr_all */
726 PROCEDURE update_absolute_rank (
727 p_terr_id_tbl IN OUT NOCOPY jtf_terr_number_list,
728 p_mode IN VARCHAR2,
729 p_table_name IN VARCHAR2,
730 errbuf OUT NOCOPY VARCHAR2,
731 retcode OUT NOCOPY VARCHAR2)
732 IS
733 l_dyn_str VARCHAR2(1000);
734 l_no_of_records NUMBER;
735 BEGIN
736 -- debug message
737 jty_log(FND_LOG.LEVEL_PROCEDURE,
738 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.start',
739 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.update_absolute_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
740
741 l_no_of_records := p_terr_id_tbl.COUNT;
742
743 /* update the relative rank of the territory in jtf_terr_all */
744 IF (l_no_of_records > 0) THEN
745
746 /* disable the trigger before update */
747 BEGIN
748 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD DISABLE';
749 EXCEPTION
750 WHEN OTHERS THEN
751 NULL;
752 END;
753
754 /* calculate the absolute rank */
755 FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
756 UPDATE jtf_terr_all jta1
757 SET jta1.ABSOLUTE_RANK = (
758 SELECT SUM(jta2.relative_rank)
759 FROM jtf_terr_all jta2
760 WHERE jta2.terr_id IN (
761 SELECT jt.terr_id
762 FROM jtf_terr_all jt
763 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
764 START WITH jt.terr_id = p_terr_id_tbl(i))),
765 jta1.last_update_date = g_sysdate
766 WHERE jta1.terr_id = p_terr_id_tbl(i);
767
768 l_dyn_str :=
769 'UPDATE ' || p_table_name || ' ' ||
770 'SET absolute_rank = ( ' ||
771 ' SELECT absolute_rank ' ||
772 ' FROM jtf_terr_all ' ||
773 ' WHERE terr_id = :1 ) ' ||
774 'WHERE terr_id = :2 ';
775
776 IF (p_mode = 'INCREMENTAL') THEN
777 FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
778 EXECUTE IMMEDIATE l_dyn_str USING p_terr_id_tbl(i), p_terr_id_tbl(i);
779 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
780 FORALL i IN p_terr_id_tbl.FIRST .. p_terr_id_tbl.LAST
781 EXECUTE IMMEDIATE l_dyn_str USING p_terr_id_tbl(i), p_terr_id_tbl(i);
782 END IF;
783
784 /* enable the trigger after update */
785 BEGIN
786 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD ENABLE';
787 EXCEPTION
788 WHEN OTHERS THEN
789 NULL;
790 END;
791
792 -- debug message
793 jty_log(FND_LOG.LEVEL_STATEMENT,
794 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.num_rows_updated',
795 'Number of records updated in jtf_terr_all for absolute rank : ' || l_no_of_records);
796
797 END IF; /* end IF (l_no_of_records > 0) */
798
799 -- debug message
800 jty_log(FND_LOG.LEVEL_PROCEDURE,
801 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.end',
802 'End of the procedure JTY_TERR_DENORM_RULES_PVT.update_absolute_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
803
804 retcode := 0;
805 errbuf := null;
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 RETCODE := 2;
810 ERRBUF := SQLCODE || ' : ' || SQLERRM;
811 jty_log(FND_LOG.LEVEL_EXCEPTION,
812 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.update_absolute_rank.others',
813 substr(errbuf, 1, 4000));
814 END update_absolute_rank;
815
816 /* This procedure updates the denormalized territory qualifier value informations */
817 PROCEDURE process_attr_values (
818 p_source_id IN NUMBER,
819 p_mode IN VARCHAR2,
820 p_table_name IN VARCHAR2,
821 p_terr_change_tab IN JTY_TERR_ENGINE_GEN_PVT.terr_change_type,
822 errbuf OUT NOCOPY VARCHAR2,
823 retcode OUT NOCOPY VARCHAR2 )
824 IS
825
826 CURSOR c_qual_types(cl_source_id in number, cl_terr_id in number) IS
827 SELECT a.qual_type_id
828 FROM jtf_qual_type_usgs_all a,
829 jtf_terr_qtype_usgs_all b
830 WHERE b.terr_id = cl_terr_id
831 AND b.qual_type_usg_id = a.qual_type_usg_id
832 AND a.source_id = cl_source_id;
833
834 CURSOR c_terr_qual_values(cl_terr_id in number, cl_source_id in number, cl_qual_type_id in number) IS
835 SELECT jtqa.qual_usg_id,
836 nvl(jqua.qual_relation_factor, 1),
837 jtva.comparison_operator,
838 jtva.low_value_char_id,
839 decode(cl_source_id, -1001, upper(jtva.low_value_char), -1600, upper(jtva.low_value_char), jtva.low_value_char),
840 decode(cl_source_id, -1001, upper(jtva.high_value_char), -1600, upper(jtva.high_value_char), jtva.high_value_char),
841 jtva.low_value_number,
842 jtva.high_value_number,
843 jtva.interest_type_id,
844 jtva.primary_interest_code_id,
845 jtva.secondary_interest_code_id,
846 jtva.currency_code,
847 jtva.value1_id,
848 jtva.value2_id,
849 jtva.value3_id,
850 jtva.value4_id,
851 jtva.first_char,
852 jqua.update_attr_val_stmt,
853 jqua.insert_attr_val_stmt,
854 jtdr.top_level_terr_id,
855 jta.absolute_rank,
856 jtdr.start_date,
857 jtdr.end_date,
858 count(*) over(partition by jtqa.qual_usg_id)
859 FROM jtf_terr_all jta,
860 jtf_terr_denorm_rules_all jtdr,
861 jtf_terr_qual_all jtqa,
862 jtf_terr_values_all jtva,
863 jtf_qual_usgs_all jqua,
864 jtf_qual_type_usgs_all jqtu,
865 jtf_qual_type_denorm_v inlv
866 WHERE jta.terr_id = cl_terr_id
867 AND jtdr.terr_id = jta.terr_id
868 AND jtdr.related_terr_id = jtqa.terr_id
869 AND jtdr.source_id = cl_source_id
870 AND jtqa.terr_qual_id = jtva.terr_qual_id
871 AND jtqa.qual_usg_id = jqua.qual_usg_id
872 AND jqua.org_id = -3113
873 AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
874 AND jqtu.source_id = cl_source_id
875 AND jqtu.qual_type_id = inlv.related_id
876 AND inlv.qual_type_id = cl_qual_type_id
877 AND jtqa.qual_usg_id <> -1102 -- eliminate CNRG
878 AND EXISTS
879 (SELECT 1
880 FROM jtf_terr_rsc_all jtr,
881 jtf_terr_rsc_access_all jtra,
882 jtf_qual_types_all jqta
883 WHERE jtr.terr_id = jta.terr_id
884 AND jtr.end_date_active >= sysdate
885 AND jtr.start_date_active <= sysdate
886 AND jtr.resource_type <> 'RS_ROLE'
887 AND jtr.terr_rsc_id = jtra.terr_rsc_id
888 AND jtra.access_type = jqta.name
889 AND jqta.qual_type_id = cl_qual_type_id
890 AND jtra.trans_access_code <> 'NONE')
891 UNION ALL
892 SELECT jtqa.qual_usg_id,
893 nvl(jqua.qual_relation_factor, 1),
894 cnrgv.comparison_operator,
895 null,
896 upper(cnrgv.low_value_char),
897 upper(cnrgv.high_value_char),
898 null,
899 null,
900 null,
901 null,
902 null,
903 null,
904 null,
905 null,
906 null,
907 null,
908 CAST( SUBSTR(UPPER(cnrgv.low_value_char), 1, 1) AS VARCHAR2(3) ),
909 jqua.update_attr_val_stmt,
910 jqua.insert_attr_val_stmt,
911 jtdr.top_level_terr_id,
912 jta.absolute_rank,
913 jtdr.start_date,
914 jtdr.end_date,
915 count(*) over(partition by jtqa.qual_usg_id)
916 FROM jtf_terr_all jta,
917 jtf_terr_denorm_rules_all jtdr,
918 jtf_terr_qual_all jtqa,
919 jtf_terr_values_all jtva,
920 jtf_qual_usgs_all jqua,
921 jtf_qual_type_usgs_all jqtu,
922 jtf_qual_type_denorm_v inlv,
923 jtf_terr_cnr_groups cnrg,
924 jtf_terr_cnr_group_values cnrgv
925 WHERE jta.terr_id = cl_terr_id
926 AND jtdr.terr_id = jta.terr_id
927 AND jtdr.related_terr_id = jtqa.terr_id
928 AND jtdr.source_id = cl_source_id
929 AND jtqa.terr_qual_id = jtva.terr_qual_id
930 AND jtqa.qual_usg_id = jqua.qual_usg_id
931 AND jqua.org_id = -3113
932 AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
933 AND jqtu.source_id = cl_source_id
934 AND jqtu.qual_type_id = inlv.related_id
935 AND inlv.qual_type_id = cl_qual_type_id
936 AND jtqa.qual_usg_id = -1102 -- include CNRG
937 AND cnrg.cnr_group_id = jtva.low_value_char_id
938 AND cnrg.cnr_group_id = cnrgv.cnr_group_id
939 AND EXISTS
940 (SELECT 1
941 FROM jtf_terr_rsc_all jtr,
942 jtf_terr_rsc_access_all jtra,
943 jtf_qual_types_all jqta
944 WHERE jtr.terr_id = jta.terr_id
945 AND jtr.end_date_active >= sysdate
946 AND jtr.start_date_active <= sysdate
947 AND jtr.resource_type <> 'RS_ROLE'
948 AND jtr.terr_rsc_id = jtra.terr_rsc_id
949 AND jtra.access_type = jqta.name
950 AND jqta.qual_type_id = cl_qual_type_id
951 AND jtra.trans_access_code <> 'NONE')
952 ORDER BY 1;
953
954 CURSOR c_terr_dea_qual_values(cl_terr_id in number, cl_source_id in number, cl_qual_type_id in number) IS
955 SELECT /*+ leading(JTA) index(JTA JTF_TERR_U1) */ jtqa.qual_usg_id,
956 nvl(jqua.qual_relation_factor, 1),
957 jtva.comparison_operator,
958 jtva.low_value_char_id,
959 decode(cl_source_id, -1001, upper(jtva.low_value_char), -1600, upper(jtva.low_value_char), jtva.low_value_char),
960 decode(cl_source_id, -1001, upper(jtva.high_value_char), -1600, upper(jtva.high_value_char), jtva.high_value_char),
961 jtva.low_value_number,
962 jtva.high_value_number,
963 jtva.interest_type_id,
964 jtva.primary_interest_code_id,
965 jtva.secondary_interest_code_id,
966 jtva.currency_code,
967 jtva.value1_id,
968 jtva.value2_id,
969 jtva.value3_id,
970 jtva.value4_id,
971 jtva.first_char,
972 jqua.update_attr_val_stmt,
973 jqua.insert_attr_val_stmt,
974 jtdr.top_level_terr_id,
975 jta.absolute_rank,
976 jtdr.start_date,
977 jtdr.end_date,
978 count(*) over(partition by jtqa.qual_usg_id)
979 FROM jtf_terr_all jta,
980 jty_denorm_dea_rules_all jtdr,
981 jtf_terr_qual_all jtqa,
982 jtf_terr_values_all jtva,
983 jtf_qual_usgs_all jqua,
984 jtf_qual_type_usgs_all jqtu,
985 jtf_qual_type_denorm_v inlv
986 WHERE jta.terr_id = cl_terr_id
987 AND jtdr.terr_id = jta.terr_id
988 AND jtdr.related_terr_id = jtqa.terr_id
989 AND jtqa.terr_qual_id = jtva.terr_qual_id
990 AND jtqa.qual_usg_id = jqua.qual_usg_id
991 AND jqua.org_id = -3113
992 AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
993 AND jqtu.source_id = cl_source_id
994 AND jqtu.qual_type_id = inlv.related_id
995 AND inlv.qual_type_id = cl_qual_type_id
996 AND jtqa.qual_usg_id <> -1102 -- eliminate CNRG
997 AND EXISTS
998 (SELECT 1
999 FROM jtf_terr_rsc_all jtr,
1000 jtf_terr_rsc_access_all jtra,
1001 jtf_qual_types_all jqta
1002 WHERE jtr.terr_id = jta.terr_id
1003 AND jtr.resource_type <> 'RS_ROLE'
1004 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1005 AND jtra.access_type = jqta.name
1006 AND jqta.qual_type_id + 0 = cl_qual_type_id
1007 AND jtra.trans_access_code <> 'NONE')
1008 UNION ALL
1009 SELECT /*+ leading(JTA) index(JTA JTF_TERR_U1) */ jtqa.qual_usg_id,
1010 nvl(jqua.qual_relation_factor, 1),
1011 cnrgv.comparison_operator,
1012 null,
1013 upper(cnrgv.low_value_char),
1014 upper(cnrgv.high_value_char),
1015 null,
1016 null,
1017 null,
1018 null,
1019 null,
1020 null,
1021 null,
1022 null,
1023 null,
1024 null,
1025 CAST( SUBSTR(UPPER(cnrgv.low_value_char), 1, 1) AS VARCHAR2(3) ),
1026 jqua.update_attr_val_stmt,
1027 jqua.insert_attr_val_stmt,
1028 jtdr.top_level_terr_id,
1029 jta.absolute_rank,
1030 jtdr.start_date,
1031 jtdr.end_date,
1032 count(*) over(partition by jtqa.qual_usg_id)
1033 FROM jtf_terr_all jta,
1034 jty_denorm_dea_rules_all jtdr,
1035 jtf_terr_qual_all jtqa,
1036 jtf_terr_values_all jtva,
1037 jtf_qual_usgs_all jqua,
1038 jtf_qual_type_usgs_all jqtu,
1039 jtf_qual_type_denorm_v inlv,
1040 jtf_terr_cnr_groups cnrg,
1041 jtf_terr_cnr_group_values cnrgv
1042 WHERE jta.terr_id = cl_terr_id
1043 AND jtdr.terr_id = jta.terr_id
1044 AND jtdr.related_terr_id = jtqa.terr_id
1045 AND jtqa.terr_qual_id = jtva.terr_qual_id
1046 AND jtqa.qual_usg_id = jqua.qual_usg_id
1047 AND jqua.org_id = -3113
1048 AND jqua.qual_type_usg_id = jqtu.qual_type_usg_id
1049 AND jqtu.source_id = cl_source_id
1050 AND jqtu.qual_type_id = inlv.related_id
1051 AND inlv.qual_type_id = cl_qual_type_id
1052 AND jtqa.qual_usg_id = -1102 -- include CNRG
1053 AND cnrg.cnr_group_id = jtva.low_value_char_id
1054 AND cnrg.cnr_group_id = cnrgv.cnr_group_id
1055 AND EXISTS
1056 (SELECT 1
1057 FROM jtf_terr_rsc_all jtr,
1058 jtf_terr_rsc_access_all jtra,
1059 jtf_qual_types_all jqta
1060 WHERE jtr.terr_id = jta.terr_id
1061 AND jtr.resource_type <> 'RS_ROLE'
1062 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1063 AND jtra.access_type = jqta.name
1064 AND jqta.qual_type_id + 0 = cl_qual_type_id
1065 AND jtra.trans_access_code <> 'NONE')
1066 ORDER BY 1;
1067
1068 CURSOR c_column_names(p_table_name IN VARCHAR2, p_owner IN VARCHAR2) is
1069 SELECT column_name
1070 FROM all_tab_columns
1071 WHERE table_name = p_table_name
1072 AND owner = p_owner
1073 AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'LAST_UPDATE_DATE',
1074 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
1075 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'DENORM_TERR_ATTR_VALUES_ID',
1076 'DENORM_TERR_DEA_VALUES_ID');
1077
1078
1079 CURSOR c_actual_tab_reference is
1080 SELECT COUNT(*)
1081 FROM JTF_TERR_QUAL_ALL jtqa, JTF_TERR_USGS_ALL jtua, JTF_TERR_ALL jta
1082 WHERE jtqa.QUAL_USG_ID NOT IN
1083 (-1040, -1041, -1042, -1044, -1744, -1734, -1048, -1096, -1038, -1061, -1060, -1043,
1084 -1095, -1051, -1050, -1037, -1210, -1045, -1046, -1206, -1213, -1039, -1218)
1085 AND JTQA.TERR_ID = JTUA.TERR_ID
1086 AND JTUA.SOURCE_ID = -1002
1087 AND JTA.TERR_ID = JTUA.TERR_ID
1088 AND SYSDATE BETWEEN JTA.START_DATE_ACTIVE AND JTA.END_DATE_ACTIVE;
1089
1090
1091 TYPE l_qtype_terr_id_tbl_type IS TABLE OF jtf_terr_all.terr_id%TYPE;
1092 TYPE l_qtype_trans_id_tbl_type IS TABLE OF jtf_qual_types_all.qual_type_id%TYPE;
1093 TYPE l_qtype_source_id_tbl_type IS TABLE OF jtf_sources_all.source_id%TYPE;
1094 TYPE l_qtype_num_qual_tbl_type IS TABLE OF jtf_terr_qtype_usgs_all.num_qual%TYPE;
1095 TYPE l_qtype_qual_prd_tbl_type IS TABLE OF jtf_terr_qtype_usgs_all.qual_relation_product%TYPE;
1096
1097 TYPE l_qual_type_id_tbl_type IS TABLE OF jtf_qual_type_usgs_all.qual_type_id%TYPE;
1098 TYPE l_qual_usg_id_tbl_type IS TABLE OF jtf_qual_usgs_all.qual_usg_id%TYPE;
1099 TYPE l_qual_rel_fac_tbl_type IS TABLE OF jtf_qual_usgs_all.qual_relation_factor%TYPE;
1100 TYPE l_cop_tbl_type IS TABLE OF jtf_terr_values_all.comparison_operator%TYPE;
1101 TYPE l_lvc_id_tbl_type IS TABLE OF jtf_terr_values_all.low_value_char_id%TYPE;
1102 TYPE l_lvc_tbl_type IS TABLE OF jtf_terr_values_all.low_value_char%TYPE;
1103 TYPE l_hvc_tbl_type IS TABLE OF jtf_terr_values_all.high_value_char%TYPE;
1104 TYPE l_lvn_tbl_type IS TABLE OF jtf_terr_values_all.low_value_number%TYPE;
1105 TYPE l_hvn_tbl_type IS TABLE OF jtf_terr_values_all.high_value_number%TYPE;
1106 TYPE l_it_id_tbl_type IS TABLE OF jtf_terr_values_all.interest_type_id%TYPE;
1107 TYPE l_pic_id_tbl_type IS TABLE OF jtf_terr_values_all.primary_interest_code_id%TYPE;
1108 TYPE l_sic_id_tbl_type IS TABLE OF jtf_terr_values_all.secondary_interest_code_id%TYPE;
1109 TYPE l_curr_tbl_type IS TABLE OF jtf_terr_values_all.currency_code%TYPE;
1110 TYPE l_value1_id_tbl_type IS TABLE OF jtf_terr_values_all.value1_id%TYPE;
1111 TYPE l_value2_id_tbl_type IS TABLE OF jtf_terr_values_all.value2_id%TYPE;
1112 TYPE l_value3_id_tbl_type IS TABLE OF jtf_terr_values_all.value3_id%TYPE;
1113 TYPE l_value4_id_tbl_type IS TABLE OF jtf_terr_values_all.value4_id%TYPE;
1114 TYPE l_fc_tbl_type IS TABLE OF jtf_terr_values_all.first_char%TYPE;
1115 TYPE l_update_stmt_tbl_type IS TABLE OF jtf_qual_usgs_all.update_attr_val_stmt%TYPE;
1116 TYPE l_insert_stmt_tbl_type IS TABLE OF jtf_qual_usgs_all.insert_attr_val_stmt%TYPE;
1117 TYPE l_top_lvl_terr_id_tbl_type IS TABLE OF jtf_terr_denorm_rules_all.top_level_terr_id%TYPE;
1118 TYPE l_abs_rank_tbl_type IS TABLE OF jtf_terr_denorm_rules_all.absolute_rank%TYPE;
1119 TYPE l_start_date_tbl_type IS TABLE OF jtf_terr_denorm_rules_all.start_date%TYPE;
1120 TYPE l_end_date_tbl_type IS TABLE OF jtf_terr_denorm_rules_all.end_date%TYPE;
1121 TYPE l_no_of_val_tbl_type IS TABLE OF NUMBER;
1122 TYPE l_rowid_tbl_type IS TABLE OF ROWID;
1123
1124 l_qual_type_id_tbl l_qual_type_id_tbl_type;
1125 l_qual_usg_id_tbl l_qual_usg_id_tbl_type;
1126 l_qual_rel_fac_tbl l_qual_rel_fac_tbl_type;
1127 l_cop_tbl l_cop_tbl_type;
1128 l_lvc_id_tbl l_lvc_id_tbl_type;
1129 l_lvc_tbl l_lvc_tbl_type;
1130 l_hvc_tbl l_hvc_tbl_type;
1131 l_lvn_tbl l_lvn_tbl_type;
1132 l_hvn_tbl l_hvn_tbl_type;
1133 l_it_id_tbl l_it_id_tbl_type;
1134 l_pic_id_tbl l_pic_id_tbl_type;
1135 l_sic_id_tbl l_sic_id_tbl_type;
1136 l_curr_tbl l_curr_tbl_type;
1137 l_value1_id_tbl l_value1_id_tbl_type;
1138 l_value2_id_tbl l_value2_id_tbl_type;
1139 l_value3_id_tbl l_value3_id_tbl_type;
1140 l_value4_id_tbl l_value4_id_tbl_type;
1141 l_fc_tbl l_fc_tbl_type;
1142 l_update_stmt_tbl l_update_stmt_tbl_type;
1143 l_insert_stmt_tbl l_insert_stmt_tbl_type;
1144 l_top_lvl_terr_id_tbl l_top_lvl_terr_id_tbl_type;
1145 l_abs_rank_tbl l_abs_rank_tbl_type;
1146 l_start_date_tbl l_start_date_tbl_type;
1147 l_end_date_tbl l_end_date_tbl_type;
1148 l_no_of_val_tbl l_no_of_val_tbl_type;
1149 l_rowid_tbl l_rowid_tbl_type;
1150
1151 l_qtype_terr_id_tbl l_qtype_terr_id_tbl_type;
1152 l_qtype_trans_id_tbl l_qtype_trans_id_tbl_type;
1153 l_qtype_source_id_tbl l_qtype_source_id_tbl_type;
1154 l_qtype_num_qual_tbl l_qtype_num_qual_tbl_type;
1155 l_qtype_qual_prd_tbl l_qtype_qual_prd_tbl_type;
1156
1157 l_num_qual NUMBER;
1158 l_qual_relation_product NUMBER;
1159 l_terr_qval_counter NUMBER;
1160 l_owner VARCHAR2(30);
1161 l_indent VARCHAR2(30);
1162 l_status VARCHAR2(30);
1163 l_industry VARCHAR2(30);
1164 first_time BOOLEAN;
1165 l_table_name VARCHAR2(30);
1166
1167 l_delete_stmt VARCHAR2(200);
1168 l_update_stmt VARCHAR2(3000);
1169 l_rowid_update_stmt VARCHAR2(3000);
1170 l_rowid_insert_stmt VARCHAR2(3000);
1171 l_insert_stmt VARCHAR2(10000);
1172 l_select_stmt VARCHAR2(10000);
1173
1174 x_return_status VARCHAR2(250);
1175
1176 L_SCHEMA_NOTFOUND EXCEPTION;
1177 l_actual_tab_reference NUMBER;
1178
1179 BEGIN
1180 -- debug message
1181 jty_log(FND_LOG.LEVEL_PROCEDURE,
1182 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.start',
1183 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.process_attr_values ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1184
1185 /* initialize the pl/sql tables */
1186 l_qtype_terr_id_tbl := l_qtype_terr_id_tbl_type();
1187 l_qtype_trans_id_tbl := l_qtype_trans_id_tbl_type();
1188 l_qtype_source_id_tbl := l_qtype_source_id_tbl_type();
1189 l_qtype_num_qual_tbl := l_qtype_num_qual_tbl_type();
1190 l_qtype_qual_prd_tbl := l_qtype_qual_prd_tbl_type();
1191
1192 l_table_name := p_table_name;
1193
1194 IF (p_terr_change_tab.terr_id.COUNT > 0) THEN
1195
1196 OPEN c_actual_tab_reference;
1197 FETCH c_actual_tab_reference INTO l_actual_tab_reference;
1198 CLOSE c_actual_tab_reference;
1199
1200 /* delete the old data from global temp table */
1201 DELETE jty_denorm_terr_attr_values_gt;
1202
1203 /* if mode is incremental, delete the old entries of the territory from the denorm table */
1204 IF (p_mode = 'INCREMENTAL') THEN
1205 l_delete_stmt := 'DELETE ' || l_table_name || ' where terr_id = :1 and :2 IN (''I'', ''D'') ';
1206
1207 FOR i IN p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST LOOP
1208 execute immediate l_delete_stmt USING p_terr_change_tab.terr_id(i), p_terr_change_tab.attr_processing_flag(i);
1209 END LOOP;
1210 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
1211 l_delete_stmt := 'DELETE ' || l_table_name || ' where terr_id = :1 and :2 IN (''I'', ''D'') ';
1212
1213 FOR i IN p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST LOOP
1214 execute immediate l_delete_stmt USING p_terr_change_tab.terr_id(i), p_terr_change_tab.attr_processing_flag(i);
1215 END LOOP;
1216 END IF;
1217
1218 FOR i IN p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST LOOP
1219
1220 IF (p_terr_change_tab.attr_processing_flag(i) IN ('I', 'D')) THEN
1221
1222 /* Get all the transaction types for the territory */
1223 OPEN c_qual_types(p_source_id, p_terr_change_tab.terr_id(i));
1224 FETCH c_qual_types BULK COLLECT INTO l_qual_type_id_tbl;
1225 CLOSE c_qual_types;
1226
1227 IF (l_qual_type_id_tbl.COUNT > 0) THEN
1228 /* for each transaction type calculate num_qual and qual_relation_product */
1229 /* and also denormalize the qualifier values for the territory */
1230 FOR j IN l_qual_type_id_tbl.FIRST .. l_qual_type_id_tbl.LAST LOOP
1231 l_num_qual := 0;
1232 l_qual_relation_product := 1;
1233
1234 /* Get all the qualifiers and their values of the territory */
1235 IF (p_mode = 'DATE EFFECTIVE') THEN
1236 OPEN c_terr_dea_qual_values(p_terr_change_tab.terr_id(i), p_source_id, l_qual_type_id_tbl(j));
1237 FETCH c_terr_dea_qual_values BULK COLLECT INTO
1238 l_qual_usg_id_tbl
1239 ,l_qual_rel_fac_tbl
1240 ,l_cop_tbl
1241 ,l_lvc_id_tbl
1242 ,l_lvc_tbl
1243 ,l_hvc_tbl
1244 ,l_lvn_tbl
1245 ,l_hvn_tbl
1246 ,l_it_id_tbl
1247 ,l_pic_id_tbl
1248 ,l_sic_id_tbl
1249 ,l_curr_tbl
1250 ,l_value1_id_tbl
1251 ,l_value2_id_tbl
1252 ,l_value3_id_tbl
1253 ,l_value4_id_tbl
1254 ,l_fc_tbl
1255 ,l_update_stmt_tbl
1256 ,l_insert_stmt_tbl
1257 ,l_top_lvl_terr_id_tbl
1258 ,l_abs_rank_tbl
1259 ,l_start_date_tbl
1260 ,l_end_date_tbl
1261 ,l_no_of_val_tbl;
1262 CLOSE c_terr_dea_qual_values;
1263 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
1264 OPEN c_terr_dea_qual_values(p_terr_change_tab.terr_id(i), p_source_id, l_qual_type_id_tbl(j));
1265 FETCH c_terr_dea_qual_values BULK COLLECT INTO
1266 l_qual_usg_id_tbl
1267 ,l_qual_rel_fac_tbl
1268 ,l_cop_tbl
1269 ,l_lvc_id_tbl
1270 ,l_lvc_tbl
1271 ,l_hvc_tbl
1272 ,l_lvn_tbl
1273 ,l_hvn_tbl
1274 ,l_it_id_tbl
1275 ,l_pic_id_tbl
1276 ,l_sic_id_tbl
1277 ,l_curr_tbl
1278 ,l_value1_id_tbl
1279 ,l_value2_id_tbl
1280 ,l_value3_id_tbl
1281 ,l_value4_id_tbl
1282 ,l_fc_tbl
1283 ,l_update_stmt_tbl
1284 ,l_insert_stmt_tbl
1285 ,l_top_lvl_terr_id_tbl
1286 ,l_abs_rank_tbl
1287 ,l_start_date_tbl
1288 ,l_end_date_tbl
1289 ,l_no_of_val_tbl;
1290 CLOSE c_terr_dea_qual_values;
1291 ELSE
1292 OPEN c_terr_qual_values(p_terr_change_tab.terr_id(i), p_source_id, l_qual_type_id_tbl(j));
1293 FETCH c_terr_qual_values BULK COLLECT INTO
1294 l_qual_usg_id_tbl
1295 ,l_qual_rel_fac_tbl
1296 ,l_cop_tbl
1297 ,l_lvc_id_tbl
1298 ,l_lvc_tbl
1299 ,l_hvc_tbl
1300 ,l_lvn_tbl
1301 ,l_hvn_tbl
1302 ,l_it_id_tbl
1303 ,l_pic_id_tbl
1304 ,l_sic_id_tbl
1305 ,l_curr_tbl
1306 ,l_value1_id_tbl
1307 ,l_value2_id_tbl
1308 ,l_value3_id_tbl
1309 ,l_value4_id_tbl
1310 ,l_fc_tbl
1311 ,l_update_stmt_tbl
1312 ,l_insert_stmt_tbl
1313 ,l_top_lvl_terr_id_tbl
1314 ,l_abs_rank_tbl
1315 ,l_start_date_tbl
1316 ,l_end_date_tbl
1317 ,l_no_of_val_tbl;
1318 CLOSE c_terr_qual_values;
1319 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
1320
1321 IF (l_qual_usg_id_tbl.COUNT > 0) THEN
1322 l_terr_qval_counter := l_qual_usg_id_tbl.FIRST;
1323
1324 /* process each qualifier of the territory for the transaction type */
1325 WHILE l_terr_qval_counter IS NOT NULL LOOP
1326 /* for each qualifier, calcualte number of qualifiers and qual relation product */
1327 l_num_qual := l_num_qual + 1;
1328 l_qual_relation_product := l_qual_relation_product * l_qual_rel_fac_tbl(l_terr_qval_counter);
1329
1330 IF (l_no_of_val_tbl(l_terr_qval_counter) = 1) THEN
1331
1332 /* control reaching here means that the number of values for the qualifier is one */
1333 /* update the global temp table with the qualifier values, insert if no data found */
1334
1335 IF l_actual_tab_reference > 0 THEN
1336
1337 l_update_stmt := replace(l_update_stmt_tbl(l_terr_qval_counter),
1338 'UPDATE', 'UPDATE /*+ index(JTY_DENORM_TERR_ATTR_VALUES_GT jty_dnm_terr_values_gt_n1) */ ');
1339 EXECUTE IMMEDIATE l_update_stmt USING
1340 l_cop_tbl(l_terr_qval_counter)
1341 ,l_lvc_id_tbl(l_terr_qval_counter)
1342 ,l_lvc_tbl(l_terr_qval_counter)
1343 ,l_hvc_tbl(l_terr_qval_counter)
1344 ,l_lvn_tbl(l_terr_qval_counter)
1345 ,l_hvn_tbl(l_terr_qval_counter)
1346 ,l_it_id_tbl(l_terr_qval_counter)
1347 ,l_pic_id_tbl(l_terr_qval_counter)
1348 ,l_sic_id_tbl(l_terr_qval_counter)
1349 ,l_curr_tbl(l_terr_qval_counter)
1350 ,l_value1_id_tbl(l_terr_qval_counter)
1351 ,l_value2_id_tbl(l_terr_qval_counter)
1352 ,l_value3_id_tbl(l_terr_qval_counter)
1353 ,l_value4_id_tbl(l_terr_qval_counter)
1354 ,l_fc_tbl(l_terr_qval_counter)
1355 ,p_terr_change_tab.terr_id(i)
1356 ,p_source_id
1357 ,l_qual_type_id_tbl(j);
1358
1359 IF (SQL%ROWCOUNT = 0) THEN
1360 EXECUTE IMMEDIATE l_insert_stmt_tbl(l_terr_qval_counter) USING
1361 p_terr_change_tab.terr_id(i)
1362 ,l_start_date_tbl(l_terr_qval_counter)
1363 ,l_end_date_tbl(l_terr_qval_counter)
1364 ,p_source_id
1365 ,l_qual_type_id_tbl(j)
1366 ,G_SYSDATE
1367 ,G_USER_ID
1368 ,G_SYSDATE
1369 ,G_USER_ID
1370 ,G_USER_ID
1371 ,l_abs_rank_tbl(l_terr_qval_counter)
1372 ,l_top_lvl_terr_id_tbl(l_terr_qval_counter)
1373 ,G_PROGRAM_ID
1374 ,G_USER_ID
1375 ,G_PROGRAM_APPL_ID
1376 ,G_REQUEST_ID
1377 ,G_SYSDATE
1378 ,l_cop_tbl(l_terr_qval_counter)
1379 ,l_lvc_id_tbl(l_terr_qval_counter)
1380 ,l_lvc_tbl(l_terr_qval_counter)
1381 ,l_hvc_tbl(l_terr_qval_counter)
1382 ,l_lvn_tbl(l_terr_qval_counter)
1383 ,l_hvn_tbl(l_terr_qval_counter)
1384 ,l_it_id_tbl(l_terr_qval_counter)
1385 ,l_pic_id_tbl(l_terr_qval_counter)
1386 ,l_sic_id_tbl(l_terr_qval_counter)
1387 ,l_curr_tbl(l_terr_qval_counter)
1388 ,l_value1_id_tbl(l_terr_qval_counter)
1389 ,l_value2_id_tbl(l_terr_qval_counter)
1390 ,l_value3_id_tbl(l_terr_qval_counter)
1391 ,l_value4_id_tbl(l_terr_qval_counter)
1392 ,l_fc_tbl(l_terr_qval_counter);
1393 END IF; /* end IF (SQL%ROWCOUNT = 0) */
1394 END IF;
1395 l_terr_qval_counter := l_qual_usg_id_tbl.NEXT(l_terr_qval_counter);
1396
1397 ELSE
1398
1399 /* control reaching here means that the number of values for the qualifier is more than one */
1400 FOR k IN 1 .. l_no_of_val_tbl(l_terr_qval_counter) LOOP
1401 IF (k = 1) THEN
1402 IF l_actual_tab_reference > 0 THEN
1403 l_rowid_update_stmt := replace(l_update_stmt_tbl(l_terr_qval_counter),
1404 'UPDATE', 'UPDATE /*+ index(JTY_DENORM_TERR_ATTR_VALUES_GT jty_dnm_terr_values_gt_n1) */ ') ||
1405 ' returning rowid into :19 ';
1406
1407 /* for the first value, update the existing rows with the qualifier values */
1408 /* if there is no row, insert a row for the qualifier values */
1409 EXECUTE IMMEDIATE l_rowid_update_stmt USING
1410 l_cop_tbl(l_terr_qval_counter)
1411 ,l_lvc_id_tbl(l_terr_qval_counter)
1412 ,l_lvc_tbl(l_terr_qval_counter)
1413 ,l_hvc_tbl(l_terr_qval_counter)
1414 ,l_lvn_tbl(l_terr_qval_counter)
1415 ,l_hvn_tbl(l_terr_qval_counter)
1416 ,l_it_id_tbl(l_terr_qval_counter)
1417 ,l_pic_id_tbl(l_terr_qval_counter)
1418 ,l_sic_id_tbl(l_terr_qval_counter)
1419 ,l_curr_tbl(l_terr_qval_counter)
1420 ,l_value1_id_tbl(l_terr_qval_counter)
1421 ,l_value2_id_tbl(l_terr_qval_counter)
1422 ,l_value3_id_tbl(l_terr_qval_counter)
1423 ,l_value4_id_tbl(l_terr_qval_counter)
1424 ,l_fc_tbl(l_terr_qval_counter)
1425 ,p_terr_change_tab.terr_id(i)
1426 ,p_source_id
1427 ,l_qual_type_id_tbl(j)
1428 RETURNING BULK COLLECT INTO l_rowid_tbl;
1429
1430 IF (SQL%ROWCOUNT = 0) THEN
1431 l_rowid_insert_stmt := l_insert_stmt_tbl(l_terr_qval_counter) ||
1432 ' returning rowid into :33 ';
1433
1434 EXECUTE IMMEDIATE l_rowid_insert_stmt USING
1435 p_terr_change_tab.terr_id(i)
1436 ,l_start_date_tbl(l_terr_qval_counter)
1437 ,l_end_date_tbl(l_terr_qval_counter)
1438 ,p_source_id
1439 ,l_qual_type_id_tbl(j)
1440 ,G_SYSDATE
1441 ,G_USER_ID
1442 ,G_SYSDATE
1443 ,G_USER_ID
1444 ,G_USER_ID
1445 ,l_abs_rank_tbl(l_terr_qval_counter)
1446 ,l_top_lvl_terr_id_tbl(l_terr_qval_counter)
1447 ,G_PROGRAM_ID
1448 ,G_USER_ID
1449 ,G_PROGRAM_APPL_ID
1450 ,G_REQUEST_ID
1451 ,G_SYSDATE
1452 ,l_cop_tbl(l_terr_qval_counter)
1453 ,l_lvc_id_tbl(l_terr_qval_counter)
1454 ,l_lvc_tbl(l_terr_qval_counter)
1455 ,l_hvc_tbl(l_terr_qval_counter)
1456 ,l_lvn_tbl(l_terr_qval_counter)
1457 ,l_hvn_tbl(l_terr_qval_counter)
1458 ,l_it_id_tbl(l_terr_qval_counter)
1459 ,l_pic_id_tbl(l_terr_qval_counter)
1460 ,l_sic_id_tbl(l_terr_qval_counter)
1461 ,l_curr_tbl(l_terr_qval_counter)
1462 ,l_value1_id_tbl(l_terr_qval_counter)
1463 ,l_value2_id_tbl(l_terr_qval_counter)
1464 ,l_value3_id_tbl(l_terr_qval_counter)
1465 ,l_value4_id_tbl(l_terr_qval_counter)
1466 ,l_fc_tbl(l_terr_qval_counter)
1467 RETURNING BULK COLLECT INTO l_rowid_tbl;
1468 END IF; /* end IF (SQL%ROWCOUNT = 0) */
1469 END IF;
1470 l_terr_qval_counter := l_qual_usg_id_tbl.NEXT(l_terr_qval_counter);
1471
1472 ELSE
1473 /* for the second value onwards, duplicate the existing rows */
1474 /* and update the existing rows with the qualifier values */
1475 /* duplicate the existing rows and update with the qualifier values */
1476
1477 IF l_actual_tab_reference > 0 THEN
1478 FORALL l IN l_rowid_tbl.FIRST .. l_rowid_tbl.LAST
1479 INSERT INTO jty_denorm_terr_attr_values_gt (
1480 SELECT * FROM jty_denorm_terr_attr_values_gt
1481 WHERE rowid = l_rowid_tbl(l));
1482
1483 l_rowid_update_stmt := l_update_stmt_tbl(l_terr_qval_counter) ||
1484 ' and rowid = :19 ';
1485
1486 FORALL l IN l_rowid_tbl.FIRST .. l_rowid_tbl.LAST
1487 EXECUTE IMMEDIATE l_rowid_update_stmt USING
1488 l_cop_tbl(l_terr_qval_counter)
1489 ,l_lvc_id_tbl(l_terr_qval_counter)
1490 ,l_lvc_tbl(l_terr_qval_counter)
1491 ,l_hvc_tbl(l_terr_qval_counter)
1492 ,l_lvn_tbl(l_terr_qval_counter)
1493 ,l_hvn_tbl(l_terr_qval_counter)
1494 ,l_it_id_tbl(l_terr_qval_counter)
1495 ,l_pic_id_tbl(l_terr_qval_counter)
1496 ,l_sic_id_tbl(l_terr_qval_counter)
1497 ,l_curr_tbl(l_terr_qval_counter)
1498 ,l_value1_id_tbl(l_terr_qval_counter)
1499 ,l_value2_id_tbl(l_terr_qval_counter)
1500 ,l_value3_id_tbl(l_terr_qval_counter)
1501 ,l_value4_id_tbl(l_terr_qval_counter)
1502 ,l_fc_tbl(l_terr_qval_counter)
1503 ,p_terr_change_tab.terr_id(i)
1504 ,p_source_id
1505 ,l_qual_type_id_tbl(j)
1506 ,l_rowid_tbl(l);
1507 END IF;
1508 l_terr_qval_counter := l_qual_usg_id_tbl.NEXT(l_terr_qval_counter);
1509
1510 END IF; /* end IF (k = 1) */
1511 END LOOP; /* end loop FOR k IN 1 .. l_no_of_val_tbl(l_terr_qval_counter) */
1512
1513 END IF; /* end IF (l_no_of_val_tbl(l_terr_qval_counter) = 1) */
1514
1515 END LOOP; /* end loop WHILE l_terr_qval_counter IS NOT NULL */
1516 END IF; /* end IF (l_qual_usg_id_tbl.COUNT > 0) */
1517
1518 l_qtype_terr_id_tbl.EXTEND();
1519 l_qtype_trans_id_tbl.EXTEND();
1520 l_qtype_source_id_tbl.EXTEND();
1521 l_qtype_num_qual_tbl.EXTEND();
1522 l_qtype_qual_prd_tbl.EXTEND();
1523
1524 l_qtype_terr_id_tbl(l_qtype_terr_id_tbl.COUNT) := p_terr_change_tab.terr_id(i);
1525 l_qtype_trans_id_tbl(l_qtype_trans_id_tbl.COUNT) := l_qual_type_id_tbl(j);
1526 l_qtype_source_id_tbl(l_qtype_source_id_tbl.COUNT) := p_source_id;
1527 l_qtype_num_qual_tbl(l_qtype_num_qual_tbl.COUNT) := l_num_qual;
1528 l_qtype_qual_prd_tbl(l_qtype_qual_prd_tbl.COUNT) := l_qual_relation_product;
1529
1530 END LOOP; /* end loop FOR j IN l_qual_type_id_tbl.FIRST .. l_qual_type_id_tbl.LAST */
1531 END IF; /* end IF (l_qual_type_id_tbl.COUNT > 0) */
1532 END IF; /* end IF (p_terr_change_tab.attr_processing_flag(i) IN ('I', 'D')) */
1533
1534 /* update num_qual and qual_relation_product if # of rows > g_commit_size to avoid memory overflow */
1535 IF (l_qtype_terr_id_tbl.COUNT >= G_COMMIT_SIZE) THEN
1536
1537 /* disable the trigger before update */
1538 BEGIN
1539 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
1540 EXCEPTION
1541 WHEN OTHERS THEN
1542 NULL;
1543 END;
1544
1545 /* update num_qual and qual_relation_product */
1546 FORALL l in l_qtype_terr_id_tbl.FIRST .. l_qtype_terr_id_tbl.LAST
1547 UPDATE jtf_terr_qtype_usgs_all
1548 SET num_qual = l_qtype_num_qual_tbl(l),
1549 qual_relation_product = l_qtype_qual_prd_tbl(l)
1550 WHERE terr_id = l_qtype_terr_id_tbl(l)
1551 AND qual_type_usg_id =
1552 (SELECT qual_type_usg_id
1553 FROM jtf_qual_type_usgs_all
1554 WHERE source_id = l_qtype_source_id_tbl(l)
1555 AND qual_type_id = l_qtype_trans_id_tbl(l));
1556
1557 /* enable the trigger after update */
1558 BEGIN
1559 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562 NULL;
1563 END;
1564
1565 /* l_qtype_terr_id_tbl.TRIM();
1566 l_qtype_trans_id_tbl.TRIM();
1567 l_qtype_source_id_tbl.TRIM();
1568 l_qtype_num_qual_tbl.TRIM();
1569 l_qtype_qual_prd_tbl.TRIM();
1570 Fix for bug 7240171 */
1571 l_qtype_terr_id_tbl.DELETE;
1572 l_qtype_trans_id_tbl.DELETE;
1573 l_qtype_source_id_tbl.DELETE;
1574 l_qtype_num_qual_tbl.DELETE;
1575 l_qtype_qual_prd_tbl.DELETE;
1576
1577 END IF; /* end IF (l_qtype_terr_id_tbl.COUNT >= G_COMMIT_SIZE) */
1578 END LOOP; /* end loop FOR i IN p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST */
1579 END IF; /* end IF (p_terr_change_tab.terr_id.COUNT > 0) */
1580
1581 -- debug message
1582 jty_log(FND_LOG.LEVEL_STATEMENT,
1583 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.denorm_value_gt',
1584 'Done populating the global temp table with denormalised informations');
1585
1586 /* Move the denormalized territory qualifier values from global temp table to the actual one */
1587 /* Get the schema name corresponding to JTF application */
1588 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
1589 NULL;
1590 END IF;
1591
1592 IF (l_owner IS NULL) THEN
1593 RAISE L_SCHEMA_NOTFOUND;
1594 END IF;
1595
1596 /* Initialize local variables */
1597 first_time := TRUE;
1598 l_indent := ' ';
1599
1600 /* Form the insert statement to insert the denormalized informations from global temp table to physical table */
1601 l_insert_stmt := 'INSERT INTO ' || l_table_name || ' ( ';
1602 l_select_stmt := '(SELECT ';
1603
1604 FOR column_names in c_column_names(l_table_name, l_owner) LOOP
1605 IF (first_time) THEN
1606 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
1607 l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
1608 first_time := FALSE;
1609 ELSE
1610 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
1611 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
1612 END IF;
1613 END LOOP;
1614
1615 /* Standard WHO columns */
1616 l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
1617 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
1618 g_new_line || l_indent || ',CREATION_DATE ' ||
1619 g_new_line || l_indent || ',CREATED_BY ' ||
1620 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
1621 g_new_line || l_indent || ',REQUEST_ID ' ||
1622 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
1623 g_new_line || l_indent || ',PROGRAM_ID ' ||
1624 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ) ';
1625
1626 l_select_stmt := l_select_stmt || g_new_line || l_indent || ',:1' ||
1627 g_new_line || l_indent || ',:2' ||
1628 g_new_line || l_indent || ',:3' ||
1629 g_new_line || l_indent || ',:4' ||
1630 g_new_line || l_indent || ',:5' ||
1631 g_new_line || l_indent || ',:6' ||
1632 g_new_line || l_indent || ',:7' ||
1633 g_new_line || l_indent || ',:8' ||
1634 g_new_line || l_indent || ',:9' ||
1635 g_new_line || l_indent || ' FROM jty_denorm_terr_attr_values_gt) ';
1636
1637 jty_log(FND_LOG.LEVEL_STATEMENT,
1638 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.denorm_value',
1639 'Start Insert into denormalized table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1640
1641 EXECUTE IMMEDIATE l_insert_stmt || l_select_stmt USING
1642 g_sysdate
1643 ,g_user_id
1644 ,g_sysdate
1645 ,g_user_id
1646 ,g_login_id
1647 ,g_request_id
1648 ,g_program_appl_id
1649 ,g_program_id
1650 ,g_sysdate;
1651
1652 jty_log(FND_LOG.LEVEL_STATEMENT,
1653 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.denorm_value',
1654 'End Insert into denormalized table ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1655
1656 -- debug message
1657 jty_log(FND_LOG.LEVEL_STATEMENT,
1658 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.denorm_value',
1659 'Number of rows inserted : ' || SQL%ROWCOUNT);
1660
1661 /* analyze the denorm value table to caluclate the selectivity of the columns */
1662 IF (p_mode <> 'INCREMENTAL') THEN
1663 JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX(
1664 p_table_name => l_table_name
1665 , p_percent => 20
1666 , x_return_status => x_return_status );
1667 END IF;
1668
1669 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1670 retcode := 2;
1671 errbuf := 'JTY_TAE_INDEX_CREATION_PVT.ANALYZE_TABLE_INDEX has failed for table ' || l_table_name;
1672 jty_log(FND_LOG.LEVEL_EXCEPTION,
1673 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.analyze_table_index',
1674 'ANALYZE_TABLE_INDEX API has failed');
1675
1676 RAISE FND_API.G_EXC_ERROR;
1677 END IF;
1678
1679 IF (l_qtype_terr_id_tbl.COUNT > 0) THEN
1680 /* disable the trigger before update */
1681 BEGIN
1682 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD DISABLE';
1683 EXCEPTION
1684 WHEN OTHERS THEN
1685 NULL;
1686 END;
1687
1688 /* update num_qual and qual_relation_product */
1689 FORALL l in l_qtype_terr_id_tbl.FIRST .. l_qtype_terr_id_tbl.LAST
1690 UPDATE jtf_terr_qtype_usgs_all
1691 SET num_qual = l_qtype_num_qual_tbl(l),
1692 qual_relation_product = l_qtype_qual_prd_tbl(l)
1693 WHERE terr_id = l_qtype_terr_id_tbl(l)
1694 AND qual_type_usg_id =
1695 (SELECT qual_type_usg_id
1696 FROM jtf_qual_type_usgs_all
1697 WHERE source_id = l_qtype_source_id_tbl(l)
1698 AND qual_type_id = l_qtype_trans_id_tbl(l));
1699
1700 /* enable the trigger before update */
1701 BEGIN
1702 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERR_QTYPE_USGS_BIUD ENABLE';
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 NULL;
1706 END;
1707
1708 /* l_qtype_terr_id_tbl.TRIM();
1709 l_qtype_trans_id_tbl.TRIM();
1710 l_qtype_source_id_tbl.TRIM();
1711 l_qtype_num_qual_tbl.TRIM();
1712 l_qtype_qual_prd_tbl.TRIM();
1713 Fix for bug 7240171 */
1714 l_qtype_terr_id_tbl.DELETE;
1715 l_qtype_trans_id_tbl.DELETE;
1716 l_qtype_source_id_tbl.DELETE;
1717 l_qtype_num_qual_tbl.DELETE;
1718 l_qtype_qual_prd_tbl.DELETE;
1719
1720 END IF; /* end IF (l_qtype_terr_id_tbl.COUNT > 0) */
1721
1722 -- debug message
1723 jty_log(FND_LOG.LEVEL_STATEMENT,
1724 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.update_num_qual',
1725 'Done updating jtf_terr_qtype_usgs_all with num_qual and qual_relation_product');
1726
1727 -- debug message
1728 jty_log(FND_LOG.LEVEL_PROCEDURE,
1729 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.end',
1730 'End of the procedure JTY_TERR_DENORM_RULES_PVT.process_attr_values ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1731
1732 retcode := 0;
1733 errbuf := null;
1734
1735 EXCEPTION
1736 WHEN FND_API.G_EXC_ERROR THEN
1737 jty_log(FND_LOG.LEVEL_EXCEPTION,
1738 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.g_exc_error',
1739 'API JTY_TERR_DENORM_RULES_PVT.process_attr_values has failed with FND_API.G_EXC_ERROR exception');
1740
1741 WHEN L_SCHEMA_NOTFOUND THEN
1742 RETCODE := 2;
1743 ERRBUF := 'JTY_TERR_DENORM_RULES_PVT.process_attr_values : SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
1744 jty_log(FND_LOG.LEVEL_EXCEPTION,
1745 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.l_schema_notfound',
1746 errbuf);
1747
1748 WHEN OTHERS THEN
1749 RETCODE := 2;
1750 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1751 jty_log(FND_LOG.LEVEL_EXCEPTION,
1752 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_values.others',
1753 substr(errbuf, 1, 4000));
1754
1755 END process_attr_values;
1756
1757 /* This procedure calculates relative rank and denormalized hierarchy informations */
1758 PROCEDURE process_terr_rank (
1759 p_source_id IN NUMBER,
1760 p_mode IN VARCHAR2,
1761 p_terr_change_tab IN JTY_TERR_ENGINE_GEN_PVT.terr_change_type,
1762 p_table_name IN VARCHAR2,
1763 errbuf OUT NOCOPY VARCHAR2,
1764 retcode OUT NOCOPY VARCHAR2 )
1765 IS
1766
1767 l_new_parent_territory_id NUMBER;
1768 l_parent_terr_id NUMBER;
1769 l_new_parent_num_winners NUMBER;
1770 l_level_from_root NUMBER;
1771 l_max_rank NUMBER;
1772
1773 l_rows_inserted1 INTEGER;
1774 l_rows_inserted2 INTEGER;
1775 l_no_of_records INTEGER;
1776
1777 l_dyn_str VARCHAR2(1000);
1778
1779 l_terr_id_tbl1 jtf_terr_number_list := jtf_terr_number_list();
1780 l_related_terr_id_tbl jtf_terr_number_list := jtf_terr_number_list();
1781 l_top_level_terr_id_tbl jtf_terr_number_list := jtf_terr_number_list();
1782 l_num_winners_tbl jtf_terr_number_list := jtf_terr_number_list();
1783 l_level_from_root_tbl jtf_terr_number_list := jtf_terr_number_list();
1784 l_level_from_parent_tbl jtf_terr_number_list := jtf_terr_number_list();
1785 l_terr_rank_tbl jtf_terr_number_list := jtf_terr_number_list();
1786 l_immediate_parent_flag_tbl jtf_terr_char_1list := jtf_terr_char_1list();
1787 l_org_id_tbl jtf_terr_number_list := jtf_terr_number_list();
1788 l_start_date_tbl jtf_terr_date_list := jtf_terr_date_list();
1789 l_end_date_tbl jtf_terr_date_list := jtf_terr_date_list();
1790
1791 l_terr_id_tbl2 jtf_terr_number_list := jtf_terr_number_list();
1792 l_relative_rank_tbl jtf_terr_number_list := jtf_terr_number_list();
1793
1794 l_qual_type_id_tbl jtf_terr_number_list := jtf_terr_number_list();
1795
1796 CURSOR c_get_qual_type_id (cl_source_id IN NUMBER) IS
1797 SELECT qual_type_id
1798 FROM jtf_qual_type_usgs_all
1799 WHERE source_id = cl_source_id
1800 AND qual_type_id <> -1001;
1801
1802 BEGIN
1803 -- debug message
1804 jty_log(FND_LOG.LEVEL_PROCEDURE,
1805 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.start',
1806 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.process_terr_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1807
1808 /* Get the maximum rank among the territories for the usage */
1809 BEGIN
1810
1811 SELECT /*+ ORDERED */ nvl(MAX(j2.rank), 99)
1812 INTO l_max_rank
1813 FROM jtf_qual_type_usgs j1
1814 , jtf_terr_qtype_usgs_all j4
1815 , jtf_terr_all j2
1816 WHERE j2.terr_id <> 1
1817 AND j4.terr_id = j2.terr_id
1818 AND j4.qual_type_usg_id = j1.qual_type_usg_id
1819 AND j1.source_id = p_source_id;
1820
1821 EXCEPTION
1822 WHEN NO_DATA_FOUND THEN
1823 l_max_rank := 99;
1824 END;
1825
1826 l_rows_inserted1 := 0;
1827 l_rows_inserted2 := 0;
1828
1829 IF (p_terr_change_tab.terr_id.COUNT > 0) THEN
1830
1831 FOR i IN p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST LOOP
1832
1833 /* if mode is incremental, delete all entries from denorm table for the territory */
1834 IF ((p_terr_change_tab.hier_processing_flag(i) IN ('I', 'D')) AND (p_mode = 'INCREMENTAL')) THEN
1835 DELETE jtf_terr_denorm_rules_all
1836 WHERE terr_id = p_terr_change_tab.terr_id(i);
1837 END IF;
1838
1839 /* if mode is dea incremental, delete all entries from denorm table for the territory */
1840 IF ((p_terr_change_tab.hier_processing_flag(i) IN ('I', 'D')) AND (p_mode = 'DEA INCREMENTAL')) THEN
1841 DELETE jty_denorm_dea_rules_all
1842 WHERE terr_id = p_terr_change_tab.terr_id(i);
1843 END IF;
1844
1845 /* if the # of rows that need to updated for relative rank exceeds */
1846 /* g_commit_size, then update the physical table to avoid memory overflow */
1847 IF (l_rows_inserted2 >= G_COMMIT_SIZE) THEN
1848 update_relative_rank (
1849 p_terr_id_tbl => l_terr_id_tbl2,
1850 p_relative_rank_tbl => l_relative_rank_tbl,
1851 errbuf => errbuf,
1852 retcode => retcode);
1853
1854 IF (retcode <> 0) THEN
1855 -- debug message
1856 jty_log(FND_LOG.LEVEL_EXCEPTION,
1857 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_relative_rank',
1858 'update_relative_rank API has failed');
1859
1860 RAISE FND_API.G_EXC_ERROR;
1861 END IF;
1862
1863 update_absolute_rank (
1864 p_terr_id_tbl => l_terr_id_tbl2,
1865 p_mode => p_mode,
1866 p_table_name => p_table_name,
1867 errbuf => errbuf,
1868 retcode => retcode);
1869
1870 IF (retcode <> 0) THEN
1871 -- debug message
1872 jty_log(FND_LOG.LEVEL_EXCEPTION,
1873 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_absolute_rank',
1874 'update_absolute_rank API has failed');
1875
1876 RAISE FND_API.G_EXC_ERROR;
1877 END IF;
1878
1879 l_terr_id_tbl2.TRIM(l_rows_inserted2);
1880 l_relative_rank_tbl.TRIM(l_rows_inserted2);
1881
1882 l_rows_inserted2 := 0;
1883 END IF; /* end IF (l_rows_inserted2 >= G_COMMIT_SIZE) */
1884
1885 /* if the # of rows that need to updated for denorm hier table exceeds */
1886 /* g_commit_size, then update the physical table to avoid memory overflow */
1887 IF (l_rows_inserted1 >= G_COMMIT_SIZE) THEN
1888 update_denorm_table (
1889 p_source_id => p_source_id,
1890 p_mode => p_mode,
1891 p_terr_id_tbl => l_terr_id_tbl1,
1892 p_related_terr_id_tbl => l_related_terr_id_tbl,
1893 p_top_level_terr_id_tbl => l_top_level_terr_id_tbl,
1894 p_num_winners_tbl => l_num_winners_tbl,
1895 p_level_from_root_tbl => l_level_from_root_tbl,
1896 p_level_from_parent_tbl => l_level_from_parent_tbl,
1897 p_terr_rank_tbl => l_terr_rank_tbl,
1898 p_immediate_parent_flag_tbl => l_immediate_parent_flag_tbl,
1899 p_org_id_tbl => l_org_id_tbl,
1900 p_start_date_tbl => l_start_date_tbl,
1901 p_end_date_tbl => l_end_date_tbl,
1902 errbuf => errbuf,
1903 retcode => retcode);
1904
1905 IF (retcode <> 0) THEN
1906 -- debug message
1907 jty_log(FND_LOG.LEVEL_EXCEPTION,
1908 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_denorm_table',
1909 'update_denorm_table API has failed');
1910
1911 RAISE FND_API.G_EXC_ERROR;
1912 END IF;
1913
1914 l_terr_id_tbl1.TRIM(l_rows_inserted1);
1915 l_related_terr_id_tbl.TRIM(l_rows_inserted1);
1916 l_top_level_terr_id_tbl.TRIM(l_rows_inserted1);
1917 l_num_winners_tbl.TRIM(l_rows_inserted1);
1918 l_level_from_root_tbl.TRIM(l_rows_inserted1);
1919 l_level_from_parent_tbl.TRIM(l_rows_inserted1);
1920 l_terr_rank_tbl.TRIM(l_rows_inserted1);
1921 l_immediate_parent_flag_tbl.TRIM(l_rows_inserted1);
1922 l_org_id_tbl.TRIM(l_rows_inserted1);
1923 l_start_date_tbl.TRIM(l_rows_inserted1);
1924 l_end_date_tbl.TRIM(l_rows_inserted1);
1925
1926 l_rows_inserted1 := 0;
1927 END IF;
1928
1929 l_level_from_root := p_terr_change_tab.level_from_root(i);
1930
1931 /* calculate the relative rank of the territory */
1932 IF (p_terr_change_tab.rank_calc_flag(i) = 'Y') THEN
1933 l_rows_inserted2 := l_rows_inserted2 + 1;
1934
1935 l_terr_id_tbl2.EXTEND;
1936 l_terr_id_tbl2(l_rows_inserted2) := p_terr_change_tab.terr_id(i);
1937
1938 l_relative_rank_tbl.EXTEND;
1939 l_relative_rank_tbl(l_rows_inserted2) := 1/(p_terr_change_tab.terr_rank(i) * POWER(l_max_rank, l_level_from_root));
1940 END IF;
1941
1942 IF (p_terr_change_tab.hier_processing_flag(i) = 'I') THEN
1943 l_rows_inserted1 := l_rows_inserted1 + 1;
1944
1945 /* insert row for itself */
1946 l_terr_id_tbl1.EXTEND;
1947 l_terr_id_tbl1(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
1948
1949 l_related_terr_id_tbl.EXTEND;
1950 l_related_terr_id_tbl(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
1951
1952 l_num_winners_tbl.EXTEND;
1953 l_top_level_terr_id_tbl.EXTEND;
1954 IF (p_source_id = -1001) THEN
1955 IF ((p_terr_change_tab.parent_terr_id(i) = 1) AND (p_terr_change_tab.num_winners(i) IS NULL)) THEN
1956 l_num_winners_tbl(l_rows_inserted1) := 1;
1957 ELSE
1958 l_num_winners_tbl(l_rows_inserted1) := p_terr_change_tab.num_winners(i);
1959 END IF;
1960 ELSE
1961 SELECT jt.terr_id, NVL(jt.num_winners, 1)
1962 INTO l_top_level_terr_id_tbl(l_rows_inserted1), l_num_winners_tbl(l_rows_inserted1)
1963 FROM jtf_terr_all jt
1964 WHERE jt.parent_territory_id = 1
1965 AND (jt.org_id <> -3114 OR jt.org_id IS NULL)
1966 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
1967 START WITH jt.terr_id = p_terr_change_tab.terr_id(i);
1968 END IF;
1969
1970 l_level_from_parent_tbl.EXTEND;
1971 l_level_from_parent_tbl(l_rows_inserted1) := 0;
1972
1973 l_level_from_root_tbl.EXTEND;
1974 l_level_from_root_tbl(l_rows_inserted1) := l_level_from_root;
1975
1976 l_terr_rank_tbl.EXTEND;
1977 l_terr_rank_tbl(l_rows_inserted1) := p_terr_change_tab.terr_rank(i);
1978
1979 l_immediate_parent_flag_tbl.EXTEND;
1980 l_immediate_parent_flag_tbl(l_rows_inserted1) := 'N';
1981
1982 l_org_id_tbl.EXTEND;
1983 l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
1984
1985 l_org_id_tbl.EXTEND;
1986 l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
1987
1988 l_start_date_tbl.EXTEND;
1989 l_start_date_tbl(l_rows_inserted1) := p_terr_change_tab.start_date(i);
1990
1991 l_end_date_tbl.EXTEND;
1992 l_end_date_tbl(l_rows_inserted1) := p_terr_change_tab.end_date(i);
1993
1994 /* Insert row for immediate parent */
1995 IF (p_terr_change_tab.terr_id(i) <> 1 AND p_terr_change_tab.parent_terr_id(i) <> 1 ) THEN
1996 l_rows_inserted1 := l_rows_inserted1 + 1;
1997
1998 l_terr_id_tbl1.EXTEND;
1999 l_terr_id_tbl1(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
2000
2001 l_related_terr_id_tbl.EXTEND;
2002 l_related_terr_id_tbl(l_rows_inserted1) := p_terr_change_tab.parent_terr_id(i);
2003
2004 l_num_winners_tbl.EXTEND;
2005 l_num_winners_tbl(l_rows_inserted1) := p_terr_change_tab.parent_num_winners(i);
2006
2007 l_top_level_terr_id_tbl.EXTEND;
2008 l_top_level_terr_id_tbl(l_rows_inserted1) := l_top_level_terr_id_tbl(l_rows_inserted1 - 1);
2009
2010 l_level_from_parent_tbl.EXTEND;
2011 l_level_from_parent_tbl(l_rows_inserted1) := l_level_from_parent_tbl(l_rows_inserted1 - 1) + 1;
2012
2013 l_level_from_root_tbl.EXTEND;
2014 l_level_from_root_tbl(l_rows_inserted1) := l_level_from_root_tbl(l_rows_inserted1 - 1) - 1;
2015
2016 l_terr_rank_tbl.EXTEND;
2017 l_terr_rank_tbl(l_rows_inserted1) := p_terr_change_tab.terr_rank(i);
2018
2019 l_immediate_parent_flag_tbl.EXTEND;
2020 l_immediate_parent_flag_tbl(l_rows_inserted1) := 'Y';
2021
2022 l_org_id_tbl.EXTEND;
2023 l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
2024
2025 l_start_date_tbl.EXTEND;
2026 l_start_date_tbl(l_rows_inserted1) := p_terr_change_tab.start_date(i);
2027
2028 l_end_date_tbl.EXTEND;
2029 l_end_date_tbl(l_rows_inserted1) := p_terr_change_tab.end_date(i);
2030
2031 l_parent_terr_id := p_terr_change_tab.parent_terr_id(i);
2032
2033 /* insert rows for the other parents */
2034 LOOP
2035 SELECT /*+ index(TR1 JTF_TERR_U1) */ DISTINCT TR1.PARENT_TERRITORY_ID, TR2.NUM_WINNERS
2036 INTO l_new_parent_territory_id, l_new_parent_num_winners
2037 FROM jtf_terr_all TR1, jtf_terr_all TR2
2038 WHERE TR2.terr_id = TR1.parent_territory_id
2039 AND TR1.TERR_ID <> 1
2040 AND TR1.TERR_ID = l_parent_terr_id;
2041
2042 EXIT WHEN ( l_parent_terr_id = 1 OR l_new_parent_territory_id = 1 );
2043
2044 l_rows_inserted1 := l_rows_inserted1 + 1;
2045
2046 l_terr_id_tbl1.EXTEND;
2047 l_terr_id_tbl1(l_rows_inserted1) := p_terr_change_tab.terr_id(i);
2048
2049 l_related_terr_id_tbl.EXTEND;
2050 l_related_terr_id_tbl(l_rows_inserted1) := l_new_parent_territory_id;
2051
2052 l_num_winners_tbl.EXTEND;
2053 l_num_winners_tbl(l_rows_inserted1) := l_new_parent_num_winners;
2054
2055 l_top_level_terr_id_tbl.EXTEND;
2056 l_top_level_terr_id_tbl(l_rows_inserted1) := l_top_level_terr_id_tbl(l_rows_inserted1 - 1);
2057
2058 l_level_from_parent_tbl.EXTEND;
2059 l_level_from_parent_tbl(l_rows_inserted1) := l_level_from_parent_tbl(l_rows_inserted1 - 1) + 1;
2060
2061 l_level_from_root_tbl.EXTEND;
2062 l_level_from_root_tbl(l_rows_inserted1) := l_level_from_root_tbl(l_rows_inserted1 - 1) - 1;
2063
2064 l_terr_rank_tbl.EXTEND;
2065 l_terr_rank_tbl(l_rows_inserted1) := p_terr_change_tab.terr_rank(i);
2066
2067 l_immediate_parent_flag_tbl.EXTEND;
2068 l_immediate_parent_flag_tbl(l_rows_inserted1) := 'N';
2069
2070 l_org_id_tbl.EXTEND;
2071 l_org_id_tbl(l_rows_inserted1) := p_terr_change_tab.org_id(i);
2072
2073 l_start_date_tbl.EXTEND;
2074 l_start_date_tbl(l_rows_inserted1) := p_terr_change_tab.start_date(i);
2075
2076 l_end_date_tbl.EXTEND;
2077 l_end_date_tbl(l_rows_inserted1) := p_terr_change_tab.end_date(i);
2078
2079 l_parent_terr_id := l_new_parent_territory_id;
2080
2081 END LOOP;
2082
2083 END IF; /* end IF (p_terr_change_tab.terr_id(i) <> 1 AND p_terr_change_tab.parent_terr_id(i) <> 1 ) */
2084 END IF; /* end IF (p_terr_change_tab.hier_processing_flag = 'I') */
2085 END LOOP; /* end loop FOR i IN p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST */
2086
2087 /* update relative rank */
2088 IF (l_rows_inserted2 > 0) THEN
2089 update_relative_rank (
2090 p_terr_id_tbl => l_terr_id_tbl2,
2091 p_relative_rank_tbl => l_relative_rank_tbl,
2092 errbuf => errbuf,
2093 retcode => retcode);
2094
2095 IF (retcode <> 0) THEN
2096 -- debug message
2097 jty_log(FND_LOG.LEVEL_EXCEPTION,
2098 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_relative_rank',
2099 'update_relative_rank API has failed');
2100
2101 RAISE FND_API.G_EXC_ERROR;
2102 END IF;
2103
2104 /* update absolute rank */
2105 update_absolute_rank (
2106 p_terr_id_tbl => l_terr_id_tbl2,
2107 p_mode => p_mode,
2108 p_table_name => p_table_name,
2109 errbuf => errbuf,
2110 retcode => retcode);
2111
2112 IF (retcode <> 0) THEN
2113 -- debug message
2114 jty_log(FND_LOG.LEVEL_EXCEPTION,
2115 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_absolute_rank',
2116 'update_absolute_rank API has failed');
2117
2118 RAISE FND_API.G_EXC_ERROR;
2119 END IF;
2120
2121 l_terr_id_tbl2.TRIM(l_rows_inserted2);
2122
2123
2124 l_relative_rank_tbl.TRIM(l_rows_inserted2);
2125
2126 l_rows_inserted2 := 0;
2127 END IF;
2128
2129 /* update denorm hier table */
2130 IF (l_rows_inserted1 > 0) THEN
2131 update_denorm_table (
2132 p_source_id => p_source_id,
2133 p_mode => p_mode,
2134 p_terr_id_tbl => l_terr_id_tbl1,
2135 p_related_terr_id_tbl => l_related_terr_id_tbl,
2136 p_top_level_terr_id_tbl => l_top_level_terr_id_tbl,
2137 p_num_winners_tbl => l_num_winners_tbl,
2138 p_level_from_root_tbl => l_level_from_root_tbl,
2139 p_level_from_parent_tbl => l_level_from_parent_tbl,
2140 p_terr_rank_tbl => l_terr_rank_tbl,
2141 p_immediate_parent_flag_tbl => l_immediate_parent_flag_tbl,
2142 p_org_id_tbl => l_org_id_tbl,
2143 p_start_date_tbl => l_start_date_tbl,
2144 p_end_date_tbl => l_end_date_tbl,
2145 errbuf => errbuf,
2146 retcode => retcode);
2147
2148 IF (retcode <> 0) THEN
2149 -- debug message
2150 jty_log(FND_LOG.LEVEL_EXCEPTION,
2151 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.update_denorm_table',
2152 'update_denorm_table API has failed');
2153
2154 RAISE FND_API.G_EXC_ERROR;
2155 END IF;
2156
2157 l_terr_id_tbl1.TRIM(l_rows_inserted1);
2158 l_related_terr_id_tbl.TRIM(l_rows_inserted1);
2159 l_top_level_terr_id_tbl.TRIM(l_rows_inserted1);
2160 l_num_winners_tbl.TRIM(l_rows_inserted1);
2161 l_level_from_root_tbl.TRIM(l_rows_inserted1);
2162 l_level_from_parent_tbl.TRIM(l_rows_inserted1);
2163 l_terr_rank_tbl.TRIM(l_rows_inserted1);
2164 l_immediate_parent_flag_tbl.TRIM(l_rows_inserted1);
2165 l_org_id_tbl.TRIM(l_rows_inserted1);
2166 l_start_date_tbl.TRIM(l_rows_inserted1);
2167 l_end_date_tbl.TRIM(l_rows_inserted1);
2168
2169 l_rows_inserted1 := 0;
2170 END IF;
2171
2172 /* disable the trigger before update */
2173 /* BEGIN
2174 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD DISABLE';
2175 EXCEPTION
2176 WHEN OTHERS THEN
2177 NULL;
2178 END;*/
2179
2180 /* calculate the absolute rank */
2181 /*FORALL i IN l_terr_id_tbl2.FIRST .. l_terr_id_tbl2.LAST
2182 UPDATE jtf_terr_all jta1
2183 SET jta1.ABSOLUTE_RANK = (
2184 SELECT SUM(jta2.relative_rank)
2185 FROM jtf_terr_all jta2
2186 WHERE jta2.terr_id IN (
2187 SELECT jt.terr_id
2188 FROM jtf_terr_all jt
2189 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
2190 START WITH jt.terr_id = l_terr_id_tbl2(i))),
2191 jta1.last_update_date = g_sysdate
2192 WHERE jta1.terr_id = l_terr_id_tbl2(i);
2193
2194 l_dyn_str :=
2195 'UPDATE ' || p_table_name || ' ' ||
2196 'SET absolute_rank = ( ' ||
2197 ' SELECT absolute_rank ' ||
2198 ' FROM jtf_terr_all ' ||
2199 ' WHERE terr_id = :1 ) ' ||
2200 'WHERE terr_id = :2 ';
2201
2202 IF (p_mode = 'INCREMENTAL') THEN
2203 FORALL i IN l_terr_id_tbl2.FIRST .. l_terr_id_tbl2.LAST
2204 EXECUTE IMMEDIATE l_dyn_str USING l_terr_id_tbl2(i), l_terr_id_tbl2(i);
2205 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
2206 FORALL i IN l_terr_id_tbl2.FIRST .. l_terr_id_tbl2.LAST
2207 EXECUTE IMMEDIATE l_dyn_str USING l_terr_id_tbl2(i), l_terr_id_tbl2(i);
2208 END IF;
2209
2210 l_terr_id_tbl2.TRIM(l_rows_inserted2);*/
2211
2212 /* enable the trigger after update */
2213 /*BEGIN
2214 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORIES_BIUD ENABLE';
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 NULL;
2218 END;*/
2219
2220 -- debug message
2221 jty_log(FND_LOG.LEVEL_STATEMENT,
2222 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.rows_inserted',
2223 'Finished inserting rows into denorm table and rank calculation');
2224
2225 END IF; /* end IF (p_terr_change_tab.terr_id.COUNT > 0) */
2226
2227 /* update the first_char column to improve performance of LIKE op */
2228 BEGIN
2229
2230 OPEN c_get_qual_type_id(p_source_id);
2231 FETCH c_get_qual_type_id BULK COLLECT INTO l_qual_type_id_tbl;
2232 CLOSE c_get_qual_type_id;
2233
2234 l_no_of_records := l_qual_type_id_tbl.COUNT;
2235
2236 IF (l_no_of_records > 0) THEN
2237
2238 BEGIN
2239 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_VALUES_BIUD DISABLE';
2240 EXCEPTION
2241 WHEN OTHERS THEN
2242 NULL;
2243 END;
2244
2245 FORALL i IN l_qual_type_id_tbl.FIRST .. l_qual_type_id_tbl.LAST
2246 UPDATE /*+ INDEX (o jtf_terr_values_n1) */ jtf_terr_values_all o
2247 SET o.first_char = SUBSTR(o.low_value_char, 1, 1)
2248 WHERE o.terr_qual_id IN (
2249 SELECT /*+ INDEX (i2 jtf_qual_usgs_n3) */
2250 i1.terr_qual_id
2251 FROM jtf_terr_qual_all i1, jtf_qual_usgs_all i2, jtf_qual_type_usgs_all i3
2252 WHERE i1.qual_usg_id = i2.qual_usg_id
2253 AND i2.display_type = 'CHAR'
2254 AND i2.lov_sql IS NULL
2255 AND i2.org_id = -3113
2256 AND i2.qual_type_usg_id = i3.qual_type_usg_id
2257 AND i3.source_id = p_source_id
2258 AND i3.qual_type_id in (SELECT related_id
2259 FROM jtf_qual_type_denorm_v
2260 WHERE qual_type_id = l_qual_type_id_tbl(i)));
2261
2262 l_qual_type_id_tbl.TRIM(l_no_of_records);
2263
2264 BEGIN
2265 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_VALUES_BIUD ENABLE';
2266 EXCEPTION
2267 WHEN OTHERS THEN
2268 NULL;
2269 END;
2270
2271 END IF; /* end IF (l_no_of_records > 0) */
2272
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 NULL;
2276 END;
2277
2278 -- debug message
2279 jty_log(FND_LOG.LEVEL_PROCEDURE,
2280 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.end',
2281 'End of the procedure JTY_TERR_DENORM_RULES_PVT.process_terr_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2282
2283 retcode := 0;
2284 errbuf := null;
2285
2286 EXCEPTION
2287 WHEN FND_API.G_EXC_ERROR THEN
2288 RETCODE := 2;
2289 jty_log(FND_LOG.LEVEL_EXCEPTION,
2290 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.g_exc_error',
2291 'API JTY_TERR_DENORM_RULES_PVT.process_terr_rank has failed with FND_API.G_EXC_ERROR exception');
2292
2293 WHEN OTHERS THEN
2294 RETCODE := 2;
2295 ERRBUF := SQLCODE || ' : ' || SQLERRM;
2296 jty_log(FND_LOG.LEVEL_EXCEPTION,
2297 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_terr_rank.others',
2298 substr(errbuf, 1, 4000));
2299
2300 END process_terr_rank;
2301
2302 /* drop indexes of the denorm value table */
2303 PROCEDURE DROP_DNMVAL_TABLE_INDEXES( p_table_name IN VARCHAR2
2304 ,p_mode IN VARCHAR2
2305 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
2306
2307 v_statement varchar2(800);
2308
2309 l_status VARCHAR2(30);
2310 l_industry VARCHAR2(30);
2311 l_jtf_schema VARCHAR2(30);
2312
2313 Cursor getIndexList(cl_table_name IN VARCHAR2, cl_owner IN VARCHAR2) IS
2314 SELECT aidx.owner, aidx.INDEX_NAME
2315 FROM DBA_INDEXES aidx
2316 WHERE aidx.table_name = cl_table_name
2317 AND aidx.table_owner = cl_owner
2318 AND aidx.index_name like 'JTY_DNM_ATTR_VAL%';
2319
2320 Cursor getDeaIndexList(cl_table_name IN VARCHAR2, cl_owner IN VARCHAR2) IS
2321 SELECT aidx.owner, aidx.INDEX_NAME
2322 FROM DBA_INDEXES aidx
2323 WHERE aidx.table_name = cl_table_name
2324 AND aidx.table_owner = cl_owner
2325 AND aidx.index_name like 'JTY_DEA_ATTR_VAL%';
2326
2327 L_SCHEMA_NOTFOUND EXCEPTION;
2328 BEGIN
2329 -- debug message
2330 jty_log(FND_LOG.LEVEL_PROCEDURE,
2331 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.drop_dnmval_table_indexes.begin',
2332 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.drop_dnmval_table_indexes ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2333
2334 x_return_status := FND_API.G_RET_STS_SUCCESS;
2335
2336 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
2337 NULL;
2338 END IF;
2339
2340 IF (l_jtf_schema IS NULL) THEN
2341 RAISE L_SCHEMA_NOTFOUND;
2342 END IF;
2343
2344 -- for each index
2345 IF (p_mode = 'TOTAL') THEN
2346 FOR idx IN getIndexList(p_table_name, l_jtf_schema) LOOP
2347 v_statement := 'DROP INDEX ' || idx.owner || '.' || idx.index_name;
2348
2349 BEGIN
2350 EXECUTE IMMEDIATE v_statement;
2351 EXCEPTION
2352 WHEN OTHERS THEN NULL;
2353 END;
2354
2355 END LOOP;
2356 ELSIF (p_mode = 'DATE EFFECTIVE') THEN
2357 FOR idx IN getDeaIndexList(p_table_name, l_jtf_schema) LOOP
2358 v_statement := 'DROP INDEX ' || idx.owner || '.' || idx.index_name;
2359
2360 BEGIN
2361 EXECUTE IMMEDIATE v_statement;
2362 EXCEPTION
2363 WHEN OTHERS THEN NULL;
2364 END;
2365
2366 END LOOP;
2367 END IF;
2368
2369 -- debug message
2370 jty_log(FND_LOG.LEVEL_PROCEDURE,
2371 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.drop_dnmval_table_indexes.end',
2372 'End of the procedure JTY_TERR_DENORM_RULES_PVT.drop_dnmval_table_indexes ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2373
2374 EXCEPTION
2375 WHEN L_SCHEMA_NOTFOUND THEN
2376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2377 jty_log(FND_LOG.LEVEL_EXCEPTION,
2378 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.drop_dnmval_table_indexes.l_schema_notfound',
2379 'Schema name corresponding to JTF application not found');
2380
2381 WHEN OTHERS THEN
2382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2383 jty_log(FND_LOG.LEVEL_EXCEPTION,
2384 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.drop_dnmval_table_indexes.others',
2385 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
2386
2387 END DROP_DNMVAL_TABLE_INDEXES;
2388
2389 /* entry point of this package */
2390 PROCEDURE process_attr_and_rank (
2391 p_source_id IN NUMBER,
2392 p_mode IN VARCHAR2,
2393 p_terr_change_tab IN JTY_TERR_ENGINE_GEN_PVT.terr_change_type,
2394 errbuf OUT NOCOPY VARCHAR2,
2395 retcode OUT NOCOPY VARCHAR2 )
2396 IS
2397
2398 l_table_name VARCHAR2(30);
2399 l_table_owner VARCHAR2(30);
2400 x_return_status VARCHAR2(250);
2401 l_dea_incr_start_date DATE;
2402 l_dea_incr_end_date DATE;
2403 l_jtf_app_id NUMBER := 690;
2404
2405 CURSOR c_prod(b_APP_ID NUMBER) IS
2406 SELECT ORACLE_USERNAME
2407 FROM FND_ORACLE_USERID
2408 WHERE ORACLE_ID=b_APP_ID;
2409 BEGIN
2410 -- debug message
2411 jty_log(FND_LOG.LEVEL_PROCEDURE,
2412 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.start',
2413 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2414
2415 OPEN c_prod(l_jtf_app_id);
2416 FETCH c_prod INTO l_table_owner;
2417 CLOSE c_prod;
2418
2419 jty_log(FND_LOG.LEVEL_PROCEDURE,
2420 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.l_table_owner',
2421 'Start of the procedure JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank ' || l_table_owner);
2422
2423 IF (p_mode = 'DATE EFFECTIVE') THEN
2424 SELECT denorm_dea_value_table_name
2425 INTO l_table_name
2426 FROM jtf_sources_all
2427 WHERE source_id = p_source_id;
2428 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
2429 SELECT denorm_dea_value_table_name
2430 INTO l_table_name
2431 FROM jtf_sources_all
2432 WHERE source_id = p_source_id;
2433 ELSE
2434 SELECT denorm_value_table_name
2435 INTO l_table_name
2436 FROM jtf_sources_all
2437 WHERE source_id = p_source_id;
2438 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
2439
2440 /* delete the old records from denormalized tables */
2441 IF (p_mode = 'TOTAL') THEN
2442 DELETE jtf_terr_denorm_rules_all
2443 WHERE source_id = p_source_id;
2444
2445 /* drop index on denorm value table */
2446 drop_dnmval_table_indexes (
2447 p_table_name => l_table_name
2448 ,p_mode => p_mode
2449 ,x_return_status => x_return_status);
2450
2451 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2452 retcode := 2;
2453 errbuf := 'drop_dnmval_table_indexes API has failed';
2454 -- debug message
2455 jty_log(FND_LOG.LEVEL_EXCEPTION,
2456 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.drop_dnmval_table_indexes',
2457 'drop_dnmval_table_indexes API has failed');
2458
2459 RAISE FND_API.G_EXC_ERROR;
2460 END IF;
2461
2462 DELETE jty_terr_values_idx_details dtl
2463 WHERE EXISTS (
2464 SELECT 1
2465 FROM jty_terr_values_idx_header hdr
2466 WHERE dtl.terr_values_idx_header_id = hdr.terr_values_idx_header_id
2467 AND hdr.source_id = p_source_id );
2468
2469 DELETE jty_terr_values_idx_header hdr
2470 WHERE hdr.source_id = p_source_id;
2471
2472 EXECUTE IMMEDIATE 'truncate table '||l_table_owner || '.' || l_table_name ;
2473 ELSIF (p_mode = 'DATE EFFECTIVE') THEN
2474 DELETE jty_denorm_dea_rules_all
2475 WHERE source_id = p_source_id;
2476
2477 /* drop index on denorm value table */
2478 drop_dnmval_table_indexes (
2479 p_table_name => l_table_name
2480 ,p_mode => p_mode
2481 ,x_return_status => x_return_status);
2482
2483 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2484 retcode := 2;
2485 errbuf := 'drop_dnmval_table_indexes API has failed';
2486 -- debug message
2487 jty_log(FND_LOG.LEVEL_EXCEPTION,
2488 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.drop_dnmval_table_indexes',
2489 'drop_dnmval_table_indexes API has failed');
2490
2491 RAISE FND_API.G_EXC_ERROR;
2492 END IF;
2493
2494 DELETE jty_dea_values_idx_details dtl
2495 WHERE EXISTS (
2496 SELECT 1
2497 FROM jty_dea_values_idx_header hdr
2498 WHERE dtl.dea_values_idx_header_id = hdr.dea_values_idx_header_id
2499 AND hdr.source_id = p_source_id );
2500
2501 DELETE jty_dea_values_idx_header hdr
2502 WHERE hdr.source_id = p_source_id;
2503
2504 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_table_owner || '.' || l_table_name;
2505 ELSIF (p_mode = 'INCREMENTAL') THEN
2506 EXECUTE IMMEDIATE 'delete ' || l_table_name || ' where source_id = :1 and (start_date > :2 or end_date < :3) ' USING p_source_id, g_sysdate, g_sysdate;
2507
2508 DELETE jtf_terr_denorm_rules_all
2509 WHERE source_id = p_source_id
2510 AND (start_date > g_sysdate
2511 OR end_date < g_sysdate);
2512
2513 /* mark all the records to be deleted */
2514 /* delete_flag will be updated to 'N' for qualifiers that are used by active territories while generating real time matching sql */
2515 /* delete_flag will be updated to 'N' for qualifier combinations used by active territories after updating jtf_tae_qual_products */
2516 UPDATE jty_terr_values_idx_header
2517 SET delete_flag = 'Y'
2518 WHERE source_id = p_source_id;
2519 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
2520
2521 SELECT max(start_date) into l_dea_incr_start_date
2522 FROM jty_conc_req_summ a
2523 WHERE a.program_name = 'JTY_STAR'
2524 AND a.param1 = to_char(p_source_id)
2525 AND a.param2 = 'DATE EFFECTIVE'
2526 AND a.retcode = 0 ;
2527
2528 SELECT max(end_date) into l_dea_incr_end_date
2529 FROM jty_conc_req_summ a
2530 WHERE a.program_name = 'JTY_STAR'
2531 AND a.param1 = to_char(p_source_id)
2532 AND a.param2 = 'DATE EFFECTIVE'
2533 AND a.retcode = 0 ;
2534
2535 EXECUTE IMMEDIATE 'delete ' || l_table_name || ' where source_id = :1 and (start_date > :2 or end_date < :3) ' USING p_source_id, l_dea_incr_end_date, l_dea_incr_start_date;
2536
2537 DELETE jty_denorm_dea_rules_all
2538 WHERE source_id = p_source_id
2539 AND (start_date > l_dea_incr_end_date
2540 OR end_date < l_dea_incr_end_date);
2541
2542 /* mark all the records to be deleted */
2543 /* delete_flag will be updated to 'N' for qualifiers that are used by active territories while generating real time matching sql */
2544 /* delete_flag will be updated to 'N' for qualifier combinations used by active territories after updating jtf_tae_qual_products */
2545 UPDATE jty_dea_values_idx_header
2546 SET delete_flag = 'Y'
2547 WHERE source_id = p_source_id;
2548 END IF;
2549
2550 /* Denormalize the territory hierarchy and calculate rank */
2551 process_terr_rank (
2552 p_source_id => p_source_id
2553 ,p_mode => p_mode
2554 ,p_terr_change_tab => p_terr_change_tab
2555 ,p_table_name => l_table_name
2556 ,errbuf => errbuf
2557 ,retcode => retcode);
2558
2559 IF (retcode <> 0) THEN
2560 -- debug message
2561 jty_log(FND_LOG.LEVEL_EXCEPTION,
2562 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.process_terr_rank',
2563 'process_terr_rank API has failed');
2564
2565 RAISE FND_API.G_EXC_ERROR;
2566 END IF;
2567
2568 -- debug message
2569 jty_log(FND_LOG.LEVEL_EVENT,
2570 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.process_terr_rank',
2571 'API process_terr_rank completed successfully');
2572
2573 /* Denormalize the qualifier values and calculate num_qual and qual_relation_product */
2574 process_attr_values (
2575 p_source_id => p_source_id
2576 ,p_mode => p_mode
2577 ,p_table_name => l_table_name
2578 ,p_terr_change_tab => p_terr_change_tab
2579 ,errbuf => errbuf
2580 ,retcode => retcode);
2581
2582 IF (retcode <> 0) THEN
2583 -- debug message
2584 jty_log(FND_LOG.LEVEL_EXCEPTION,
2585 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.process_attr_values',
2586 'process_attr_values API has failed');
2587
2588 RAISE FND_API.G_EXC_ERROR;
2589 END IF;
2590
2591 -- debug message
2592 jty_log(FND_LOG.LEVEL_EVENT,
2593 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.process_attr_values',
2594 'API process_attr_values completed successfully');
2595
2596 -- debug message
2597 jty_log(FND_LOG.LEVEL_PROCEDURE,
2598 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.end',
2599 'End of the procedure JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
2600
2601 retcode := 0;
2602 errbuf := null;
2603
2604 EXCEPTION
2605 WHEN FND_API.G_EXC_ERROR THEN
2606 jty_log(FND_LOG.LEVEL_EXCEPTION,
2607 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.g_exc_error',
2608 'API JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank has failed with FND_API.G_EXC_ERROR exception');
2609
2610 WHEN OTHERS THEN
2611 RETCODE := 2;
2612 ERRBUF := SQLCODE || ' : ' || SQLERRM;
2613 jty_log(FND_LOG.LEVEL_EXCEPTION,
2614 'jtf.plsql.JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank.others',
2615 substr(errbuf, 1, 4000));
2616
2617 END process_attr_and_rank;
2618
2619
2620 END JTY_TERR_DENORM_RULES_PVT;