[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.26 2011/04/26 09:39:18 sseshaiy 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 PROCEDURE jty_log(p_log_level IN NUMBER
31 ,p_module IN VARCHAR2
32 ,p_message IN VARCHAR2)
33 IS
34 pragma autonomous_transaction;
35 BEGIN
36 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
37 FND_LOG.string(p_log_level, p_module, p_message);
38 commit;
39 END IF;
40 END;
41
42 /* this procedure looks at the table jty_changed_terrs and retrive the master */
43 /* list that need to be processed by incremental star */
44 PROCEDURE get_terr_for_incr_star (
45 p_source_id IN NUMBER,
46 p_request_id IN NUMBER,
47 p_terr_change_tab OUT NOCOPY terr_change_type,
48 retcode OUT NOCOPY VARCHAR2,
49 errbuf OUT NOCOPY VARCHAR2
50 )
51 AS
52
53 CURSOR c_changed_terrs (cl_request_id IN NUMBER) IS
54 SELECT a.terr_id,
55 a.rank_calc_flag,
56 a.process_attr_values_flag,
57 a.matching_sql_flag,
58 a.hier_processing_flag
59 FROM jty_changed_terrs a
60 WHERE a.star_request_id = cl_request_id
61 AND a.source_id = p_source_id
62 AND (a.rank_calc_flag <> 'N' OR a.process_attr_values_flag <> 'N' OR
63 a.matching_sql_flag <> 'N' OR a.hier_processing_flag <> 'N')
64 AND NOT EXISTS (
65 SELECT jt.terr_id
66 FROM jtf_terr_all jt
67 WHERE jt.end_date_active < sysdate
68 OR jt.start_date_active > sysdate
69 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
70 START WITH jt.terr_id = a.terr_id )
71 UNION ALL
72 SELECT a.terr_id,
73 'N',
74 'D',
75 'Y',
76 'D'
77 FROM jtf_terr_all a
78 WHERE (a.start_date_active > sysdate
79 OR a.end_date_active < sysdate)
80 AND exists (
81 SELECT 1
82 FROM jty_changed_terrs b
83 WHERE b.terr_id = a.terr_id
84 AND b.star_request_id = cl_request_id
85 AND b.source_id = p_source_id);
86
87 CURSOR c_child_terrs (cl_terr_id IN NUMBER) IS
88 SELECT terr_id
89 FROM jtf_terr_all
90 START WITH terr_id = cl_terr_id
91 CONNECT BY PRIOR terr_id = parent_territory_id;
92
93 TYPE l_terr_id_tbl_type IS TABLE OF jty_changed_terrs.terr_id%TYPE;
94 TYPE l_rank_tbl_type IS TABLE OF jty_changed_terrs.rank_calc_flag%TYPE;
95 TYPE l_attr_values_tbl_type IS TABLE OF jty_changed_terrs.process_attr_values_flag%TYPE;
96 TYPE l_match_sql_tbl_type IS TABLE OF jty_changed_terrs.matching_sql_flag%TYPE;
97 TYPE l_hier_tbl_type IS TABLE OF jty_changed_terrs.hier_processing_flag%TYPE;
98
99 l_terr_id_tbl l_terr_id_tbl_type;
100 l_rank_tbl l_rank_tbl_type;
101 l_attr_values_tbl l_attr_values_tbl_type;
102 l_match_sql_tbl l_match_sql_tbl_type;
103 l_hier_tbl l_hier_tbl_type;
104 BEGIN
105
106 -- debug message
107 jty_log(FND_LOG.LEVEL_PROCEDURE,
108 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star.start',
109 'Start of the procedure JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
110
111 /* Insert into jty_changed_terrs the territories that */
112 /* have become active after the last run of STAR */
113 MERGE INTO jty_changed_terrs A
114 USING
115 ( SELECT
116 a.terr_id terr_id,
117 b.source_id source_id
118 FROM jtf_terr_all a,
119 jtf_terr_usgs_all b
120 WHERE a.terr_id = b.terr_id
121 AND b.source_id = p_source_id
122 AND a.start_date_active >
123 (SELECT max(end_date)
124 FROM jty_conc_req_summ a
125 WHERE a.program_name = 'JTY_STAR'
126 AND a.param1 = to_char(p_source_id)
127 AND a.retcode = 0)
128 AND a.start_date_active < sysdate
129 AND a.end_date_active > sysdate
130 AND NOT EXISTS (
131 SELECT jt.terr_id
132 FROM jtf_terr_all jt
133 WHERE jt.end_date_active < sysdate
134 OR jt.start_date_active > sysdate
135 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
136 START WITH jt.terr_id = a.terr_id ) ) S
137 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
138 WHEN MATCHED THEN
139 UPDATE SET
140 A.rank_calc_flag = 'Y'
141 ,A.process_attr_values_flag = 'I'
142 ,A.matching_sql_flag = 'Y'
143 ,A.hier_processing_flag = 'I'
144 WHEN NOT MATCHED THEN
145 INSERT (
146 A.CHANGED_TERRITORY_ID
147 ,A.OBJECT_VERSION_NUMBER
148 ,A.TERR_ID
149 ,A.SOURCE_ID
150 ,A.CHANGE_TYPE
151 ,A.RANK_CALC_FLAG
152 ,A.PROCESS_ATTR_VALUES_FLAG
153 ,A.MATCHING_SQL_FLAG
154 ,A.HIER_PROCESSING_FLAG)
155 VALUES (
156 jty_changed_terrs_s.nextval
157 ,0
158 ,S.terr_id
159 ,S.source_id
160 ,'UPDATE'
161 ,'Y'
162 ,'I'
163 ,'Y'
164 ,'I');
165
166 /* Insert into jty_changed_terrs the territories that */
167 /* have become inactive after the last run of STAR */
168 MERGE INTO jty_changed_terrs A
169 USING
170 ( SELECT
171 a.terr_id terr_id,
172 b.source_id source_id
173 FROM jtf_terr_all a,
174 jtf_terr_usgs_all b
175 WHERE a.terr_id = b.terr_id
176 AND b.source_id = p_source_id
177 AND a.end_date_active >
178 (SELECT max(end_date)
179 FROM jty_conc_req_summ a
180 WHERE a.program_name = 'JTY_STAR'
181 AND a.param1 = to_char(p_source_id)
182 AND a.retcode = 0)
183 AND a.end_date_active < sysdate ) S
184 ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
185 WHEN MATCHED THEN
186 UPDATE SET
187 A.rank_calc_flag = 'N'
188 ,A.process_attr_values_flag = 'D'
189 ,A.matching_sql_flag = 'Y'
190 ,A.hier_processing_flag = 'D'
191 WHEN NOT MATCHED THEN
192 INSERT (
193 A.CHANGED_TERRITORY_ID
194 ,A.OBJECT_VERSION_NUMBER
195 ,A.TERR_ID
196 ,A.SOURCE_ID
197 ,A.CHANGE_TYPE
198 ,A.RANK_CALC_FLAG
199 ,A.PROCESS_ATTR_VALUES_FLAG
200 ,A.MATCHING_SQL_FLAG
201 ,A.HIER_PROCESSING_FLAG)
202 VALUES (
203 jty_changed_terrs_s.nextval
204 ,0
205 ,S.terr_id
206 ,S.source_id
207 ,'UPDATE'
208 ,'N'
209 ,'D'
210 ,'Y'
211 ,'D');
212
213 DELETE jty_changed_terrs_gt;
214
215 UPDATE jty_changed_terrs a
216 SET a.star_request_id = p_request_id
217 WHERE a.star_request_id IS NULL
218 AND a.source_id = p_source_id;
219
220 OPEN c_changed_terrs(p_request_id);
221 FETCH c_changed_terrs BULK COLLECT INTO
222 l_terr_id_tbl,
223 l_rank_tbl,
224 l_attr_values_tbl,
225 l_match_sql_tbl,
226 l_hier_tbl;
227 CLOSE c_changed_terrs;
228
229 IF (l_terr_id_tbl.COUNT > 0) THEN
230 FOR i IN l_terr_id_tbl.FIRST .. l_terr_id_tbl.LAST LOOP
231 INSERT INTO jty_changed_terrs_gt (
232 terr_id
233 ,rank_calc_flag
234 ,process_attr_values_flag
235 ,matching_sql_flag
236 ,hier_processing_flag)
237 VALUES (
238 l_terr_id_tbl(i)
239 ,l_rank_tbl(i)
240 ,l_attr_values_tbl(i)
241 ,l_match_sql_tbl(i)
242 ,l_hier_tbl(i));
243
244 FOR child_terrs IN c_child_terrs (l_terr_id_tbl(i)) LOOP
245 UPDATE jty_changed_terrs_gt
246 SET rank_calc_flag = decode(rank_calc_flag, 'Y', 'Y', l_rank_tbl(i)),
247 process_attr_values_flag =
248 decode(process_attr_values_flag,
249 'I', 'I',
250 'D', decode(l_attr_values_tbl(i), 'I', 'I', 'D'),
251 l_attr_values_tbl(i)),
252 matching_sql_flag = decode(matching_sql_flag, 'Y', 'Y', l_match_sql_tbl(i)),
253 hier_processing_flag =
254 decode(hier_processing_flag,
255 'I', 'I',
256 'D', decode(l_hier_tbl(i), 'I', 'I', 'D'),
257 l_hier_tbl(i))
258 WHERE terr_id = child_terrs.terr_id;
259
260 IF (SQL%ROWCOUNT = 0) THEN
261 INSERT INTO jty_changed_terrs_gt (
262 terr_id
263 ,rank_calc_flag
264 ,process_attr_values_flag
265 ,matching_sql_flag
266 ,hier_processing_flag)
267 VALUES (
268 child_terrs.terr_id
269 ,l_rank_tbl(i)
270 ,l_attr_values_tbl(i)
271 ,l_match_sql_tbl(i)
272 ,l_hier_tbl(i));
273 END IF;
274
275 END LOOP; /* end loop FOR child_terrs IN c_child_terrs (l_terr_id_tbl(i)) */
276 END LOOP; /* end loop FOR i IN l_terr_id_tbl.FIRST .. l_terr_id_tbl.LAST */
277 END IF; /* end IF (l_terr_id_tbl.COUNT > 0) */
278
279 SELECT
280 a.terr_id
281 ,a.rank_calc_flag
282 ,a.process_attr_values_flag
283 ,a.matching_sql_flag
284 ,a.hier_processing_flag
285 ,b.rank
286 ,b.parent_territory_id
287 ,JTY_TERR_DENORM_RULES_PVT.get_level_from_root(a.terr_id)
288 ,b.num_winners
289 ,b.org_id
290 ,c.num_winners
291 ,b.start_date_active
292 ,b.end_date_active
293 BULK COLLECT INTO
294 p_terr_change_tab.terr_id
295 ,p_terr_change_tab.rank_calc_flag
296 ,p_terr_change_tab.attr_processing_flag
297 ,p_terr_change_tab.matching_sql_flag
298 ,p_terr_change_tab.hier_processing_flag
299 ,p_terr_change_tab.terr_rank
300 ,p_terr_change_tab.parent_terr_id
301 ,p_terr_change_tab.level_from_root
302 ,p_terr_change_tab.num_winners
303 ,p_terr_change_tab.org_id
304 ,p_terr_change_tab.parent_num_winners
305 ,p_terr_change_tab.start_date
306 ,p_terr_change_tab.end_date
307 FROM
308 jty_changed_terrs_gt a
309 ,jtf_terr_all b
310 ,jtf_terr_all c
311 WHERE a.terr_id = b.terr_id(+)
312 AND b.parent_territory_id = c.terr_id(+)
313 AND b.org_id = c.org_id(+);
314
315 -- debug message
316 jty_log(FND_LOG.LEVEL_PROCEDURE,
317 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star.end',
318 'End of the procedure JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
319
320 retcode := 0;
321 errbuf := null;
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 RETCODE := 2;
326 ERRBUF := SQLCODE || ' : ' || SQLERRM;
327 jty_log(FND_LOG.LEVEL_EXCEPTION,
328 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_incr_star.others',
329 substr(errbuf, 1, 4000));
330
331 END get_terr_for_incr_star;
332
333 /* this procedure looks at the table jty_changed_dea_terrs and retrive the master */
334 /* list that need to be processed by dea incremental star */
335 PROCEDURE get_terr_for_dea_incr_star (
336 p_source_id IN NUMBER,
337 p_request_id IN NUMBER,
338 p_terr_change_tab OUT NOCOPY terr_change_type,
339 retcode OUT NOCOPY VARCHAR2,
340 errbuf OUT NOCOPY VARCHAR2
341 )
342 AS
343
344 CURSOR c_changed_terrs (cl_request_id IN NUMBER, cl_start_date Date, cl_end_date Date) IS
345 SELECT a.terr_id,
346 a.rank_calc_flag,
347 a.process_attr_values_flag,
348 a.matching_sql_flag,
349 a.hier_processing_flag
350 FROM jty_changed_dea_terrs a
351 WHERE a.star_request_id = cl_request_id
352 AND a.source_id = p_source_id
353 AND (a.rank_calc_flag <> 'N' OR a.process_attr_values_flag <> 'N' OR
354 a.matching_sql_flag <> 'N' OR a.hier_processing_flag <> 'N')
355 AND NOT EXISTS (
356 SELECT jt.terr_id
357 FROM jtf_terr_all jt
358 WHERE jt.end_date_active < to_date(cl_start_date,'dd-mm-rr')
359 OR jt.start_date_active > to_date(cl_end_date,'dd-mm-rr')
360 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
361 START WITH jt.terr_id = a.terr_id );
362
363 CURSOR c_child_terrs (cl_terr_id IN NUMBER) IS
364 SELECT terr_id
365 FROM jtf_terr_all
366 START WITH terr_id = cl_terr_id
367 CONNECT BY PRIOR terr_id = parent_territory_id;
368
369 TYPE l_terr_id_tbl_type IS TABLE OF jty_changed_dea_terrs.terr_id%TYPE;
370 TYPE l_rank_tbl_type IS TABLE OF jty_changed_dea_terrs.rank_calc_flag%TYPE;
371 TYPE l_attr_values_tbl_type IS TABLE OF jty_changed_dea_terrs.process_attr_values_flag%TYPE;
372 TYPE l_match_sql_tbl_type IS TABLE OF jty_changed_dea_terrs.matching_sql_flag%TYPE;
373 TYPE l_hier_tbl_type IS TABLE OF jty_changed_dea_terrs.hier_processing_flag%TYPE;
374
375 l_terr_id_tbl l_terr_id_tbl_type;
376 l_rank_tbl l_rank_tbl_type;
377 l_attr_values_tbl l_attr_values_tbl_type;
378 l_match_sql_tbl l_match_sql_tbl_type;
379 l_hier_tbl l_hier_tbl_type;
380 l_dea_incr_start_date DATE;
381 l_dea_incr_end_date DATE;
382 l_query varchar2(1000);
383
384 BEGIN
385
386 -- debug message
387 jty_log(FND_LOG.LEVEL_PROCEDURE,
388 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_dea_incr_star.start',
389 'Start of the procedure JTY_TERR_ENGINE_GEN_PVT.get_terr_for_dea_incr_star ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
390
391
392 SELECT to_date(param3, 'dd/mm/yyyy hh24:mi:ss'),to_date(param4, 'dd/mm/yyyy hh24:mi:ss')
393 into l_dea_incr_start_date,l_dea_incr_end_date
394 FROM jty_conc_req_summ a
395 WHERE a.program_name = 'JTY_STAR'
396 AND a.param1 = to_char(p_source_id)
397 AND a.param2 = 'DATE EFFECTIVE'
398 AND a.retcode = 0
399 and rownum = 1
400 order by end_date ;
401
402 DELETE jty_changed_terrs_gt;
403
404 UPDATE jty_changed_dea_terrs a
405 SET a.star_request_id = p_request_id
406 WHERE a.star_request_id IS NULL
407 AND a.source_id = p_source_id;
408
409 OPEN c_changed_terrs(p_request_id,l_dea_incr_start_date,l_dea_incr_end_date);
410 FETCH c_changed_terrs BULK COLLECT INTO
411 l_terr_id_tbl,
412 l_rank_tbl,
413 l_attr_values_tbl,
414 l_match_sql_tbl,
415 l_hier_tbl;
416 CLOSE c_changed_terrs;
417
418 IF (l_terr_id_tbl.COUNT > 0) THEN
419 FOR i IN l_terr_id_tbl.FIRST .. l_terr_id_tbl.LAST LOOP
420 INSERT INTO jty_changed_terrs_gt (
421 terr_id
422 ,rank_calc_flag
423 ,process_attr_values_flag
424 ,matching_sql_flag
425 ,hier_processing_flag)
426 VALUES (
427 l_terr_id_tbl(i)
428 ,l_rank_tbl(i)
429 ,l_attr_values_tbl(i)
430 ,l_match_sql_tbl(i)
431 ,l_hier_tbl(i));
432
433
434 FOR child_terrs IN c_child_terrs (l_terr_id_tbl(i)) LOOP
435 UPDATE jty_changed_terrs_gt
436 SET rank_calc_flag = decode(rank_calc_flag, 'Y', 'Y', l_rank_tbl(i)),
437 process_attr_values_flag =
438 decode(process_attr_values_flag,
439 'I', 'I',
440 'D', decode(l_attr_values_tbl(i), 'I', 'I', 'D'),
441 l_attr_values_tbl(i)),
442 matching_sql_flag = decode(matching_sql_flag, 'Y', 'Y', l_match_sql_tbl(i)),
443 hier_processing_flag =
444 decode(hier_processing_flag,
445 'I', 'I',
446 'D', decode(l_hier_tbl(i), 'I', 'I', 'D'),
447 l_hier_tbl(i))
448 WHERE terr_id = child_terrs.terr_id;
449
450
451 IF (SQL%ROWCOUNT = 0) THEN
452 INSERT INTO jty_changed_terrs_gt (
453 terr_id
454 ,rank_calc_flag
455 ,process_attr_values_flag
456 ,matching_sql_flag
457 ,hier_processing_flag)
458 VALUES (
459 child_terrs.terr_id
460 ,l_rank_tbl(i)
461 ,l_attr_values_tbl(i)
462 ,l_match_sql_tbl(i)
463 ,l_hier_tbl(i));
464 END IF;
465
466 END LOOP; /* end loop FOR child_terrs IN c_child_terrs (l_terr_id_tbl(i)) */
467 END LOOP; /* end loop FOR i IN l_terr_id_tbl.FIRST .. l_terr_id_tbl.LAST */
468 END IF; /* end IF (l_terr_id_tbl.COUNT > 0) */
469
470 -- l_query := 'create table jty_changed_terrs_rk_test as select * from jty_changed_terrs_gt';
471 --EXECUTE IMMEDIATE l_query;
472
473 SELECT
474 a.terr_id
475 ,a.rank_calc_flag
476 ,a.process_attr_values_flag
477 ,a.matching_sql_flag
478 ,a.hier_processing_flag
479 ,b.rank
480 ,b.parent_territory_id
481 ,JTY_TERR_DENORM_RULES_PVT.get_level_from_root(a.terr_id)
482 ,b.num_winners
483 ,b.org_id
484 ,c.num_winners
485 ,b.start_date_active
486 ,b.end_date_active
487 BULK COLLECT INTO
488 p_terr_change_tab.terr_id
489 ,p_terr_change_tab.rank_calc_flag
490 ,p_terr_change_tab.attr_processing_flag
491 ,p_terr_change_tab.matching_sql_flag
492 ,p_terr_change_tab.hier_processing_flag
493 ,p_terr_change_tab.terr_rank
494 ,p_terr_change_tab.parent_terr_id
495 ,p_terr_change_tab.level_from_root
496 ,p_terr_change_tab.num_winners
497 ,p_terr_change_tab.org_id
498 ,p_terr_change_tab.parent_num_winners
499 ,p_terr_change_tab.start_date
500 ,p_terr_change_tab.end_date
501 FROM
502 jty_changed_terrs_gt a
503 ,jtf_terr_all b
504 ,jtf_terr_all c
505 WHERE a.terr_id = b.terr_id(+)
506 AND b.parent_territory_id = c.terr_id(+)
507 AND b.org_id = c.org_id(+);
508
509
510 -- debug message
511 jty_log(FND_LOG.LEVEL_PROCEDURE,
512 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_dea_incr_star.end',
513 'End of the procedure JTY_TERR_ENGINE_GEN_PVT.get_terr_for_dea_incr_star ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
514
515 retcode := 0;
516 errbuf := null;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 RETCODE := 2;
521 ERRBUF := SQLCODE || ' : ' || SQLERRM;
522 jty_log(FND_LOG.LEVEL_EXCEPTION,
523 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.get_terr_for_dea_incr_star.others',
524 substr(errbuf, 1, 4000));
525
526 END get_terr_for_dea_incr_star;
527
528 /* this procedure calls APIs to generate real time and batch matching SQLs */
529 PROCEDURE gen_matching_sql (
530 p_source_id IN NUMBER,
531 p_mode IN VARCHAR2,
532 p_terr_change_tab terr_change_type,
533 p_start_date IN DATE,
534 p_end_date IN DATE,
535 x_Return_Status OUT NOCOPY VARCHAR2,
536 x_Msg_Count OUT NOCOPY NUMBER,
537 x_Msg_Data OUT NOCOPY VARCHAR2,
538 errbuf OUT NOCOPY VARCHAR2,
539 retcode OUT NOCOPY VARCHAR2
540 )
541 AS
542
543 CURSOR c_trans_types (cl_source_id NUMBER) IS
544 SELECT qual_type_id
545 FROM jtf_qual_type_usgs_all
546 WHERE source_id = cl_source_id
547 AND qual_type_id <> -1001;
548
549 CURSOR c_qual_rel_sets(cl_source_id number, cl_qual_type_id number) is
550 select distinct jtqu.qual_relation_product
551 from jtf_terr_qtype_usgs_all jtqu
552 ,jtf_qual_type_usgs_all jqtu
553 where jqtu.source_id = cl_source_id
554 and jqtu.qual_type_id = cl_qual_type_id
555 and jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
556 and jtqu.qual_relation_product <> 1
557 and exists (
558 select /*+ index_ffs(jtdr jtf_terr_denorm_rules_n1) */ 1
559 from jtf_terr_denorm_rules_all jtdr
560 where jtdr.terr_id = jtqu.terr_id
561 and jtqu.terr_id = jtdr.related_terr_id );
562
563 CURSOR c_dea_qual_rel_sets(cl_source_id number, cl_qual_type_id number) is
564 select distinct jtqu.qual_relation_product
565 from jtf_terr_qtype_usgs_all jtqu
566 ,jtf_qual_type_usgs_all jqtu
567 where jqtu.source_id = cl_source_id
568 and jqtu.qual_type_id = cl_qual_type_id
569 and jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
570 and jtqu.qual_relation_product <> 1
571 and exists (
572 select /*+ index_ffs(jtdr jty_denorm_dea_rules_n1) */ 1
573 from jty_denorm_dea_rules_all jtdr
574 where jtdr.terr_id = jtqu.terr_id
575 and jtqu.terr_id = jtdr.related_terr_id );
576
577 TYPE l_qual_type_id_tbl_type IS TABLE OF jtf_qual_type_usgs_all.qual_type_id%TYPE;
578
579 l_qual_type_id_tbl l_qual_type_id_tbl_type;
580 l_qual_prd_tbl qual_prd_tbl_type;
581
582 l_new_qual_prd BOOLEAN;
583 l_sysdate DATE;
584 l_qual_relation_product NUMBER;
585
586 BEGIN
587
588 -- debug message
589 jty_log(FND_LOG.LEVEL_PROCEDURE,
590 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.start',
591 'Start of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
592
593 l_sysdate := SYSDATE;
594 l_qual_prd_tbl := qual_prd_tbl_type();
595
596 /* get all the transaction types */
597 OPEN c_trans_types(p_source_id);
598 FETCH c_trans_types BULK COLLECT INTO
599 l_qual_type_id_tbl;
600 CLOSE c_trans_types;
601
602 IF (l_qual_type_id_tbl.COUNT > 0) THEN
603 FOR i IN l_qual_type_id_tbl.FIRST .. l_qual_type_id_tbl.LAST LOOP
604
605 /* always generate the real time matching sql */
606 JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql (
607 p_source_id => p_source_id,
608 p_trans_id => l_qual_type_id_tbl(i),
609 p_mode => p_mode,
610 p_start_date => p_start_date,
611 p_end_date => p_end_date,
612 errbuf => errbuf,
613 retcode => retcode);
614 IF (retcode <> 0) THEN
615 -- debug message
616 jty_log(FND_LOG.LEVEL_EXCEPTION,
617 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.gen_real_time_sql',
618 'JTY_TERR_ENGINE_GEN2_PVT.gen_real_time_sql API has failed');
619
620 RAISE FND_API.G_EXC_ERROR;
621 END IF;
622
623
624 /* delete the old records from the tables jtf_tae_qual_products, jtf_tae_qual_factors */
625 /* and jtf_tae_qual_prod_factors if mode is total or incremental */
626 /* delete the old records from the tables jtf_dea_attr_products, jtf_dea_attr_factors */
627 /* and jtf_dea_attr_prod_factors if mode is date effective */
628 JTY_TAE_CONTROL_PVT.delete_combinations(
629 p_source_id => p_source_id,
630 p_trans_id => l_qual_type_id_tbl(i),
631 p_mode => p_mode,
632 x_Return_Status => x_return_status,
633 x_Msg_Count => x_msg_count,
634 x_Msg_Data => x_msg_data,
635 ERRBUF => errbuf,
636 RETCODE => retcode);
637
638 IF (retcode <> 0) THEN
639 -- debug message
640 jty_log(FND_LOG.LEVEL_EXCEPTION,
641 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.delete_combinations',
642 'JTY_TAE_CONTROL_PVT.delete_combinations API has failed');
643
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646
647 /* if mode is total or date effective, get all the qualifier combinations for the active territories */
648 /* if mode is incremental, get all the distinct qualifier comb for the territories with */
649 /* matching_sql_flag = 'Y' in p_terr_change_tab */
650 IF (p_mode = 'TOTAL') THEN
651 OPEN c_qual_rel_sets(p_source_id, l_qual_type_id_tbl(i));
652 FETCH c_qual_rel_sets BULK COLLECT INTO
653 l_qual_prd_tbl;
654 CLOSE c_qual_rel_sets;
655 ELSIF (p_mode = 'DATE EFFECTIVE') THEN
656 OPEN c_dea_qual_rel_sets(p_source_id, l_qual_type_id_tbl(i));
657 FETCH c_dea_qual_rel_sets BULK COLLECT INTO
658 l_qual_prd_tbl;
659 CLOSE c_dea_qual_rel_sets;
660 ELSIF (p_mode = 'INCREMENTAL') THEN
661 FOR j in p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST LOOP
662 IF (p_terr_change_tab.matching_sql_flag(j) = 'Y') THEN
663
664 BEGIN
665 SELECT a.qual_relation_product
666 INTO l_qual_relation_product
667 FROM jtf_terr_qtype_usgs_all a,
668 jtf_qual_type_usgs_all b,
669 jtf_terr_all c
670 WHERE a.qual_type_usg_id = b.qual_type_usg_id
671 AND b.source_id = p_source_id
672 AND b.qual_type_id = l_qual_type_id_tbl(i)
673 AND a.terr_id = p_terr_change_tab.terr_id(j)
674 AND c.terr_id = a.terr_id
675 AND c.start_date_active < sysdate
676 AND c.end_date_active > sysdate
677 AND a.qual_relation_product <> 1
678 AND NOT EXISTS (
679 SELECT 1
680 FROM jtf_tae_qual_products c
681 WHERE c.source_id = p_source_id
682 AND c.trans_object_type_id = l_qual_type_id_tbl(i)
683 AND c.relation_product = a.qual_relation_product);
684
685 /* check if the qual rel prd alreday exists in the pl/sql table that will be processed */
686 l_new_qual_prd := TRUE;
687 IF (l_qual_prd_tbl.COUNT > 0) THEN
688 FOR k in l_qual_prd_tbl.FIRST .. l_qual_prd_tbl.LAST LOOP
689 IF (l_qual_relation_product = l_qual_prd_tbl(k)) THEN
690 l_new_qual_prd := FALSE;
691 exit;
692 END IF;
693 END LOOP;
694 END IF;
695
696 /* insert the current qual rel prd into the pl/sql table only if it does not exist */
697 IF (l_new_qual_prd) THEN
698 l_qual_prd_tbl.EXTEND();
699 l_qual_prd_tbl(l_qual_prd_tbl.COUNT) := l_qual_relation_product;
700 END IF;
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 NULL;
704 END;
705
706 END IF; /* end IF (p_terr_change_tab.matching_sql_flag = 'Y') */
707 END LOOP; /* end loop FOR j in p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST */
708 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
709 FOR j in p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST LOOP
710 IF (p_terr_change_tab.matching_sql_flag(j) = 'Y') THEN
711
712 BEGIN
713 SELECT a.qual_relation_product
714 INTO l_qual_relation_product
715 FROM jtf_terr_qtype_usgs_all a,
716 jtf_qual_type_usgs_all b,
717 jtf_terr_all c
718 WHERE a.qual_type_usg_id = b.qual_type_usg_id
719 AND b.source_id = p_source_id
720 AND b.qual_type_id = l_qual_type_id_tbl(i)
721 AND a.terr_id = p_terr_change_tab.terr_id(j)
722 AND c.terr_id = a.terr_id
723 AND c.start_date_active < sysdate
724 AND c.end_date_active > sysdate
725 AND a.qual_relation_product <> 1
726 AND NOT EXISTS (
727 SELECT 1
728 FROM jty_dea_attr_products c
729 WHERE c.source_id = p_source_id
730 AND c.trans_type_id = l_qual_type_id_tbl(i)
731 AND c.attr_relation_product = a.qual_relation_product);
732
733 /* check if the qual rel prd alreday exists in the pl/sql table that will be processed */
734 l_new_qual_prd := TRUE;
735 IF (l_qual_prd_tbl.COUNT > 0) THEN
736 FOR k in l_qual_prd_tbl.FIRST .. l_qual_prd_tbl.LAST LOOP
737 IF (l_qual_relation_product = l_qual_prd_tbl(k)) THEN
738 l_new_qual_prd := FALSE;
739 exit;
740 END IF;
741 END LOOP;
742 END IF;
743
744 /* insert the current qual rel prd into the pl/sql table only if it does not exist */
745 IF (l_new_qual_prd) THEN
746 l_qual_prd_tbl.EXTEND();
747 l_qual_prd_tbl(l_qual_prd_tbl.COUNT) := l_qual_relation_product;
748 END IF;
749 EXCEPTION
750 WHEN NO_DATA_FOUND THEN
751 NULL;
752 END;
753
754 END IF; /* end IF (p_terr_change_tab.matching_sql_flag = 'Y') */
755 END LOOP; /* end loop FOR j in p_terr_change_tab.terr_id.FIRST .. p_terr_change_tab.terr_id.LAST */
756 END IF; /* end IF (p_mode = 'TOTAL') */
757
758 /* generate the batch matching sql for all qualifier combinations present in l_qual_type_id_tbl */
759 IF (l_qual_prd_tbl.COUNT > 0) THEN
760 jty_tae_gen_pvt.gen_batch_sql(
761 p_source_id => p_source_id,
762 p_trans_id => l_qual_type_id_tbl(i),
763 p_mode => p_mode,
764 p_qual_prd_tbl => l_qual_prd_tbl,
765 x_return_status => x_return_status,
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data,
768 errbuf => errbuf,
769 retcode => retcode);
770
771 IF (retcode <> 0) THEN
772 -- debug message
773 jty_log(FND_LOG.LEVEL_EXCEPTION,
774 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.gen_batch_sql',
775 'jty_tae_gen_pvt.gen_batch_sql API has failed');
776
777 RAISE FND_API.G_EXC_ERROR;
778 END IF;
779
780 END IF; /* end IF (l_qual_prd_tbl.COUNT > 0) */
781
782 l_qual_prd_tbl.TRIM(l_qual_prd_tbl.COUNT);
783
784 END LOOP; /* end loop FOR i IN l_qual_type_id_tbl.FIRST .. l_qual_type_id_tbl.LAST */
785 END IF; /* end IF (l_qual_type_id_tbl.COUNT > 0) */
786
787 -- debug message
788 jty_log(FND_LOG.LEVEL_PROCEDURE,
789 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.end',
790 'End of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
791
792 retcode := 0;
793 errbuf := null;
794
795 EXCEPTION
796 WHEN FND_API.G_EXC_ERROR THEN
797 RETCODE := 2;
798 jty_log(FND_LOG.LEVEL_EXCEPTION,
799 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.g_exc_error',
800 'API JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql has failed with FND_API.G_EXC_ERROR exception');
801
802 WHEN OTHERS THEN
803 RETCODE := 2;
804 ERRBUF := SQLCODE || ' : ' || SQLERRM;
805 jty_log(FND_LOG.LEVEL_EXCEPTION,
806 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.others',
807 substr(errbuf, 1, 4000));
808
809 END gen_matching_sql;
810
811 /* entry point of the concurrent program STAR */
812 PROCEDURE gen_rule_engine (
813 errbuf OUT NOCOPY VARCHAR2,
814 retcode OUT NOCOPY VARCHAR2,
815 p_source_id IN NUMBER,
816 p_mode IN VARCHAR2,
817 p_start_date IN VARCHAR2,
818 p_end_date IN VARCHAR2
819 )
820 AS
821
822 CURSOR csr_get_terr(lp_source_id NUMBER, lp_start_date DATE, lp_end_date DATE) IS
823 SELECT /* index(JTA2 JTF_TERR_U1) */ jta1.terr_id terr_id
824 ,NVL(jta1.rank, 999999999) rank
825 ,jta1.num_winners num_winners
826 ,jta1.org_id org_id
827 ,jta1.parent_territory_id parent_territory_id
828 ,JTY_TERR_DENORM_RULES_PVT.get_level_from_root(jta1.terr_id) level_from_root
829 ,jta2.num_winners parent_num_winners
830 ,'Y' rank_calc_flag
831 ,'I' attr_processing_flag
832 ,'I' hier_processing_flag
833 ,'Y' matching_sql_flag
834 ,jta1.start_date_active start_date
835 ,jta1.end_date_active end_date
836 FROM jtf_terr_usgs_all jtu
837 , jtf_terr_all jta1
838 , jtf_terr_all jta2
839 WHERE jtu.source_id = lp_source_id
840 AND jtu.terr_id = jta1.terr_id
841 AND jta1.terr_id <> 1
842 AND jta1.end_date_active >= lp_start_date
843 AND jta1.start_date_active <= lp_end_date
844 AND jta2.terr_id = jta1.parent_territory_id
845 AND ( jta1.org_id = jta2.org_id OR
846 (jta1.org_id IS NULL AND jta2.org_id IS NULL) )
847 AND NOT EXISTS (
848 SELECT /* index(JT JTF_TERR_U1) */ jt.terr_id
849 FROM jtf_terr_all jt
850 WHERE jt.end_date_active < lp_start_date
851 OR jt.start_date_active > lp_end_date
852 CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
853 START WITH jt.terr_id = jta1.terr_id );
854
855 l_terr_change_tab terr_change_type;
856 l_no_of_records NUMBER;
857 l_batch_enabled NUMBER;
858 l_count NUMBER;
859 l_table_name VARCHAR2(30);
860
861 l_return_status VARCHAR2(10);
862 l_msg_count NUMBER;
863 l_msg_data VARCHAR2(2000);
864
865 l_resp_appl_id NUMBER;
866 l_resp_id NUMBER;
867 l_user_id NUMBER;
868 l_login_id NUMBER;
869 l_sysdate DATE;
870 l_start_date DATE;
871 l_end_date DATE;
872 l_pgm_appl_id NUMBER;
873 l_pgm_name VARCHAR2(360);
874 l_conc_pgm_id NUMBER;
875
876 l_param_start_date DATE;
877 l_param_end_date DATE;
878 l_denorm_count NUMBER;
879 BEGIN
880
881 -- debug message
882 jty_log(FND_LOG.LEVEL_PROCEDURE,
883 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.start',
884 'Start of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
885
886 /* Initialize audit columns */
887 l_start_date := SYSDATE;
888 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
889 l_resp_id := FND_GLOBAL.RESP_ID;
890 l_user_id := FND_GLOBAL.USER_ID;
891 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
892 l_pgm_appl_id := FND_GLOBAL.PROG_APPL_ID;
893 l_conc_pgm_id := FND_GLOBAL.CONC_PROGRAM_ID;
894 l_pgm_name := 'JTY_STAR';
895
896 l_param_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
897 l_param_end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
898
899 /* mark the records in the changed table that will be processed */
900 IF (p_mode = 'TOTAL') THEN
901 UPDATE jty_changed_terrs
902 SET star_request_id = g_request_id
903 WHERE source_id = p_source_id
904 AND star_request_id IS NULL;
905 END IF;
906
907 IF (p_mode = 'DATE EFFECTIVE') THEN
908 UPDATE jty_changed_dea_terrs
909 SET star_request_id = g_request_id
910 WHERE source_id = p_source_id
911 AND star_request_id IS NULL;
912 END IF;
913
914 IF (p_mode = 'DEA INCREMENTAL') THEN
915
916 SELECT to_date(param3, 'dd/mm/yyyy hh24:mi:ss'),to_date(param4, 'dd/mm/yyyy hh24:mi:ss')
917 into l_param_start_date,l_param_end_date
918 FROM jty_conc_req_summ a
919 WHERE a.program_name = 'JTY_STAR'
920 AND a.param1 = to_char(p_source_id)
921 AND a.param2 = 'DATE EFFECTIVE'
922 AND a.retcode = 0
923 and rownum = 1
924 order by end_date ;
925 END IF;
926
927 -- debug message
928 jty_log(FND_LOG.LEVEL_STATEMENT,
929 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.param_values',
930 'Source : ' || p_source_id || ' Mode : ' || p_mode || ' Start Date : ' || p_start_date ||
931 ' End Date : ' || p_end_date);
932
933 /* if mode = incremental , get all the territories that need to be processed from incr_gtp */
934 /* if mode = total , get all the active territories , as of sysdate, from jtf_terr_all */
935 /* if mode = date effective , get all the active territories , between p_start_date and p_end_date, from jtf_terr_all */
936 IF (p_mode = 'INCREMENTAL') THEN
937 BEGIN
938 SELECT count(*)
939 INTO l_count
940 FROM jty_conc_req_summ a
941 WHERE a.program_name = 'JTY_STAR'
942 AND a.param1 = p_source_id
943 AND a.param2 = 'TOTAL'
944 AND a.retcode = 0;
945
946 IF (l_count = 0) THEN
947 -- debug message
948 retcode := 2;
949 errbuf := 'STAR should be run at least once in TOTAL mode before INCREMENTAL mode';
950 jty_log(FND_LOG.LEVEL_EXCEPTION,
951 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.check_total_mode',
952 errbuf);
953
954 RAISE FND_API.G_EXC_ERROR;
955 END IF;
956 EXCEPTION
957 WHEN OTHERS THEN
958 RAISE;
959 END;
960
961 get_terr_for_incr_star (
962 p_source_id => p_source_id,
963 p_request_id => g_request_id,
964 p_terr_change_tab => l_terr_change_tab,
965 retcode => retcode,
966 errbuf => errbuf);
967
968 IF (retcode <> 0) THEN
969 -- debug message
970 jty_log(FND_LOG.LEVEL_EXCEPTION,
971 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.get_terr_for_incr_star',
972 'get_terr_for_incr_star API has failed');
973
974 RAISE FND_API.G_EXC_ERROR;
975 END IF;
976 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
977 BEGIN
978 SELECT count(*)
979 INTO l_count
980 FROM jty_conc_req_summ a
981 WHERE a.program_name = 'JTY_STAR'
982 AND a.param1 = p_source_id
983 AND a.param2 = 'DATE EFFECTIVE'
984 AND a.retcode = 0;
985
986 IF (l_count = 0) THEN
987 -- debug message
988 retcode := 2;
989 errbuf := 'STAR should be run at least once in DATE EFFECTIVE mode before DEA INCREMENTAL mode';
990 jty_log(FND_LOG.LEVEL_EXCEPTION,
991 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.check_dea_incremental_mode',
992 errbuf);
993
994 RAISE FND_API.G_EXC_ERROR;
995 END IF;
996 EXCEPTION
997 WHEN OTHERS THEN
998 RAISE;
999 END;
1000
1001 get_terr_for_dea_incr_star (
1002 p_source_id => p_source_id,
1003 p_request_id => g_request_id,
1004 p_terr_change_tab => l_terr_change_tab,
1005 retcode => retcode,
1006 errbuf => errbuf);
1007
1008 IF (retcode <> 0) THEN
1009 -- debug message
1010 jty_log(FND_LOG.LEVEL_EXCEPTION,
1011 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.get_terr_for_dea_incr_star',
1012 'get_terr_for_incr_star API has failed');
1013
1014 RAISE FND_API.G_EXC_ERROR;
1015 END IF;
1016 ELSE
1017 IF (p_mode = 'TOTAL') THEN
1018 OPEN csr_get_terr(p_source_id, g_sysdate, g_sysdate);
1019 ELSIF (p_mode = 'DATE EFFECTIVE') THEN
1020 OPEN csr_get_terr(p_source_id, l_param_start_date, l_param_end_date);
1021 END IF;
1022
1023 FETCH csr_get_terr BULK COLLECT INTO
1024 l_terr_change_tab.terr_id
1025 ,l_terr_change_tab.terr_rank
1026 ,l_terr_change_tab.num_winners
1027 ,l_terr_change_tab.org_id
1028 ,l_terr_change_tab.parent_terr_id
1029 ,l_terr_change_tab.level_from_root
1030 ,l_terr_change_tab.parent_num_winners
1031 ,l_terr_change_tab.rank_calc_flag
1032 ,l_terr_change_tab.attr_processing_flag
1033 ,l_terr_change_tab.hier_processing_flag
1034 ,l_terr_change_tab.matching_sql_flag
1035 ,l_terr_change_tab.start_date
1036 ,l_terr_change_tab.end_date;
1037
1038 CLOSE csr_get_terr;
1039 END IF; /* end IF (p_mode = 'INCREMENTAL') */
1040
1041 l_no_of_records := l_terr_change_tab.terr_id.COUNT;
1042
1043 -- debug message
1044 jty_log(FND_LOG.LEVEL_STATEMENT,
1045 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.no_of_terr',
1046 'Number of territories to be processed : ' || l_no_of_records);
1047
1048 IF (l_no_of_records > 0) THEN
1049 /* Calculate rank, denormalize hierarchy and qualifier values */
1050 JTY_TERR_DENORM_RULES_PVT.process_attr_and_rank (
1051 p_source_id => p_source_id,
1052 p_mode => p_mode,
1053 p_terr_change_tab => l_terr_change_tab,
1054 errbuf => errbuf,
1055 retcode => retcode);
1056
1057 IF (retcode <> 0) THEN
1058 -- debug message
1059 jty_log(FND_LOG.LEVEL_EXCEPTION,
1060 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.process_attr_and_rank',
1061 'process_attr_and_rank API has failed');
1062
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065
1066 -- debug message
1067 jty_log(FND_LOG.LEVEL_EVENT,
1068 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.process_attr_and_rank',
1069 'process_attr_and_rank API has completed with success');
1070
1071 /* Generate real time and batch matching SQLs */
1072 gen_matching_sql (
1073 p_source_id => p_source_id,
1074 p_mode => p_mode,
1075 p_terr_change_tab => l_terr_change_tab,
1076 p_start_date => l_param_start_date,
1077 p_end_date => l_param_end_date,
1078 x_Return_Status => l_Return_Status,
1079 x_Msg_Count => l_Msg_Count,
1080 x_Msg_Data => l_Msg_Data,
1081 errbuf => errbuf,
1082 retcode => retcode);
1083
1084 IF (retcode <> 0) THEN
1085 -- debug message
1086 jty_log(FND_LOG.LEVEL_EXCEPTION,
1087 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.gen_matching_sql',
1088 'gen_matching_sql API has failed');
1089
1090 RAISE FND_API.G_EXC_ERROR;
1091 END IF;
1092
1093 -- debug message
1094 jty_log(FND_LOG.LEVEL_EVENT,
1095 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.gen_matching_sql',
1096 'gen_matching_sql API has completed with success');
1097
1098 /* PERSON_ID required for OSO TAP */
1099 /* IF (p_source_id = -1001) THEN
1100 BEGIN
1101 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD DISABLE';
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 NULL;
1105 END;
1106
1107 FORALL i IN l_terr_change_tab.terr_id.FIRST .. l_terr_change_tab.terr_id.LAST
1108 UPDATE jtf_terr_rsc_all jtr
1109 SET jtr.person_id =
1110 ( SELECT jrrev.source_id
1111 FROM jtf_rs_resource_extns_vl jrrev
1112 WHERE jrrev.category = 'EMPLOYEE'
1113 AND jrrev.resource_id = jtr.resource_id )
1114 WHERE jtr.resource_type= 'RS_EMPLOYEE'
1115 AND jtr.terr_id = l_terr_change_tab.terr_id(i);
1116
1117 BEGIN
1118 EXECUTE IMMEDIATE 'ALTER TRIGGER JTF_TERRITORY_RSC_BIUD ENABLE';
1119 EXCEPTION
1120 WHEN OTHERS THEN
1121 NULL;
1122 END;
1123
1124 END IF;*/ -- COmmented for bug 8295746
1125
1126 IF ((p_mode = 'DATE EFFECTIVE') or (p_mode = 'DEA INCREMENTAL')) THEN
1127 SELECT denorm_dea_value_table_name
1128 INTO l_table_name
1129 FROM jtf_sources_all
1130 WHERE source_id = p_source_id;
1131 ELSE
1132 SELECT denorm_value_table_name
1133 INTO l_table_name
1134 FROM jtf_sources_all
1135 WHERE source_id = p_source_id;
1136 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
1137
1138 -- Update theabsolute rank in jtf_terr_denorm_rules_all for all service territories when the STAR is run for Service.
1139 IF (p_source_id = -1002) THEN
1140 UPDATE jtf_terr_denorm_rules_all jtda
1141 SET absolute_rank = ( select absolute_rank from jtf_terr_all jta where jta.terr_id = jtda.terr_id)
1142 where jtda.source_id = -1002;
1143 END IF;
1144
1145 IF ( p_source_id = -1002 ) THEN
1146 l_denorm_count := 0 ;
1147 EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || l_table_name || ' where ROWNUM = 1' INTO l_denorm_count;
1148 /* Create index on the denorm value table */
1149 /* Dont create the index on the denorm table if the there are no rows in the denorm table*/
1150 IF ( l_denorm_count = 1 ) THEN
1151 JTY_TERR_DENORM_RULES_PVT.CREATE_DNMVAL_INDEX (
1152 p_table_name => l_table_name,
1153 p_source_id => p_source_id,
1154 p_mode => p_mode,
1155 x_Return_Status => l_Return_Status);
1156 END IF;
1157 ELSE
1158 /* Create index on the denorm value table */
1159 JTY_TERR_DENORM_RULES_PVT.CREATE_DNMVAL_INDEX (
1160 p_table_name => l_table_name,
1161 p_source_id => p_source_id,
1162 p_mode => p_mode,
1163 x_Return_Status => l_Return_Status);
1164 END IF;
1165
1166 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1167 -- debug message
1168 jty_log(FND_LOG.LEVEL_EXCEPTION,
1169 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.CREATE_DNMVAL_INDEX',
1170 'JTY_TERR_DENORM_RULES_PVT.CREATE_DNMVAL_INDEX API has failed');
1171
1172 RAISE FND_API.G_EXC_ERROR;
1173 END IF;
1174
1175 ELSE
1176 -- debug message
1177 jty_log(FND_LOG.LEVEL_EXCEPTION,
1178 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.no_of_terr',
1179 'No territories processed');
1180
1181 END IF; /* IF (l_no_of_records > 0) */
1182
1183 /* Following procedure call has been added to set the geo_flag value in jtf_terr_all table
1184 based on wether a territory has geographical qualifiers or not */
1185 /*
1186 JTY_TERR_MAP_PVT.set_terr_geo_flag ( p_source_id => p_source_id,
1187 p_mode => p_mode,
1188 p_terr_change_tab => l_terr_change_tab,
1189 errbuf => errbuf, retcode => retcode );
1190 IF (retcode <> 0) THEN
1191 -- debug message
1192 jty_log(FND_LOG.LEVEL_EXCEPTION, 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine', 'JTY_TERR_MAP_PVT.set_terr_geo_flag API has failed');
1193 RAISE FND_API.G_EXC_ERROR;
1194 END IF;
1195 */
1196 /* if batch mode is not enabled for the usage, delete all the entries processed from the changed table */
1197 IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
1198 SELECT count(*)
1199 INTO l_batch_enabled
1200 FROM jty_trans_usg_pgm_details a
1201 WHERE a.source_id = p_source_id
1202 AND a.batch_enable_flag = 'Y';
1203
1204 IF (l_batch_enabled = 0) THEN
1205 DELETE jty_changed_terrs
1206 WHERE star_request_id = g_request_id;
1207 END IF;
1208 ELSIF (p_mode = 'DEA INCREMENTAL') THEN
1209 SELECT count(*)
1210 INTO l_batch_enabled
1211 FROM jty_trans_usg_pgm_details a
1212 WHERE a.source_id = p_source_id
1213 AND a.batch_enable_flag = 'Y';
1214
1215 IF (l_batch_enabled = 0) THEN
1216 DELETE jty_changed_dea_terrs
1217 WHERE star_request_id = g_request_id;
1218 END IF;
1219 END IF;
1220
1221 retcode := 0;
1222 errbuf := null;
1223 l_end_date := SYSDATE;
1224
1225 UPDATE JTY_CONC_REQ_SUMM
1226 SET requested_by = l_user_id
1227 ,request_date = l_start_date
1228 ,responsibility_application_id = l_resp_appl_id
1229 ,responsibility_id = l_resp_id
1230 ,last_updated_by = l_user_id
1231 ,last_update_date = l_start_date
1232 ,last_update_login = l_login_id
1233 ,start_date = l_start_date
1234 ,end_date = l_end_date
1235 ,param2 = p_mode
1236 ,param3 = TO_CHAR(l_param_start_date, 'DD/MM/YYYY HH24:MI:SS')
1237 ,param4 = TO_CHAR(l_param_end_date, 'DD/MM/YYYY HH24:MI:SS')
1238 ,param5 = null
1239 ,program_application_id = l_pgm_appl_id
1240 ,errbuf = errbuf
1241 ,request_id = g_request_id
1242 ,conc_program_id = l_conc_pgm_id
1243 WHERE program_name = 'JTY_STAR'
1244 AND param1 = to_char(p_source_id)
1245 AND retcode = retcode
1246 AND param2 = p_mode;
1247
1248 IF (SQL%ROWCOUNT = 0) THEN
1249 INSERT INTO JTY_CONC_REQ_SUMM (
1250 conc_req_id
1251 ,requested_by
1252 ,request_date
1253 ,responsibility_application_id
1254 ,responsibility_id
1255 ,last_updated_by
1256 ,last_update_date
1257 ,last_update_login
1258 ,start_date
1259 ,end_date
1260 ,param1
1261 ,param2
1262 ,param3
1263 ,param4
1264 ,param5
1265 ,program_application_id
1266 ,program_name
1267 ,retcode
1268 ,errbuf
1269 ,request_id
1270 ,conc_program_id
1271 ,object_version_number)
1272 VALUES (
1273 jty_conc_req_summ_s.nextval
1274 ,l_user_id
1275 ,l_start_date
1276 ,l_resp_appl_id
1277 ,l_resp_id
1278 ,l_user_id
1279 ,l_start_date
1280 ,l_login_id
1281 ,l_start_date
1282 ,l_end_date
1283 ,TO_CHAR(p_source_id)
1284 ,p_mode
1285 ,TO_CHAR(l_param_start_date, 'DD/MM/YYYY HH24:MI:SS')
1286 ,TO_CHAR(l_param_end_date, 'DD/MM/YYYY HH24:MI:SS')
1287 ,null
1288 ,l_pgm_appl_id
1289 ,l_pgm_name
1290 ,retcode
1291 ,errbuf
1292 ,g_request_id
1293 ,l_conc_pgm_id
1294 ,0);
1295 END IF;
1296
1297 -- debug message
1298 jty_log(FND_LOG.LEVEL_PROCEDURE,
1299 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.end',
1300 'End of the procedure JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
1301
1302 EXCEPTION
1303 WHEN FND_API.G_EXC_ERROR THEN
1304 RETCODE := 2;
1305 jty_log(FND_LOG.LEVEL_EXCEPTION,
1306 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.g_exc_error',
1307 'API JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine has failed with FND_API.G_EXC_ERROR exception');
1308
1309 WHEN OTHERS THEN
1310 RETCODE := 2;
1311 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1312 jty_log(FND_LOG.LEVEL_EXCEPTION,
1313 'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_rule_engine.others',
1314 substr(errbuf, 1, 4000));
1315
1316 END gen_rule_engine;
1317
1318 PROCEDURE update_resource_person_id(p_terr_id IN NUMBER)
1319 IS
1320
1321 BEGIN
1322
1323 UPDATE jtf_terr_rsc_all jtr
1324 SET jtr.person_id =
1325 ( SELECT jrrev.source_id
1326 FROM jtf_rs_resource_extns_vl jrrev
1327 WHERE jrrev.category = 'EMPLOYEE'
1328 AND jrrev.resource_id = jtr.resource_id )
1329 WHERE jtr.resource_type= 'RS_EMPLOYEE'
1330 AND jtr.terr_id = p_terr_id;
1331
1332 END update_resource_person_id;
1333
1334
1335 END JTY_TERR_ENGINE_GEN_PVT;