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