[Home] [Help]
PACKAGE BODY: APPS.JTY_TERR_ENGINE_GEN_PVT
Source
1 Package Body JTY_TERR_ENGINE_GEN_PVT AS
2 /* $Header: jtfytegb.pls 120.9.12010000.4 2008/11/04 10:07:48 vpalle ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTY_TERR_ENGINE_GEN_PVT
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- This package is used to generate the complete territory
9 -- Engine based on tha data setup in the JTF territory tables
10 --
11 -- Procedures:
12 -- (see below for specification)
13 --
14 -- NOTES
15 -- This package is publicly available for use
16 --
17 -- HISTORY
18 -- 06/27/05 ACHANDA Created
19 --
20 -- End of Comments
21 --
22 --------------------------------------------------
23 --- GLOBAL Declarations Starts here -----
24 --------------------------------------------------
25
26 /* Global System Variables */
27 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
28 G_SYSDATE DATE := SYSDATE;
29
30 /* this procedure looks at the table jty_changed_terrs and retrive the master */
31 /* list that need to be processed by incremental star */
32 PROCEDURE get_terr_for_incr_star (
33 p_source_id IN NUMBER,
34 p_request_id IN NUMBER,
35 p_terr_change_tab OUT NOCOPY terr_change_type,
36 retcode OUT NOCOPY VARCHAR2,
37 errbuf OUT NOCOPY VARCHAR2
38 )
39 AS
40
41 CURSOR c_changed_terrs (cl_request_id IN NUMBER) IS
42 SELECT a.terr_id,
43 a.rank_calc_flag,
44 a.process_attr_values_flag,
45 a.matching_sql_flag,
46 a.hier_processing_flag
47 FROM jty_changed_terrs a
48 WHERE a.star_request_id = cl_request_id
49 AND a.source_id = p_source_id
50 AND (a.rank_calc_flag <> 'N' OR a.process_attr_values_flag <> 'N' OR
51 a.matching_sql_flag <> 'N' OR a.hier_processing_flag <> 'N')
52 AND NOT EXISTS (
53 SELECT jt.terr_id
54 FROM jtf_terr_all jt
55 WHERE jt.end_date_active < sysdate
56 OR jt.start_date_active > sysdate
57 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
58 START WITH jt.terr_id = a.terr_id )
59 UNION ALL
60 SELECT a.terr_id,
61 'N',
62 'D',
63 'Y',
64 'D'
65 FROM jtf_terr_all a
66 WHERE (a.start_date_active > sysdate
67 OR a.end_date_active < sysdate)
68 AND exists (
69 SELECT 1
70 FROM jty_changed_terrs b
71 WHERE b.terr_id = a.terr_id
72 AND b.star_request_id = cl_request_id
73 AND b.source_id = p_source_id);
74
75 CURSOR c_child_terrs (cl_terr_id IN NUMBER) IS
76 SELECT terr_id
77 FROM jtf_terr_all
78 START WITH terr_id = cl_terr_id
79 CONNECT BY PRIOR terr_id = parent_territory_id;
80
81 TYPE l_terr_id_tbl_type IS TABLE OF jty_changed_terrs.terr_id%TYPE;
82 TYPE l_rank_tbl_type IS TABLE OF jty_changed_terrs.rank_calc_flag%TYPE;
83 TYPE l_attr_values_tbl_type IS TABLE OF jty_changed_terrs.process_attr_values_flag%TYPE;
84 TYPE l_match_sql_tbl_type IS TABLE OF jty_changed_terrs.matching_sql_flag%TYPE;
85 TYPE l_hier_tbl_type IS TABLE OF jty_changed_terrs.hier_processing_flag%TYPE;
86
87 l_terr_id_tbl l_terr_id_tbl_type;
88 l_rank_tbl l_rank_tbl_type;
89 l_attr_values_tbl l_attr_values_tbl_type;
90 l_match_sql_tbl l_match_sql_tbl_type;
91 l_hier_tbl l_hier_tbl_type;
92 BEGIN
93
94 -- debug message
95 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
96 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
97 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star.start',
98 'Start of the procedure JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star');
99 END IF;
100
101 /* Insert into jty_changed_terrs the territories that */
102 /* have become active after the last run of STAR */
103 MERGE INTO jty_changed_terrs A
104 USING
105 ( SELECT
106 a.terr_id terr_id,
107 b.source_id source_id
108 FROM jtf_terr_all a,
109 jtf_terr_usgs_all b
110 WHERE a.terr_id = b.terr_id
111 AND b.source_id = p_source_id
112 AND a.start_date_active >
113 (SELECT max(end_date)
114 FROM jty_conc_req_summ a
115 WHERE a.program_name = 'JTY_STAR'
116 AND a.param1 = to_char(p_source_id)
117 AND a.retcode = 0)
118 AND a.start_date_active < sysdate
119 AND a.end_date_active > sysdate
120 AND NOT EXISTS (
121 SELECT jt.terr_id
122 FROM jtf_terr_all jt
123 WHERE jt.end_date_active < sysdate
124 OR jt.start_date_active > sysdate
125 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
126 START WITH jt.terr_id = a.terr_id ) ) S
127 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
128 WHEN MATCHED THEN
129 UPDATE SET
130 A.rank_calc_flag = 'Y'
131 ,A.process_attr_values_flag = 'I'
132 ,A.matching_sql_flag = 'Y'
133 ,A.hier_processing_flag = 'I'
134 WHEN NOT MATCHED THEN
135 INSERT (
136 A.CHANGED_TERRITORY_ID
137 ,A.OBJECT_VERSION_NUMBER
138 ,A.TERR_ID
139 ,A.SOURCE_ID
140 ,A.CHANGE_TYPE
141 ,A.RANK_CALC_FLAG
142 ,A.PROCESS_ATTR_VALUES_FLAG
143 ,A.MATCHING_SQL_FLAG
144 ,A.HIER_PROCESSING_FLAG)
145 VALUES (
146 jty_changed_terrs_s.nextval
147 ,0
148 ,S.terr_id
149 ,S.source_id
150 ,'UPDATE'
151 ,'Y'
152 ,'I'
153 ,'Y'
154 ,'I');
155
156 /* Insert into jty_changed_terrs the territories that */
157 /* have become inactive after the last run of STAR */
158 MERGE INTO jty_changed_terrs A
159 USING
160 ( SELECT
161 a.terr_id terr_id,
162 b.source_id source_id
163 FROM jtf_terr_all a,
164 jtf_terr_usgs_all b
165 WHERE a.terr_id = b.terr_id
166 AND b.source_id = p_source_id
167 AND a.end_date_active >
168 (SELECT max(end_date)
169 FROM jty_conc_req_summ a
170 WHERE a.program_name = 'JTY_STAR'
171 AND a.param1 = to_char(p_source_id)
172 AND a.retcode = 0)
173 AND a.end_date_active < sysdate ) S
174 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
175 WHEN MATCHED THEN
176 UPDATE SET
177 A.rank_calc_flag = 'N'
178 ,A.process_attr_values_flag = 'D'
179 ,A.matching_sql_flag = 'Y'
180 ,A.hier_processing_flag = 'D'
181 WHEN NOT MATCHED THEN
182 INSERT (
183 A.CHANGED_TERRITORY_ID
184 ,A.OBJECT_VERSION_NUMBER
185 ,A.TERR_ID
186 ,A.SOURCE_ID
187 ,A.CHANGE_TYPE
188 ,A.RANK_CALC_FLAG
189 ,A.PROCESS_ATTR_VALUES_FLAG
190 ,A.MATCHING_SQL_FLAG
191 ,A.HIER_PROCESSING_FLAG)
192 VALUES (
193 jty_changed_terrs_s.nextval
194 ,0
195 ,S.terr_id
196 ,S.source_id
197 ,'UPDATE'
198 ,'N'
199 ,'D'
200 ,'Y'
201 ,'D');
202
203 DELETE jty_changed_terrs_gt;
204
205 UPDATE jty_changed_terrs a
206 SET a.star_request_id = p_request_id
207 WHERE a.star_request_id IS NULL
208 AND a.source_id = p_source_id;
209
210 OPEN c_changed_terrs(p_request_id);
211 FETCH c_changed_terrs BULK COLLECT INTO
212 l_terr_id_tbl,
213 l_rank_tbl,
214 l_attr_values_tbl,
215 l_match_sql_tbl,
216 l_hier_tbl;
217 CLOSE c_changed_terrs;
218
219 IF (l_terr_id_tbl.COUNT > 0) THEN
220 FOR i IN l_terr_id_tbl.FIRST .. l_terr_id_tbl.LAST LOOP
221 INSERT INTO jty_changed_terrs_gt (
222 terr_id
223 ,rank_calc_flag
224 ,process_attr_values_flag
225 ,matching_sql_flag
226 ,hier_processing_flag)
227 VALUES (
228 l_terr_id_tbl(i)
229 ,l_rank_tbl(i)
230 ,l_attr_values_tbl(i)
231 ,l_match_sql_tbl(i)
232 ,l_hier_tbl(i));
233
234 FOR child_terrs IN c_child_terrs (l_terr_id_tbl(i)) LOOP
235 UPDATE jty_changed_terrs_gt
236 SET rank_calc_flag = decode(rank_calc_flag, 'Y', 'Y', l_rank_tbl(i)),
237 process_attr_values_flag =
238 decode(process_attr_values_flag,
239 'I', 'I',
240 'D', decode(l_attr_values_tbl(i), 'I', 'I', 'D'),
241 l_attr_values_tbl(i)),
242 matching_sql_flag = decode(matching_sql_flag, 'Y', 'Y', l_match_sql_tbl(i)),
243 hier_processing_flag =
244 decode(hier_processing_flag,
245 'I', 'I',
246 'D', decode(l_hier_tbl(i), 'I', 'I', 'D'),
247 l_hier_tbl(i))
248 WHERE terr_id = child_terrs.terr_id;
249
250 IF (SQL%ROWCOUNT = 0) THEN
251 INSERT INTO jty_changed_terrs_gt (
252 terr_id
253 ,rank_calc_flag
254 ,process_attr_values_flag
255 ,matching_sql_flag
256 ,hier_processing_flag)
257 VALUES (
258 child_terrs.terr_id
259 ,l_rank_tbl(i)
260 ,l_attr_values_tbl(i)
261 ,l_match_sql_tbl(i)
262 ,l_hier_tbl(i));
263 END IF;
264
265 END LOOP; /* end loop FOR child_terrs IN c_child_terrs (l_terr_id_tbl(i)) */
266 END LOOP; /* end loop FOR i IN l_terr_id_tbl.FIRST .. l_terr_id_tbl.LAST */
267 END IF; /* end IF (l_terr_id_tbl.COUNT > 0) */
268
269 SELECT
270 a.terr_id
271 ,a.rank_calc_flag
272 ,a.process_attr_values_flag
273 ,a.matching_sql_flag
274 ,a.hier_processing_flag
275 ,b.rank
276 ,b.parent_territory_id
277 ,JTY_TERR_DENORM_RULES_PVT.get_level_from_root(a.terr_id)
278 ,b.num_winners
279 ,b.org_id
280 ,c.num_winners
281 ,b.start_date_active
282 ,b.end_date_active
283 BULK COLLECT INTO
284 p_terr_change_tab.terr_id
285 ,p_terr_change_tab.rank_calc_flag
286 ,p_terr_change_tab.attr_processing_flag
287 ,p_terr_change_tab.matching_sql_flag
288 ,p_terr_change_tab.hier_processing_flag
289 ,p_terr_change_tab.terr_rank
290 ,p_terr_change_tab.parent_terr_id
291 ,p_terr_change_tab.level_from_root
292 ,p_terr_change_tab.num_winners
293 ,p_terr_change_tab.org_id
294 ,p_terr_change_tab.parent_num_winners
295 ,p_terr_change_tab.start_date
296 ,p_terr_change_tab.end_date
297 FROM
298 jty_changed_terrs_gt a
299 ,jtf_terr_all b
300 ,jtf_terr_all c
301 WHERE a.terr_id = b.terr_id(+)
302 AND b.parent_territory_id = c.terr_id(+)
303 AND b.org_id = c.org_id(+);
304
305 -- debug message
306 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
307 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
308 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star.end',
309 'End of the procedure JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star');
310 END IF;
311
312 retcode := 0;
313 errbuf := null;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 RETCODE := 2;
318 ERRBUF := SQLCODE || ' : ' || SQLERRM;
319 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
320 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
321 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star.others',
322 substr(errbuf, 1, 4000));
323 END IF;
324 END get_terr_for_incr_star;
325
326 /* this procedure calls APIs to generate real time and batch matching SQLs */
327 PROCEDURE gen_matching_sql (
328 p_source_id IN NUMBER,
329 p_mode IN VARCHAR2,
330 p_terr_change_tab terr_change_type,
331 p_start_date IN DATE,
332 p_end_date IN DATE,
333 x_Return_Status OUT NOCOPY VARCHAR2,
334 x_Msg_Count OUT NOCOPY NUMBER,
335 x_Msg_Data OUT NOCOPY VARCHAR2,
336 errbuf OUT NOCOPY VARCHAR2,
337 retcode OUT NOCOPY VARCHAR2
338 )
339 AS
340
341 CURSOR c_trans_types (cl_source_id NUMBER) IS
342 SELECT qual_type_id
343 FROM jtf_qual_type_usgs_all
344 WHERE source_id = cl_source_id
345 AND qual_type_id <> -1001;
346
347 CURSOR c_qual_rel_sets(cl_source_id number, cl_qual_type_id number) is
348 select distinct jtqu.qual_relation_product
349 from jtf_terr_qtype_usgs_all jtqu
350 ,jtf_qual_type_usgs_all jqtu
351 where jqtu.source_id = cl_source_id
352 and jqtu.qual_type_id = cl_qual_type_id
353 and jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
354 and jtqu.qual_relation_product <> 1
355 and exists (
356 select /*+ index_ffs(jtdr jtf_terr_denorm_rules_n1) */ 1
357 from jtf_terr_denorm_rules_all jtdr
358 where jtdr.terr_id = jtqu.terr_id
359 and jtqu.terr_id = jtdr.related_terr_id );
360
361 CURSOR c_dea_qual_rel_sets(cl_source_id number, cl_qual_type_id number) is
362 select distinct jtqu.qual_relation_product
363 from jtf_terr_qtype_usgs_all jtqu
364 ,jtf_qual_type_usgs_all jqtu
365 where jqtu.source_id = cl_source_id
366 and jqtu.qual_type_id = cl_qual_type_id
367 and jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
368 and jtqu.qual_relation_product <> 1
369 and exists (
370 select /*+ index_ffs(jtdr jty_denorm_dea_rules_n1) */ 1
371 from jty_denorm_dea_rules_all jtdr
372 where jtdr.terr_id = jtqu.terr_id
373 and jtqu.terr_id = jtdr.related_terr_id );
374
375 TYPE l_qual_type_id_tbl_type IS TABLE OF jtf_qual_type_usgs_all.qual_type_id%TYPE;
376
377 l_qual_type_id_tbl l_qual_type_id_tbl_type;
378 l_qual_prd_tbl qual_prd_tbl_type;
379
380 l_new_qual_prd BOOLEAN;
381 l_sysdate DATE;
382 l_qual_relation_product NUMBER;
383
384 BEGIN
385
386 -- debug message
387 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
389 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.start',
390 'Start of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql');
391 END IF;
392
393 l_sysdate := SYSDATE;
394 l_qual_prd_tbl := qual_prd_tbl_type();
395
396 /* get all the transaction types */
397 OPEN c_trans_types(p_source_id);
398 FETCH c_trans_types BULK COLLECT INTO
399 l_qual_type_id_tbl;
400 CLOSE c_trans_types;
401
402 IF (l_qual_type_id_tbl.COUNT > 0) THEN
403 FOR i IN l_qual_type_id_tbl.FIRST .. l_qual_type_id_tbl.LAST LOOP
404
405 /* always generate the real time matching sql */
406 JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql (
407 p_source_id => p_source_id,
408 p_trans_id => l_qual_type_id_tbl(i),
409 p_mode => p_mode,
410 p_start_date => p_start_date,
411 p_end_date => p_end_date,
412 errbuf => errbuf,
413 retcode => retcode);
414 IF (retcode <> 0) THEN
415 -- debug message
416 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
417 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
418 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.gen_real_time_sql',
419 'JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql API has failed');
420 END IF;
421
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424
425
426 /* delete the old records from the tables jtf_tae_qual_products, jtf_tae_qual_factors */
427 /* and jtf_tae_qual_prod_factors if mode is total or incremental */
428 /* delete the old records from the tables jtf_dea_attr_products, jtf_dea_attr_factors */
429 /* and jtf_dea_attr_prod_factors if mode is date effective */
430 JTY_TAE_CONTROL_PVT.delete_combinations(
431 p_source_id => p_source_id,
432 p_trans_id => l_qual_type_id_tbl(i),
433 p_mode => p_mode,
434 x_Return_Status => x_return_status,
435 x_Msg_Count => x_msg_count,
436 x_Msg_Data => x_msg_data,
437 ERRBUF => errbuf,
438 RETCODE => retcode);
439
440 IF (retcode <> 0) THEN
441 -- debug message
442 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
443 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
444 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.delete_combinations',
445 'JTY_TAE_CONTROL_PVT.delete_combinations API has failed');
446 END IF;
447
448 RAISE FND_API.G_EXC_ERROR;
449 END IF;
450
451 /* if mode is total or date effective, get all the qualifier combinations for the active territories */
452 /* if mode is incremental, get all the distinct qualifier comb for the territories with */
453 /* matching_sql_flag = 'Y' in p_terr_change_tab */
454 IF (p_mode = 'TOTAL') THEN
455 OPEN c_qual_rel_sets(p_source_id, l_qual_type_id_tbl(i));
456 FETCH c_qual_rel_sets BULK COLLECT INTO
457 l_qual_prd_tbl;
458 CLOSE c_qual_rel_sets;
459 ELSIF (p_mode = 'DATE EFFECTIVE') THEN
460 OPEN c_dea_qual_rel_sets(p_source_id, l_qual_type_id_tbl(i));
461 FETCH c_dea_qual_rel_sets BULK COLLECT INTO
462 l_qual_prd_tbl;
463 CLOSE c_dea_qual_rel_sets;
464 ELSIF (p_mode = 'INCREMENTAL') THEN
465 FOR j in p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST LOOP
466 IF (p_terr_change_tab.matching_sql_flag(j) = 'Y') THEN
467
468 BEGIN
469 SELECT a.qual_relation_product
470 INTO l_qual_relation_product
471 FROM jtf_terr_qtype_usgs_all a,
472 jtf_qual_type_usgs_all b,
473 jtf_terr_all c
474 WHERE a.qual_type_usg_id = b.qual_type_usg_id
475 AND b.source_id = p_source_id
476 AND b.qual_type_id = l_qual_type_id_tbl(i)
477 AND a.terr_id = p_terr_change_tab.terr_id(j)
478 AND c.terr_id = a.terr_id
479 AND c.start_date_active < sysdate
480 AND c.end_date_active > sysdate
481 AND a.qual_relation_product <> 1
482 AND NOT EXISTS (
483 SELECT 1
484 FROM jtf_tae_qual_products c
485 WHERE c.source_id = p_source_id
486 AND c.trans_object_type_id = l_qual_type_id_tbl(i)
487 AND c.relation_product = a.qual_relation_product);
488
489 /* check if the qual rel prd alreday exists in the pl/sql table that will be processed */
490 l_new_qual_prd := TRUE;
491 IF (l_qual_prd_tbl.COUNT > 0) THEN
492 FOR k in l_qual_prd_tbl.FIRST .. l_qual_prd_tbl.LAST LOOP
493 IF (l_qual_relation_product = l_qual_prd_tbl(k)) THEN
494 l_new_qual_prd := FALSE;
495 exit;
496 END IF;
497 END LOOP;
498 END IF;
499
500 /* insert the current qual rel prd into the pl/sql table only if it does not exist */
501 IF (l_new_qual_prd) THEN
502 l_qual_prd_tbl.EXTEND();
503 l_qual_prd_tbl(l_qual_prd_tbl.COUNT) := l_qual_relation_product;
504 END IF;
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 NULL;
508 END;
509
510 END IF; /* end IF (p_terr_change_tab.matching_sql_flag = 'Y') */
511 END LOOP; /* end loop FOR j in p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST */
512 END IF; /* end IF (p_mode = 'TOTAL') */
513
514 /* generate the batch matching sql for all qualifier combinations present in l_qual_type_id_tbl */
515 IF (l_qual_prd_tbl.COUNT > 0) THEN
516 jty_tae_gen_pvt.gen_batch_sql(
517 p_source_id => p_source_id,
518 p_trans_id => l_qual_type_id_tbl(i),
519 p_mode => p_mode,
520 p_qual_prd_tbl => l_qual_prd_tbl,
521 x_return_status => x_return_status,
522 x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data,
524 errbuf => errbuf,
525 retcode => retcode);
526
527 IF (retcode <> 0) THEN
528 -- debug message
529 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
530 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
531 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.gen_batch_sql',
532 'jty_tae_gen_pvt.gen_batch_sql API has failed');
533 END IF;
534
535 RAISE FND_API.G_EXC_ERROR;
536 END IF;
537
538 END IF; /* end IF (l_qual_prd_tbl.COUNT > 0) */
539
540 l_qual_prd_tbl.TRIM(l_qual_prd_tbl.COUNT);
541
542 END LOOP; /* end loop FOR i IN l_qual_type_id_tbl.FIRST .. l_qual_type_id_tbl.LAST */
543 END IF; /* end IF (l_qual_type_id_tbl.COUNT > 0) */
544
545 -- debug message
546 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
548 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.end',
549 'End of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql');
550 END IF;
551
552 retcode := 0;
553 errbuf := null;
554
555 EXCEPTION
556 WHEN FND_API.G_EXC_ERROR THEN
557 RETCODE := 2;
558 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
559 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
560 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.g_exc_error',
561 'API JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql has failed with FND_API.G_EXC_ERROR exception');
562 END IF;
563
564 WHEN OTHERS THEN
565 RETCODE := 2;
566 ERRBUF := SQLCODE || ' : ' || SQLERRM;
567 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
568 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
569 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.others',
570 substr(errbuf, 1, 4000));
571 END IF;
572 END gen_matching_sql;
573
574 /* entry point of the concurrent program STAR */
575 PROCEDURE gen_rule_engine (
576 errbuf OUT NOCOPY VARCHAR2,
577 retcode OUT NOCOPY VARCHAR2,
578 p_source_id IN NUMBER,
579 p_mode IN VARCHAR2,
580 p_start_date IN VARCHAR2,
581 p_end_date IN VARCHAR2
582 )
583 AS
584
585 CURSOR csr_get_terr(lp_source_id NUMBER, lp_start_date DATE, lp_end_date DATE) IS
586 SELECT jta1.terr_id terr_id
587 ,NVL(jta1.rank, 999999999) rank
588 ,jta1.num_winners num_winners
589 ,jta1.org_id org_id
590 ,jta1.parent_territory_id parent_territory_id
591 ,JTY_TERR_DENORM_RULES_PVT.get_level_from_root(jta1.terr_id) level_from_root
592 ,jta2.num_winners parent_num_winners
593 ,'Y' rank_calc_flag
594 ,'I' attr_processing_flag
595 ,'I' hier_processing_flag
596 ,'Y' matching_sql_flag
597 ,jta1.start_date_active start_date
598 ,jta1.end_date_active end_date
599 FROM jtf_terr_usgs_all jtu
600 , jtf_terr_all jta1
601 , jtf_terr_all jta2
602 WHERE jtu.source_id = lp_source_id
603 AND jtu.terr_id = jta1.terr_id
604 AND jta1.terr_id <> 1
605 AND jta1.end_date_active >= lp_start_date
606 AND jta1.start_date_active <= lp_end_date
607 AND jta2.terr_id = jta1.parent_territory_id
608 AND ( jta1.org_id = jta2.org_id OR
609 (jta1.org_id IS NULL AND jta2.org_id IS NULL) )
610 AND NOT EXISTS (
611 SELECT jt.terr_id
612 FROM jtf_terr_all jt
613 WHERE jt.end_date_active < lp_start_date
614 OR jt.start_date_active > lp_end_date
615 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
616 START WITH jt.terr_id = jta1.terr_id );
617
618 l_terr_change_tab terr_change_type;
619 l_no_of_records NUMBER;
620 l_batch_enabled NUMBER;
621 l_count NUMBER;
622 l_table_name VARCHAR2(30);
623
624 l_return_status VARCHAR2(10);
625 l_msg_count NUMBER;
626 l_msg_data VARCHAR2(2000);
627
628 l_resp_appl_id NUMBER;
629 l_resp_id NUMBER;
630 l_user_id NUMBER;
631 l_login_id NUMBER;
632 l_sysdate DATE;
633 l_start_date DATE;
634 l_end_date DATE;
635 l_pgm_appl_id NUMBER;
636 l_pgm_name VARCHAR2(360);
637 l_conc_pgm_id NUMBER;
638
639 l_param_start_date DATE;
640 l_param_end_date DATE;
641 l_denorm_count NUMBER;
642 BEGIN
643
644 -- debug message
645 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
646 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
647 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.start',
648 'Start of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine');
649 END IF;
650
651 /* Initialize audit columns */
652 l_start_date := SYSDATE;
653 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
654 l_resp_id := FND_GLOBAL.RESP_ID;
655 l_user_id := FND_GLOBAL.USER_ID;
656 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
657 l_pgm_appl_id := FND_GLOBAL.PROG_APPL_ID;
658 l_conc_pgm_id := FND_GLOBAL.CONC_PROGRAM_ID;
659 l_pgm_name := 'JTY_STAR';
660
661 l_param_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
662 l_param_end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
663
664 /* mark the records in the changed table that will be processed */
665 IF (p_mode = 'TOTAL') THEN
666 UPDATE jty_changed_terrs
667 SET star_request_id = g_request_id
668 WHERE source_id = p_source_id
669 AND star_request_id IS NULL;
670 END IF;
671
672 -- debug message
673 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
674 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
675 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.param_values',
676 'Source : ' || p_source_id || ' Mode : ' || p_mode || ' Start Date : ' || p_start_date ||
677 ' End Date : ' || p_end_date);
678 END IF;
679
680 /* if mode = incremental , get all the territories that need to be processed from incr_gtp */
681 /* if mode = total , get all the active territories , as of sysdate, from jtf_terr_all */
682 /* if mode = date effective , get all the active territories , between p_start_date and p_end_date, from jtf_terr_all */
683 IF (p_mode = 'INCREMENTAL') THEN
684 BEGIN
685 SELECT count(*)
686 INTO l_count
687 FROM jty_conc_req_summ a
688 WHERE a.program_name = 'JTY_STAR'
689 AND a.param1 = p_source_id
690 AND a.param2 = 'TOTAL'
691 AND a.retcode = 0;
692
693 IF (l_count = 0) THEN
694 -- debug message
695 retcode := 2;
696 errbuf := 'STAR should be run at least once in TOTAL mode before INCREMENTAL mode';
697 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
698 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
699 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.check_total_mode',
700 errbuf);
701 END IF;
702
703 RAISE FND_API.G_EXC_ERROR;
704 END IF;
705 EXCEPTION
706 WHEN OTHERS THEN
707 RAISE;
708 END;
709
710 get_terr_for_incr_star (
711 p_source_id => p_source_id,
712 p_request_id => g_request_id,
713 p_terr_change_tab => l_terr_change_tab,
714 retcode => retcode,
715 errbuf => errbuf);
716
717 IF (retcode <> 0) THEN
718 -- debug message
719 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
720 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
721 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.get_terr_for_incr_star',
722 'get_terr_for_incr_star API has failed');
723 END IF;
724
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727
728 ELSE
729 IF (p_mode = 'TOTAL') THEN
730 OPEN csr_get_terr(p_source_id, g_sysdate, g_sysdate);
731 ELSIF (p_mode = 'DATE EFFECTIVE') THEN
732 OPEN csr_get_terr(p_source_id, l_param_start_date, l_param_end_date);
733 END IF;
734
735 FETCH csr_get_terr BULK COLLECT INTO
736 l_terr_change_tab.terr_id
737 ,l_terr_change_tab.terr_rank
738 ,l_terr_change_tab.num_winners
739 ,l_terr_change_tab.org_id
740 ,l_terr_change_tab.parent_terr_id
741 ,l_terr_change_tab.level_from_root
742 ,l_terr_change_tab.parent_num_winners
743 ,l_terr_change_tab.rank_calc_flag
744 ,l_terr_change_tab.attr_processing_flag
745 ,l_terr_change_tab.hier_processing_flag
746 ,l_terr_change_tab.matching_sql_flag
747 ,l_terr_change_tab.start_date
748 ,l_terr_change_tab.end_date;
749
750 CLOSE csr_get_terr;
751 END IF; /* end IF (p_mode = 'INCREMENTAL') */
752
753 l_no_of_records := l_terr_change_tab.terr_id.COUNT;
754
755 -- debug message
756 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
757 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
758 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.no_of_terr',
759 'Number of territories to be processed : ' || l_no_of_records);
760 END IF;
761
762 IF (l_no_of_records > 0) THEN
763 /* Calculate rank, denormalize hierarchy and qualifier values */
764 JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank (
765 p_source_id => p_source_id,
766 p_mode => p_mode,
767 p_terr_change_tab => l_terr_change_tab,
768 errbuf => errbuf,
769 retcode => retcode);
770
771 IF (retcode <> 0) THEN
772 -- debug message
773 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
774 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
775 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.process_attr_and_rank',
776 'process_attr_and_rank API has failed');
777 END IF;
778
779 RAISE FND_API.G_EXC_ERROR;
780 END IF;
781
782 -- debug message
783 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
784 FND_LOG.string(FND_LOG.LEVEL_EVENT,
785 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.process_attr_and_rank',
786 'process_attr_and_rank API has completed with success');
787 END IF;
788
789 /* Generate real time and batch matching SQLs */
790 gen_matching_sql (
791 p_source_id => p_source_id,
792 p_mode => p_mode,
793 p_terr_change_tab => l_terr_change_tab,
794 p_start_date => l_param_start_date,
795 p_end_date => l_param_end_date,
796 x_Return_Status => l_Return_Status,
797 x_Msg_Count => l_Msg_Count,
798 x_Msg_Data => l_Msg_Data,
799 errbuf => errbuf,
800 retcode => retcode);
801
802 IF (retcode <> 0) THEN
803 -- debug message
804 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
806 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.gen_matching_sql',
807 'gen_matching_sql API has failed');
808 END IF;
809
810 RAISE FND_API.G_EXC_ERROR;
811 END IF;
812
813 -- debug message
814 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
815 FND_LOG.string(FND_LOG.LEVEL_EVENT,
816 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.gen_matching_sql',
817 'gen_matching_sql API has completed with success');
818 END IF;
819
820 /* PERSON_ID required for OSO TAP */
821 IF (p_source_id = -1001) THEN
822 BEGIN
823 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD DISABLE';
824 EXCEPTION
825 WHEN OTHERS THEN
826 NULL;
827 END;
828
829 FORALL i IN l_terr_change_tab.terr_id.FIRST .. l_terr_change_tab.terr_id.LAST
830 UPDATE jtf_terr_rsc_all jtr
831 SET jtr.person_id =
832 ( SELECT jrrev.source_id
833 FROM jtf_rs_resource_extns_vl jrrev
834 WHERE jrrev.category = 'EMPLOYEE'
835 AND jrrev.resource_id = jtr.resource_id )
836 WHERE jtr.resource_type= 'RS_EMPLOYEE'
837 AND jtr.terr_id = l_terr_change_tab.terr_id(i);
838
839 BEGIN
840 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD ENABLE';
841 EXCEPTION
842 WHEN OTHERS THEN
843 NULL;
844 END;
845
846 END IF;
847
848 IF (p_mode = 'DATE EFFECTIVE') THEN
849 SELECT denorm_dea_value_table_name
850 INTO l_table_name
851 FROM jtf_sources_all
852 WHERE source_id = p_source_id;
853 ELSE
854 SELECT denorm_value_table_name
855 INTO l_table_name
856 FROM jtf_sources_all
857 WHERE source_id = p_source_id;
858 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
859
860 -- Update theabsolute rank in jtf_terr_denorm_rules_all for all service territories when the STAR is run for Service.
861 IF (p_source_id = -1002) THEN
862 UPDATE jtf_terr_denorm_rules_all jtda
863 SET absolute_rank = ( select absolute_rank from jtf_terr_all jta where jta.terr_id = jtda.terr_id)
864 where jtda.source_id = -1002;
865 END IF;
866
867 IF ( p_source_id = -1002 ) THEN
868 l_denorm_count := 0 ;
869 EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || l_table_name || ' where ROWNUM = 1' INTO l_denorm_count;
870 /* Create index on the denorm value table */
871 /* Dont create the index on the denorm table if the there are no rows in the denorm table*/
872 IF ( l_denorm_count = 1 ) THEN
873 JTY_TERR_DENORM_RULES_PVT.CREATE_DNMVAL_INDEX (
874 p_table_name => l_table_name,
875 p_source_id => p_source_id,
876 p_mode => p_mode,
877 x_Return_Status => l_Return_Status);
878 END IF;
879 ELSE
880 /* Create index on the denorm value table */
881 JTY_TERR_DENORM_RULES_PVT.CREATE_DNMVAL_INDEX (
882 p_table_name => l_table_name,
883 p_source_id => p_source_id,
884 p_mode => p_mode,
885 x_Return_Status => l_Return_Status);
886 END IF;
887
888 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
889 -- debug message
890 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
891 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
892 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.CREATE_DNMVAL_INDEX',
893 'JTY_TERR_DENORM_RULES_PVT.CREATE_DNMVAL_INDEX API has failed');
894 END IF;
895
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898
899 ELSE
900 -- debug message
901 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
902 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
903 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.no_of_terr',
904 'No territories processed');
905 END IF;
906
907 END IF; /* IF (l_no_of_records > 0) */
908
909 /* if batch mode is not enabled for the usage, delete all the entries processed from the changed table */
910 IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
911 SELECT count(*)
912 INTO l_batch_enabled
913 FROM jty_trans_usg_pgm_details a
914 WHERE a.source_id = p_source_id
915 AND a.batch_enable_flag = 'Y';
916
917 IF (l_batch_enabled = 0) THEN
918 DELETE jty_changed_terrs
919 WHERE star_request_id = g_request_id;
920 END IF;
921 END IF;
922
923 retcode := 0;
924 errbuf := null;
925 l_end_date := SYSDATE;
926
927 UPDATE JTY_CONC_REQ_SUMM
928 SET requested_by = l_user_id
929 ,request_date = l_start_date
930 ,responsibility_application_id = l_resp_appl_id
931 ,responsibility_id = l_resp_id
932 ,last_updated_by = l_user_id
933 ,last_update_date = l_start_date
934 ,last_update_login = l_login_id
935 ,start_date = l_start_date
936 ,end_date = l_end_date
937 ,param2 = p_mode
938 ,param3 = TO_CHAR(l_param_start_date, 'DD/MM/YYYY HH24:MI:SS')
939 ,param4 = TO_CHAR(l_param_end_date, 'DD/MM/YYYY HH24:MI:SS')
940 ,param5 = null
941 ,program_application_id = l_pgm_appl_id
942 ,errbuf = errbuf
943 ,request_id = g_request_id
944 ,conc_program_id = l_conc_pgm_id
945 WHERE program_name = 'JTY_STAR'
946 AND param1 = to_char(p_source_id)
947 AND retcode = retcode
948 AND param2 = p_mode;
949
950 IF (SQL%ROWCOUNT = 0) THEN
951 INSERT INTO JTY_CONC_REQ_SUMM (
952 conc_req_id
953 ,requested_by
954 ,request_date
955 ,responsibility_application_id
956 ,responsibility_id
957 ,last_updated_by
958 ,last_update_date
959 ,last_update_login
960 ,start_date
961 ,end_date
962 ,param1
963 ,param2
964 ,param3
965 ,param4
966 ,param5
967 ,program_application_id
968 ,program_name
969 ,retcode
970 ,errbuf
971 ,request_id
972 ,conc_program_id
973 ,object_version_number)
974 VALUES (
975 jty_conc_req_summ_s.nextval
976 ,l_user_id
977 ,l_start_date
978 ,l_resp_appl_id
979 ,l_resp_id
980 ,l_user_id
981 ,l_start_date
982 ,l_login_id
983 ,l_start_date
984 ,l_end_date
985 ,TO_CHAR(p_source_id)
986 ,p_mode
987 ,TO_CHAR(l_param_start_date, 'DD/MM/YYYY HH24:MI:SS')
988 ,TO_CHAR(l_param_end_date, 'DD/MM/YYYY HH24:MI:SS')
989 ,null
990 ,l_pgm_appl_id
991 ,l_pgm_name
992 ,retcode
993 ,errbuf
994 ,g_request_id
995 ,l_conc_pgm_id
996 ,0);
997 END IF;
998
999 -- debug message
1000 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1001 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1002 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.end',
1003 'End of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine');
1004 END IF;
1005
1006 EXCEPTION
1007 WHEN FND_API.G_EXC_ERROR THEN
1008 RETCODE := 2;
1009 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1011 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.g_exc_error',
1012 'API JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine has failed with FND_API.G_EXC_ERROR exception');
1013 END IF;
1014
1015 WHEN OTHERS THEN
1016 RETCODE := 2;
1017 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1018 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1020 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.others',
1021 substr(errbuf, 1, 4000));
1022 END IF;
1023 END gen_rule_engine;
1024
1025 END JTY_TERR_ENGINE_GEN_PVT;