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