[Home] [Help]
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,
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')
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:
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;
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:
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;
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;
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;
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
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
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'
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
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
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'
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
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:
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
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',
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
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(+)
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
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
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',
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
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
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(+)
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;
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(*)