[Home] [Help]
PACKAGE BODY: APPS.JTY_TAE_GEN_PVT
Source
1 PACKAGE BODY JTY_TAE_GEN_PVT AS
2 /* $Header: jtfytaeb.pls 120.8 2008/05/12 13:20:15 rajukum ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTY_TAE_GEN_PVT
6 -- ---------------------------------------------------
7 -- PURPOSE
8 -- This package is used to generate the batch matching SQLs for all qualifier combinations.
9 --
10 -- Procedures:
11 -- (see below for specification)
12 --
13 -- NOTES
14 -- This package is available for private use only
15 --
16 -- HISTORY
17 -- 07/25/05 ACHANDA Created
18 --
19 -- End of Comments
20 --
21
22 --------------------------------------------------
23 --- GLOBAL Declarations Starts here -----
24 --------------------------------------------------
25
26 G_INDENT VARCHAR2(30) := ' ';
27 G_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
28 G_PROGRAM_APPL_ID NUMBER := FND_GLOBAL.PROG_APPL_ID();
29 G_PROGRAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
30 G_USER_ID NUMBER := FND_GLOBAL.USER_ID();
31 G_SYSDATE DATE := SYSDATE;
32 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
33 G_NEW_LINE VARCHAR2(02) := fnd_global.local_chr(10);
34
35 /* this procedure builds the where clause of a qualifier depending on its rule */
36 FUNCTION build_predicate_for_operator(
37 p_op_common_where VARCHAR2
38 ,p_op_eql VARCHAR2
39 ,p_op_like VARCHAR2
40 ,p_op_between VARCHAR2
41 ,p_newline VARCHAR2)
42 RETURN VARCHAR2 AS
43
44 l_result CLOB;
45 l_counter NUMBER;
46
47 BEGIN
48 l_counter := 1;
49
50 IF (p_op_eql IS NOT NULL) THEN
51 l_result := p_newline || 'AND ( ' || p_op_eql;
52 l_counter := l_counter + 1;
53 END IF;
54
55 IF p_op_like IS NOT NULL THEN
56 IF (l_counter = 1) THEN
57 l_result := p_newline || 'AND ( ' || p_op_like;
58 ELSE
59 l_result := l_result || p_newline || ' OR ' || p_newline || p_op_like;
60 END IF;
61 l_counter := l_counter + 1;
62 END IF;
63
64 IF p_op_between IS NOT NULL THEN
65 IF (l_counter = 1) THEN
66 l_result := p_newline || 'AND ( ' || p_op_between;
67 ELSE
68 l_result := l_result || p_newline || ' OR ' || p_newline || p_op_between;
69 END IF;
70 l_counter := l_counter + 1;
71 END IF;
72
73 l_result := l_result || p_newline || ' )';
74
75 IF (p_op_common_where IS NOT NULL) THEN
76 l_result := l_result || p_newline || ' AND ' || p_op_common_where || p_newline;
77 END IF;
78
79 RETURN l_result;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
84 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
85 'jtf.plsql.JTY_TAE_GEN_PVT.build_predicate_for_operator.others',
86 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
87 END IF;
88 RAISE;
89 END build_predicate_for_operator;
90
91
92 /* this function retuens the inline view of the matching SQL that returns */
93 /* all the valid territories having a particular qualifier combination */
94 FUNCTION append_inlineview(
95 p_source_id IN NUMBER,
96 p_trans_id IN NUMBER,
97 p_mode IN VARCHAR2,
98 p_qual_relation_prd IN NUMBER,
99 p_from_str IN VARCHAR2,
100 p_new_mode_fetch IN VARCHAR2)
101 RETURN VARCHAR2 AS
102
103 BEGIN
104 IF (p_mode = 'DATE EFFECTIVE') THEN
105
106 RETURN p_from_str || g_new_line || g_new_line ||
107 G_INDENT || ' , /* INLINE VIEW */' || g_new_line ||
108 G_INDENT || ' ( SELECT /*+ NO_MERGE */ ' || g_new_line ||
109 G_INDENT || ' jtdr.terr_id ' || g_new_line ||
110 G_INDENT || ' , jtdr.source_id ' || g_new_line ||
111 G_INDENT || ' , jqtu.qual_type_id ' || g_new_line ||
112 G_INDENT || ' , jtdr.top_level_terr_id ' || g_new_line ||
113 G_INDENT || ' , jta.absolute_rank ' || g_new_line ||
114 G_INDENT || ' , jta.num_winners ' || g_new_line ||
115 G_INDENT || ' , jta.org_id ' || g_new_line ||
116 G_INDENT || ' FROM jty_denorm_dea_rules_all jtdr ' || g_new_line ||
117 G_INDENT || ' ,jtf_terr_qtype_usgs_all jtqu ' || g_new_line ||
118 G_INDENT || ' ,jtf_qual_type_usgs_all jqtu ' || g_new_line ||
119 G_INDENT || ' ,jtf_terr_all jta ' || g_new_line ||
120 G_INDENT || ' WHERE jtdr.source_id = ' || p_source_id || g_new_line ||
121 G_INDENT || ' AND jtdr.terr_id= jtdr.related_terr_id' || g_new_line ||
122 G_INDENT || ' AND jtdr.terr_id= jta.terr_id' || g_new_line ||
123 G_INDENT || ' AND jqtu.source_id = jtdr.source_id ' || g_new_line ||
124 G_INDENT || ' AND jqtu.qual_type_id = ' || p_trans_id || g_new_line ||
125 G_INDENT || ' AND jtdr.terr_id = jtqu.terr_id ' || g_new_line ||
126 G_INDENT || ' AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_new_line ||
127 G_INDENT || ' --AND jtdr.resource_exists_flag = ''Y'' '|| g_new_line ||
128 G_INDENT || ' AND jtqu.qual_relation_product = ' || p_qual_relation_prd || g_new_line ||
129 G_INDENT || ' ) ILV' || g_new_line;
130
131 ELSE
132 IF (p_new_mode_fetch <> 'Y') THEN
133
134 RETURN p_from_str || g_new_line || g_new_line ||
135 G_INDENT || ' , /* INLINE VIEW */' || g_new_line ||
136 G_INDENT || ' ( SELECT /*+ NO_MERGE */ ' || g_new_line ||
137 G_INDENT || ' jtdr.terr_id ' || g_new_line ||
138 G_INDENT || ' , jtdr.source_id ' || g_new_line ||
139 G_INDENT || ' , jqtu.qual_type_id ' || g_new_line ||
140 G_INDENT || ' , jtdr.top_level_terr_id ' || g_new_line ||
141 G_INDENT || ' , jta.absolute_rank ' || g_new_line ||
142 G_INDENT || ' , jta.num_winners ' || g_new_line ||
143 G_INDENT || ' , jta.org_id ' || g_new_line ||
144 G_INDENT || ' FROM jtf_terr_denorm_rules_all jtdr ' || g_new_line ||
145 G_INDENT || ' ,jtf_terr_qtype_usgs_all jtqu ' || g_new_line ||
146 G_INDENT || ' ,jtf_qual_type_usgs_all jqtu ' || g_new_line ||
147 G_INDENT || ' ,jtf_terr_all jta ' || g_new_line ||
148 G_INDENT || ' WHERE jtdr.source_id = ' || p_source_id || g_new_line ||
149 G_INDENT || ' AND jtdr.terr_id= jtdr.related_terr_id' || g_new_line ||
150 G_INDENT || ' AND jtdr.terr_id= jta.terr_id' || g_new_line ||
151 G_INDENT || ' AND jqtu.source_id = jtdr.source_id ' || g_new_line ||
152 G_INDENT || ' AND jqtu.qual_type_id = ' || p_trans_id || g_new_line ||
153 G_INDENT || ' AND jtdr.terr_id = jtqu.terr_id ' || g_new_line ||
154 G_INDENT || ' AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_new_line ||
155 G_INDENT || ' --AND jtdr.resource_exists_flag = ''Y'' '|| g_new_line ||
156 G_INDENT || ' AND jtqu.qual_relation_product = ' || p_qual_relation_prd || g_new_line ||
157 G_INDENT || ' ) ILV' || g_new_line;
158
159 ELSE
160
161 RETURN p_from_str || g_new_line || g_new_line ||
162 G_INDENT || ' , /* INLINE VIEW */' || g_new_line ||
163 G_INDENT || ' ( SELECT /*+ NO_MERGE */ DISTINCT ' || g_new_line ||
164 G_INDENT || ' jtdr.terr_id ' || g_new_line ||
165 G_INDENT || ' , jtdr.source_id ' || g_new_line ||
166 G_INDENT || ' , jqtu.qual_type_id ' || g_new_line ||
167 G_INDENT || ' , jtdr.top_level_terr_id ' || g_new_line ||
168 G_INDENT || ' , jta.absolute_rank ' || g_new_line ||
169 G_INDENT || ' , jta.num_winners ' || g_new_line ||
170 G_INDENT || ' , jta.org_id ' || g_new_line ||
171 G_INDENT || ' FROM jtf_terr_denorm_rules_all jtdr ' || g_new_line ||
172 G_INDENT || ' ,jty_changed_terrs jct ' || g_new_line ||
173 G_INDENT || ' ,jtf_terr_qtype_usgs_all jtqu ' || g_new_line ||
174 G_INDENT || ' ,jtf_qual_type_usgs_all jqtu ' || g_new_line ||
175 G_INDENT || ' ,jtf_terr_all jta ' || g_new_line ||
176 G_INDENT || ' WHERE jqtu.source_id = ' || p_source_id || g_new_line ||
177 G_INDENT || ' AND jtdr.terr_id= jct.terr_id ' || g_new_line ||
178 G_INDENT || ' AND jtdr.terr_id= jtdr.related_terr_id' || g_new_line ||
179 G_INDENT || ' AND jtdr.terr_id= jta.terr_id' || g_new_line ||
180 G_INDENT || ' AND jqtu.source_id = jtdr.source_id ' || g_new_line ||
181 G_INDENT || ' AND jqtu.qual_type_id = ' || p_trans_id || g_new_line ||
182 G_INDENT || ' AND jct.terr_id = jtqu.terr_id ' || g_new_line ||
183 G_INDENT || ' AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_new_line ||
184 -- G_INDENT || ' AND jtdr.resource_exists_flag = ''Y'' '|| g_new_line ||
185 G_INDENT || ' AND jct.tap_request_id = :REQUEST_ID ' || g_new_line ||
186 G_INDENT || ' AND :CURR_DATE BETWEEN jtdr.start_date and jtdr.end_date ' || g_new_line ||
187 G_INDENT || ' AND jtqu.qual_relation_product = ' || p_qual_relation_prd || g_new_line ||
188 G_INDENT || ' ) ILV'||g_new_line;
189
190 END IF;
191 END IF;
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
196 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
197 'jtf.plsql.JTY_TAE_GEN_PVT.append_inlineview.others',
198 substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));
199 END IF;
200 RAISE;
201
202 END append_inlineview;
203
204 /* this procedure returns the insert and select clause of the SQL */
205 /* statement that inserts objects into NM_TRANS from TRANS table */
206 PROCEDURE get_insert_select_nmtrans(
207 p_match_table_name IN VARCHAR2,
208 p_insert_stmt OUT NOCOPY VARCHAR2,
209 p_select_stmt OUT NOCOPY VARCHAR2,
210 errbuf OUT NOCOPY VARCHAR2,
211 retcode OUT NOCOPY VARCHAR2)
212 AS
213 l_status VARCHAR2(30);
214 l_industry VARCHAR2(30);
215 l_owner VARCHAR2(30);
216 first_time BOOLEAN;
217 l_indent VARCHAR2(30);
218
219 CURSOR c_column_names(cl_table_name IN VARCHAR2, cl_owner IN VARCHAR2) is
220 SELECT column_name
221 FROM all_tab_columns
222 WHERE table_name = cl_table_name
223 AND owner = cl_owner
224 AND column_name not in ('LAST_UPDATE_DATE', 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN',
225 'REQUEST_ID', 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE');
226
227 L_SCHEMA_NOTFOUND EXCEPTION;
228 BEGIN
229
230 -- debug message
231 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
232 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
233 'jtf.plsql.JTY_TAE_GEN_PVT.get_insert_select_nmtrans.start',
234 'Start of the procedure JTY_TAE_GEN_PVT.get_insert_select_nmtrans');
235 END IF;
236
237 /* Get the schema name corresponding to JTF application */
238 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
239 NULL;
240 END IF;
241
242 IF (l_owner IS NULL) THEN
243 RAISE L_SCHEMA_NOTFOUND;
244 END IF;
245
246 /* Initialize local variables */
247 first_time := TRUE;
248 l_indent := ' ';
249
250 /* Form the insert statement to insert transaction objects into TRANS table */
251 p_insert_stmt := 'INSERT INTO ' || p_match_table_name || '(';
252 p_select_stmt := 'SELECT DISTINCT ';
253
254 FOR column_names in c_column_names(p_match_table_name, l_owner) LOOP
255 IF (first_time) THEN
256 p_insert_stmt := p_insert_stmt || g_new_line || l_indent || column_names.column_name;
257 p_select_stmt := p_select_stmt || g_new_line || l_indent || 'A.' || column_names.column_name;
258 first_time := FALSE;
259 ELSE
260 p_insert_stmt := p_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
261 p_select_stmt := p_select_stmt || g_new_line || l_indent || ',' || 'A.' || column_names.column_name;
262 END IF;
263 END LOOP;
264
265 /* Standard WHO columns */
266 p_insert_stmt := p_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
267 g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
268 g_new_line || l_indent || ',CREATION_DATE ' ||
269 g_new_line || l_indent || ',CREATED_BY ' ||
270 g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
271 g_new_line || l_indent || ',REQUEST_ID ' ||
272 g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
273 g_new_line || l_indent || ',PROGRAM_ID ' ||
274 g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
275 g_new_line || ')';
276
277 p_select_stmt := p_select_stmt || g_new_line || l_indent || ',:LAST_UPDATE_DATE ' ||
278 g_new_line || l_indent || ',:LAST_UPDATED_BY ' ||
279 g_new_line || l_indent || ',:CREATION_DATE ' ||
280 g_new_line || l_indent || ',:CREATED_BY ' ||
281 g_new_line || l_indent || ',:LAST_UPDATE_LOGIN ' ||
282 g_new_line || l_indent || ',:REQUEST_ID ' ||
283 g_new_line || l_indent || ',:PROGRAM_APPLICATION_ID ' ||
284 g_new_line || l_indent || ',:PROGRAM_ID ' ||
285 g_new_line || l_indent || ',:PROGRAM_UPDATE_DATE ' ||
286 ' FROM ';
287
288 -- debug message
289 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
290 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
291 'jtf.plsql.JTY_TAE_GEN_PVT.get_insert_select_nmtrans.end',
292 'End of the procedure JTY_TAE_GEN_PVT.get_insert_select_nmtrans');
293 END IF;
294
295 retcode := 0;
296 errbuf := null;
297
298 EXCEPTION
299 WHEN L_SCHEMA_NOTFOUND THEN
300 errbuf := 'Schema name corresponding to JTF application not found';
301 retcode := 2;
302 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
304 'jtf.plsql.JTY_TAE_GEN_PVT.get_insert_select_nmtrans.l_schema_notfound',
305 errbuf);
306 END IF;
307
308 WHEN OTHERS THEN
309 RETCODE := 2;
310 ERRBUF := SQLCODE || ' : ' || SQLERRM;
311 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
312 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
313 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.others',
314 substr(errbuf, 1, 4000));
315 END IF;
316 END get_insert_select_nmtrans;
317
318
319 /* this procedure builds inline view for qualifiers */
320 PROCEDURE build_ilv1(
321 p_source_id IN NUMBER,
322 p_trans_id IN NUMBER,
323 p_qual_relation_prd IN NUMBER,
324 p_relation_factor IN NUMBER,
325 p_mode IN VARCHAR2,
326 p_denorm_table_name IN VARCHAR2,
327 p_new_mode_fetch IN VARCHAR2,
328 p_sql OUT NOCOPY VARCHAR2,
329 retcode OUT NOCOPY VARCHAR2,
330 errbuf OUT NOCOPY VARCHAR2)
331 AS
332 l_from_str CLOB;
333 l_where_str CLOB;
334 l_predicate CLOB;
335 l_select CLOB;
336
337 CURSOR c_rel_prod_detail(cl_source_id number, cl_trans_id number, cl_relation_product number, cl_relation_factor number) IS
338 SELECT distinct
339 jtqf.qual_usg_id
340 ,jqu.alias_rule1
341 ,jqu.op_eql
342 ,jqu.op_like
343 ,jqu.op_between
344 ,jqu.op_common_where
345 FROM jtf_qual_usgs_all jqu,
346 jtf_tae_qual_factors jtqf,
347 jtf_tae_qual_products jtqp,
348 jtf_tae_qual_prod_factors jtpf
349 WHERE jqu.org_id = -3113
350 AND jqu.qual_usg_id = jtqf.qual_usg_id
351 AND jtpf.qual_factor_id = jtqf.qual_factor_id
352 AND jtqf.relation_factor = cl_relation_factor
353 AND jtqp.qual_product_id = jtpf.qual_product_id
354 AND jtqp.relation_product = cl_relation_product
355 AND jtqp.source_id = cl_source_id
356 AND jtqp.trans_object_type_id = cl_trans_id
357 AND jqu.op_not_eql IS NULL
358 AND jqu.op_not_like IS NULL
359 AND jqu.op_not_between IS NULL
360 ORDER BY jtqf.qual_usg_id;
361
362 CURSOR c_dea_rel_prod_detail(cl_source_id number, cl_trans_id number, cl_relation_product number, cl_relation_factor number) IS
363 SELECT distinct
364 jtqf.qual_usg_id
365 ,jqu.alias_rule1
366 ,jqu.op_eql
367 ,jqu.op_like
368 ,jqu.op_between
369 ,jqu.op_common_where
370 FROM jtf_qual_usgs_all jqu,
371 jty_dea_attr_factors jtqf,
372 jty_dea_attr_products jtqp,
373 jty_dea_attr_prod_factors jtpf
374 WHERE jqu.org_id = -3113
375 AND jqu.qual_usg_id = jtqf.qual_usg_id
376 AND jtpf.dea_attr_factors_id = jtqf.dea_attr_factors_id
377 AND jtqf.relation_factor = cl_relation_factor
378 AND jtqp.dea_attr_products_id = jtpf.dea_attr_products_id
379 AND jtqp.attr_relation_product = cl_relation_product
380 AND jtqp.source_id = cl_source_id
381 AND jtqp.trans_type_id = cl_trans_id
382 AND jqu.op_not_eql IS NULL
383 AND jqu.op_not_like IS NULL
384 AND jqu.op_not_between IS NULL
385 ORDER BY jtqf.qual_usg_id;
386
387 TYPE l_qual_usg_id_tbl_type IS TABLE OF jtf_qual_usgs_all.qual_usg_id%TYPE;
388 TYPE l_alias_rule1_tbl_type IS TABLE OF jtf_qual_usgs_all.alias_rule1%TYPE;
389 TYPE l_op_eql_tbl_type IS TABLE OF jtf_qual_usgs_all.op_eql%TYPE;
390 TYPE l_op_like_tbl_type IS TABLE OF jtf_qual_usgs_all.op_like%TYPE;
391 TYPE l_op_between_tbl_type IS TABLE OF jtf_qual_usgs_all.op_between%TYPE;
392 TYPE l_op_where_tbl_type IS TABLE OF jtf_qual_usgs_all.op_common_where%TYPE;
393
394 l_qual_usg_id_tbl l_qual_usg_id_tbl_type;
395 l_alias_rule1_tbl l_alias_rule1_tbl_type;
396 l_op_eql_tbl l_op_eql_tbl_type;
397 l_op_like_tbl l_op_like_tbl_type;
398 l_op_between_tbl l_op_between_tbl_type;
399 l_op_where_tbl l_op_where_tbl_type;
400 BEGIN
401
402 -- debug message
403 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
404 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
408
405 'jtf.plsql.JTY_TAE_GEN_PVT.build_ilv1.start',
406 'Start of the procedure JTY_TAE_GEN_PVT.build_ilv1');
407 END IF;
409 IF (p_mode = 'DATE EFFECTIVE') THEN
410 OPEN c_dea_rel_prod_detail(p_source_id, p_trans_id, p_qual_relation_prd, p_relation_factor);
411 FETCH c_dea_rel_prod_detail BULK COLLECT INTO
412 l_qual_usg_id_tbl
413 ,l_alias_rule1_tbl
414 ,l_op_eql_tbl
415 ,l_op_like_tbl
416 ,l_op_between_tbl
417 ,l_op_where_tbl;
418 CLOSE c_dea_rel_prod_detail;
419 ELSE
420 OPEN c_rel_prod_detail(p_source_id, p_trans_id, p_qual_relation_prd, p_relation_factor);
421 FETCH c_rel_prod_detail BULK COLLECT INTO
422 l_qual_usg_id_tbl
423 ,l_alias_rule1_tbl
424 ,l_op_eql_tbl
425 ,l_op_like_tbl
426 ,l_op_between_tbl
427 ,l_op_where_tbl;
428 CLOSE c_rel_prod_detail;
429 END IF;
430
431 FOR i IN l_qual_usg_id_tbl.FIRST .. l_qual_usg_id_tbl.LAST LOOP
432 IF mod(p_qual_relation_prd,79) = 0 THEN
433 l_select := G_INDENT || 'SELECT ' || g_new_line ||
434 G_INDENT || ' AI.customer_id' || g_new_line ||
435 G_INDENT || ' , AI.address_id' || g_new_line;
436 ELSIF mod(p_qual_relation_prd,137) = 0 THEN
437 l_select := G_INDENT || 'SELECT ' || g_new_line ||
438 G_INDENT || ' ASLLP.sales_lead_id' || g_new_line ||
439 G_INDENT || ' , ASLLP.sales_lead_line_id' || g_new_line;
440 ELSIF mod(p_qual_relation_prd,113) = 0 THEN
441 l_select := G_INDENT || 'SELECT ' || g_new_line ||
442 G_INDENT || ' ASLL.sales_lead_id' || g_new_line ||
443 G_INDENT || ' , ASLL.sales_lead_line_id' || g_new_line;
444 ELSIF mod(p_qual_relation_prd,131) = 0 THEN
445 l_select := G_INDENT || 'SELECT ' || g_new_line ||
446 G_INDENT || ' ASLLI.sales_lead_id' || g_new_line ||
447 G_INDENT || ' , ASLLI.sales_lead_line_id' || g_new_line;
448 ELSIF mod(p_qual_relation_prd,139) = 0 THEN
449 l_select := G_INDENT || 'SELECT ' || g_new_line ||
450 G_INDENT || ' ALLP.lead_id' || g_new_line ||
451 G_INDENT || ' , ALLP.lead_line_id' || g_new_line;
452 ELSIF mod(p_qual_relation_prd,163) = 0 THEN
453 l_select := G_INDENT || 'SELECT ' || g_new_line ||
454 G_INDENT || ' ALLI.lead_id' || g_new_line ||
455 G_INDENT || ' , ALLI.lead_line_id' || g_new_line;
456 ELSIF mod(p_qual_relation_prd,167) = 0 THEN
457 l_select := G_INDENT || 'SELECT ' || g_new_line ||
458 G_INDENT || ' OAI.lead_id' || g_new_line ;
459 END IF;
460
461 l_select := l_select ||
462 G_INDENT || ' , ILV.terr_id ' || g_new_line ||
463 G_INDENT || ' , ILV.top_level_terr_id ' || g_new_line ||
464 G_INDENT || ' , ILV.absolute_rank ' || g_new_line ||
465 G_INDENT || ' , ILV.num_winners ' || g_new_line ||
466 G_INDENT || ' , ILV.org_id ' || g_new_line;
467
468 l_from_str := G_INDENT || 'FROM ' || l_alias_rule1_tbl(i) || ', ' || p_denorm_table_name || ' B ';
469
470 l_from_str := append_inlineview(
471 p_source_id => p_source_id,
472 p_trans_id => p_trans_id,
473 p_mode => p_mode,
474 p_qual_relation_prd => p_qual_relation_prd,
475 p_from_str => l_from_str,
476 p_new_mode_fetch => p_new_mode_fetch);
477
478 l_where_str := g_new_line || G_INDENT || 'WHERE 1 = 1 ' ;
479
480 l_predicate := g_new_line ||
481 build_predicate_for_operator(
482 p_op_common_where => l_op_where_tbl(i),
483 p_op_eql => l_op_eql_tbl(i),
484 p_op_like => l_op_like_tbl(i),
485 p_op_between => l_op_between_tbl(i),
486 p_newline => g_new_line);
487
488 IF mod(p_qual_relation_prd,79) = 0 THEN
489 l_predicate := replace(l_predicate,'( A.SQUAL_NUM02 IS NULL AND AI.address_id IS NULL )','');
490 l_predicate := replace(l_predicate,'OR ( A.SQUAL_NUM02 = AI.address_id )' , '1=1');
491 l_predicate := replace(l_predicate,'A.SQUAL_NUM01 = AI.customer_id','1=1');
492 ELSIF mod(p_qual_relation_prd,137) = 0 THEN
493 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ASLLP.SALES_LEAD_ID','1=1');
494 ELSIF mod(p_qual_relation_prd,113) = 0 THEN
495 l_predicate := replace(l_predicate,'ASLL.SALES_LEAD_ID = A.TRANS_OBJECT_ID','1=1');
496 l_predicate := replace(l_predicate,'a.squal_curc03 = Q1022R1.currency_code','1=1');
497 ELSIF mod(p_qual_relation_prd,131) = 0 THEN
498 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ASLLI.SALES_LEAD_ID','1=1');
499 ELSIF mod(p_qual_relation_prd,139) = 0 THEN
500 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ALLP.LEAD_ID','1=1');
501 ELSIF mod(p_qual_relation_prd,163) = 0 THEN
502 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = ALLI.LEAD_ID','1=1');
503 ELSIF mod(p_qual_relation_prd,167) = 0 THEN
504 l_predicate := replace(l_predicate,'A.TRANS_OBJECT_ID = OAI.LEAD_ID','1=1');
505 END IF;
509
506
507 EXIT;
508 END LOOP;
510 p_sql := l_select || g_new_line ||
511 l_from_str || g_new_line ||
512 l_where_str || g_new_line ||
513 l_predicate;
514
515 -- debug message
516 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
517 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
518 'jtf.plsql.JTY_TAE_GEN_PVT.build_ilv1.end',
519 'End of the procedure JTY_TAE_GEN_PVT.build_ilv1');
520 END IF;
521
522 retcode := 0;
523 errbuf := null;
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 RETCODE := 2;
528 ERRBUF := SQLCODE || ' : ' || SQLERRM;
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_TAE_GEN_PVT.build_ilv1.others',
532 substr(errbuf, 1, 4000));
533 END IF;
534 END build_ilv1;
535
536 /* this procedure builds inline view for qualifiers */
537 PROCEDURE build_ilv2(
538 p_source_id IN NUMBER,
539 p_trans_id IN NUMBER,
540 p_program_name IN VARCHAR2,
541 p_mode IN VARCHAR2,
542 p_qual_relation_prd IN NUMBER,
543 p_relation_factor IN NUMBER,
544 p_trans_table_name IN VARCHAR2,
545 p_match_table_name IN VARCHAR2,
546 p_denorm_table_name IN VARCHAR2,
547 p_new_mode_fetch IN VARCHAR2,
548 p_sql OUT NOCOPY VARCHAR2,
549 retcode OUT NOCOPY VARCHAR2,
550 errbuf OUT NOCOPY VARCHAR2)
551 AS
552
553 CURSOR c_rel_prod_detail(cl_source_id number, cl_trans_id number, cl_relation_product number, cl_relation_factor number)
554 IS
555 SELECT DISTINCT
556 jqu.qual_usg_id
557 ,jqu.alias_rule1
558 ,jqu.op_eql
559 ,jqu.op_like
560 ,jqu.op_between
561 ,jqu.op_common_where
562 FROM
563 jtf_qual_usgs_all jqu
564 ,jtf_tae_qual_factors jtqf
565 ,jtf_tae_qual_products jtqp
566 ,jtf_tae_qual_prod_factors jtpf
567 WHERE jqu.org_id = -3113
568 AND jqu.qual_usg_id = jtqf.qual_usg_id
569 AND jtpf.qual_factor_id= jtqf.qual_factor_id
570 AND jtqf.relation_factor <> cl_relation_factor
571 AND jtqp.qual_product_id = jtpf.qual_product_id
572 AND jtqp.relation_product = cl_relation_product
573 AND jtqp.source_id = cl_source_id
574 AND jtqp.trans_object_type_id= cl_trans_id
575 ORDER BY jqu.qual_usg_id;
576
577 CURSOR c_dea_rel_prod_detail(cl_source_id number, cl_trans_id number, cl_relation_product number, cl_relation_factor number)
578 IS
579 SELECT DISTINCT
580 jqu.qual_usg_id
581 ,jqu.alias_rule1
582 ,jqu.op_eql
583 ,jqu.op_like
584 ,jqu.op_between
585 ,jqu.op_common_where
586 FROM
587 jtf_qual_usgs_all jqu
588 ,jty_dea_attr_factors jtqf
589 ,jty_dea_attr_products jtqp
590 ,jty_dea_attr_prod_factors jtpf
591 WHERE jqu.org_id = -3113
592 AND jqu.qual_usg_id = jtqf.qual_usg_id
593 AND jtpf.dea_attr_factors_id = jtqf.dea_attr_factors_id
594 AND jtqf.relation_factor <> cl_relation_factor
595 AND jtqp.dea_attr_products_id = jtpf.dea_attr_products_id
596 AND jtqp.attr_relation_product = cl_relation_product
597 AND jtqp.source_id = cl_source_id
598 AND jtqp.trans_type_id= cl_trans_id
599 ORDER BY jqu.qual_usg_id;
600
601 TYPE l_qual_usg_id_tbl_type IS TABLE OF jtf_qual_usgs_all.qual_usg_id%TYPE;
602 TYPE l_alias_rule1_tbl_type IS TABLE OF jtf_qual_usgs_all.alias_rule1%TYPE;
603 TYPE l_op_eql_tbl_type IS TABLE OF jtf_qual_usgs_all.op_eql%TYPE;
604 TYPE l_op_like_tbl_type IS TABLE OF jtf_qual_usgs_all.op_like%TYPE;
605 TYPE l_op_between_tbl_type IS TABLE OF jtf_qual_usgs_all.op_between%TYPE;
606 TYPE l_op_where_tbl_type IS TABLE OF jtf_qual_usgs_all.op_common_where%TYPE;
607
608 l_qual_usg_id_tbl l_qual_usg_id_tbl_type;
609 l_alias_rule1_tbl l_alias_rule1_tbl_type;
610 l_op_eql_tbl l_op_eql_tbl_type;
611 l_op_like_tbl l_op_like_tbl_type;
612 l_op_between_tbl l_op_between_tbl_type;
613 l_op_where_tbl l_op_where_tbl_type;
614
615 l_counter NUMBER;
616 l_relation_product NUMBER;
617
618 l_from_str CLOB;
619 l_where_str CLOB;
620 l_predicate CLOB;
621
622 BEGIN
623
624 -- debug message
625 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
626 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
627 'jtf.plsql.JTY_TAE_GEN_PVT.build_ilv2.start',
628 'Start of the procedure JTY_TAE_GEN_PVT.build_ilv2');
629 END IF;
630
631 l_relation_product := p_qual_relation_prd/p_relation_factor;
632 l_counter := 1;
633
634 IF (p_mode = 'DATE EFFECTIVE') THEN
635 OPEN c_dea_rel_prod_detail(p_source_id, p_trans_id, p_qual_relation_prd, p_relation_factor);
636 FETCH c_dea_rel_prod_detail BULK COLLECT INTO
637 l_qual_usg_id_tbl
638 ,l_alias_rule1_tbl
639 ,l_op_eql_tbl
640 ,l_op_like_tbl
641 ,l_op_between_tbl
642 ,l_op_where_tbl;
643 CLOSE c_dea_rel_prod_detail;
644 ELSE
648 ,l_alias_rule1_tbl
645 OPEN c_rel_prod_detail(p_source_id, p_trans_id, p_qual_relation_prd, p_relation_factor);
646 FETCH c_rel_prod_detail BULK COLLECT INTO
647 l_qual_usg_id_tbl
649 ,l_op_eql_tbl
650 ,l_op_like_tbl
651 ,l_op_between_tbl
652 ,l_op_where_tbl;
653 CLOSE c_rel_prod_detail;
654 END IF;
655
656 IF (l_qual_usg_id_tbl.COUNT > 0) THEN
657 FOR i IN l_qual_usg_id_tbl.FIRST .. l_qual_usg_id_tbl.LAST LOOP
658
659 IF (l_counter = 1) THEN
660 l_from_str := g_new_line || p_trans_table_name || ' A ' || g_new_line || ',' || p_denorm_table_name || ' B ';
661 IF (l_alias_rule1_tbl(i) IS NOT NULL) THEN
662 l_from_str := l_from_str || g_new_line || ',' || l_alias_rule1_tbl(i);
663 END IF;
664
665 l_where_str := g_new_line || 'WHERE 1 = 1';
666 IF (p_new_mode_fetch <> 'Y') THEN
667 l_where_str := l_where_str || g_new_line || 'AND a.worker_id = :p_worker_id ';
668 END IF;
669
670 l_predicate := g_new_line ||
671 build_predicate_for_operator(
672 l_op_where_tbl(i)
673 ,l_op_eql_tbl(i)
674 ,l_op_like_tbl(i)
675 ,l_op_between_tbl(i)
676 ,g_new_line);
677 ELSE
678 IF (l_alias_rule1_tbl(i) IS NOT NULL) THEN
679 l_from_str := l_from_str || g_new_line || ',' || l_alias_rule1_tbl(i);
680 END IF;
681
682 l_predicate := l_predicate || g_new_line ||
683 build_predicate_for_operator(
684 l_op_where_tbl(i)
685 ,l_op_eql_tbl(i)
686 ,l_op_like_tbl(i)
687 ,l_op_between_tbl(i)
688 ,g_new_line);
689 END IF; /* end IF (l_counter = 1) */
690
691 l_counter := l_counter + 1;
692 END LOOP; /* end loop FOR i IN l_qual_usg_id_tbl.FIRST .. l_qual_usg_id_tbl.LAST */
693 END IF; /* end IF (l_qual_usg_id_tbl.COUNT > 0) */
694
695 l_from_str :=
696 append_inlineview(
697 p_source_id => p_source_id,
698 p_trans_id => p_trans_id,
699 p_mode => p_mode,
700 p_qual_relation_prd => p_qual_relation_prd,
701 p_from_str => l_from_str,
702 p_new_mode_fetch => p_new_mode_fetch);
703
704 IF (p_new_mode_fetch = 'Y') THEN
705 p_sql := 'SELECT A.*,ILV.terr_id,ILV.absolute_rank,ILV.top_level_terr_id ,ILV.num_winners FROM ';
706 ELSE
707 IF (mod(p_qual_relation_prd, 79) = 0) THEN
708 p_sql := 'SELECT a.trans_object_id,a.trans_detail_object_id,a.worker_id,a.txn_date, ' ||
709 'ILV.terr_id,ILV.absolute_rank,ILV.top_level_terr_id ,ILV.num_winners,ILV.org_id, A.SQUAL_NUM01, A.SQUAL_NUM02 FROM ' ;
710 ELSE
711 p_sql := 'SELECT a.trans_object_id,a.trans_detail_object_id,a.worker_id,a.txn_date, ' ||
712 'ILV.terr_id,ILV.absolute_rank,ILV.top_level_terr_id ,ILV.num_winners,ILV.org_id FROM ' ;
713 END IF;
714
715 END IF;
716
717 p_sql := p_sql || l_from_str || g_new_line || l_where_str || g_new_line || l_predicate || g_new_line ||
718 'AND ILV.terr_id = B.terr_id ' || g_new_line ||
719 'AND A.txn_date BETWEEN B.start_date and B.end_date ' || g_new_line ||
720 'AND B.source_id = ' || p_source_id || g_new_line ||
721 'AND B.trans_type_id = ' || p_trans_id;
722
723 -- debug message
724 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
726 'jtf.plsql.JTY_TAE_GEN_PVT.build_ilv2.end',
727 'End of the procedure JTY_TAE_GEN_PVT.build_ilv2');
728 END IF;
729
730 retcode := 0;
731 errbuf := null;
732 EXCEPTION
733 WHEN OTHERS THEN
734 RETCODE := 2;
735 ERRBUF := SQLCODE || ' : ' || SQLERRM;
736 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
737 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
738 'jtf.plsql.JTY_TAE_GEN_PVT.build_ilv2.others',
739 substr(errbuf, 1, 4000));
740 END IF;
741
742 END build_ilv2;
743
744 /* this procedure build the matching sql based on rules for qualifier combinations */
745 PROCEDURE build_qualifier_rules(
746 p_source_id IN NUMBER,
747 p_trans_id IN NUMBER,
748 p_program_name IN VARCHAR2,
749 p_mode IN VARCHAR2,
750 p_qual_relation_prd IN NUMBER,
751 p_relation_factor IN NUMBER,
752 p_trans_table_name IN VARCHAR2,
753 p_match_table_name IN VARCHAR2,
754 p_denorm_table_name IN VARCHAR2,
755 p_new_mode_fetch IN VARCHAR2,
756 p_sql OUT NOCOPY CLOB,
757 retcode OUT NOCOPY VARCHAR2,
758 errbuf OUT NOCOPY VARCHAR2)
759 AS
760
761 CURSOR c_rel_prod_detail(cl_source_id number, cl_trans_id number, cl_relation_product number, cl_relation_factor number)
762 IS
763 SELECT DISTINCT
764 jqu.qual_usg_id
765 ,jqu.alias_rule1
766 ,jqu.op_eql
767 ,jqu.op_like
768 ,jqu.op_between
772 ,jtf_tae_qual_factors jtqf
769 ,jqu.op_common_where
770 FROM
771 jtf_qual_usgs_all jqu
773 ,jtf_tae_qual_products jtqp
774 ,jtf_tae_qual_prod_factors jtpf
775 WHERE jqu.org_id = -3113
776 AND jqu.qual_usg_id = jtqf.qual_usg_id
777 AND jtpf.qual_factor_id= jtqf.qual_factor_id
778 AND jtqf.relation_factor <> cl_relation_factor
779 AND jtqp.qual_product_id = jtpf.qual_product_id
780 AND jtqp.relation_product = cl_relation_product
781 AND jtqp.source_id = cl_source_id
782 AND jtqp.trans_object_type_id= cl_trans_id
783 ORDER BY jqu.qual_usg_id;
784
785 CURSOR c_dea_rel_prod_detail(cl_source_id number, cl_trans_id number, cl_relation_product number, cl_relation_factor number)
786 IS
787 SELECT DISTINCT
788 jqu.qual_usg_id
789 ,jqu.alias_rule1
790 ,jqu.op_eql
791 ,jqu.op_like
792 ,jqu.op_between
793 ,jqu.op_common_where
794 FROM
795 jtf_qual_usgs_all jqu
796 ,jty_dea_attr_factors jtqf
797 ,jty_dea_attr_products jtqp
798 ,jty_dea_attr_prod_factors jtpf
799 WHERE jqu.org_id = -3113
800 AND jqu.qual_usg_id = jtqf.qual_usg_id
801 AND jtpf.dea_attr_factors_id = jtqf.dea_attr_factors_id
802 AND jtqf.relation_factor <> cl_relation_factor
803 AND jtqp.dea_attr_products_id = jtpf.dea_attr_products_id
804 AND jtqp.attr_relation_product = cl_relation_product
805 AND jtqp.source_id = cl_source_id
806 AND jtqp.trans_type_id= cl_trans_id
807 ORDER BY jqu.qual_usg_id;
808
809 TYPE l_qual_usg_id_tbl_type IS TABLE OF jtf_qual_usgs_all.qual_usg_id%TYPE;
810 TYPE l_alias_rule1_tbl_type IS TABLE OF jtf_qual_usgs_all.alias_rule1%TYPE;
811 TYPE l_op_eql_tbl_type IS TABLE OF jtf_qual_usgs_all.op_eql%TYPE;
812 TYPE l_op_like_tbl_type IS TABLE OF jtf_qual_usgs_all.op_like%TYPE;
813 TYPE l_op_between_tbl_type IS TABLE OF jtf_qual_usgs_all.op_between%TYPE;
814 TYPE l_op_where_tbl_type IS TABLE OF jtf_qual_usgs_all.op_common_where%TYPE;
815
816 l_qual_usg_id_tbl l_qual_usg_id_tbl_type;
817 l_alias_rule1_tbl l_alias_rule1_tbl_type;
818 l_op_eql_tbl l_op_eql_tbl_type;
819 l_op_like_tbl l_op_like_tbl_type;
820 l_op_between_tbl l_op_between_tbl_type;
821 l_op_where_tbl l_op_where_tbl_type;
822
823 l_counter NUMBER;
824
825 l_from_str CLOB;
826 l_where_str CLOB;
827 l_predicate CLOB;
828 l_alias_rule1 jtf_qual_usgs_all.alias_rule1%TYPE;
829
830 BEGIN
831
832 -- debug message
833 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
834 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
835 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules.start',
836 'Start of the procedure JTY_TAE_GEN_PVT.build_qualifier_rules');
837 END IF;
838 -- debug message
839 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
840 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
841 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
842 'source_id='|| p_source_id || ',trans_id='|| p_trans_id
843 || ',qual_relation_prd='|| p_qual_relation_prd
844 || ',relation_factor='||p_relation_factor);
845 END IF;
846
847 l_counter := 1;
848
849 IF (p_mode = 'DATE EFFECTIVE') THEN
850 OPEN c_dea_rel_prod_detail(p_source_id, p_trans_id, p_qual_relation_prd, p_relation_factor);
851 FETCH c_dea_rel_prod_detail BULK COLLECT INTO
852 l_qual_usg_id_tbl
853 ,l_alias_rule1_tbl
854 ,l_op_eql_tbl
855 ,l_op_like_tbl
856 ,l_op_between_tbl
857 ,l_op_where_tbl;
858 CLOSE c_dea_rel_prod_detail;
859 ELSE
860 OPEN c_rel_prod_detail(p_source_id, p_trans_id, p_qual_relation_prd, p_relation_factor);
861 FETCH c_rel_prod_detail BULK COLLECT INTO
862 l_qual_usg_id_tbl
863 ,l_alias_rule1_tbl
864 ,l_op_eql_tbl
865 ,l_op_like_tbl
866 ,l_op_between_tbl
867 ,l_op_where_tbl;
868 CLOSE c_rel_prod_detail;
869 END IF;
870
871 IF (l_qual_usg_id_tbl.COUNT > 0) THEN
872 l_alias_rule1 := 'ABC';
873 FOR i IN l_qual_usg_id_tbl.FIRST .. l_qual_usg_id_tbl.LAST LOOP
874 -- debug message
875 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
876 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
877 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
878 l_counter || ',l_alias_rule1='|| l_alias_rule1
879 || ',l_alias_rule1_tbl('||i||')='|| l_alias_rule1_tbl(i)
880 || ',p_trans_table_name='|| p_trans_table_name
881 || ',p_denorm_table_name='|| p_denorm_table_name);
882 END IF;
883 IF (l_counter = 1) THEN
884 -- debug message
885 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
886 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
887 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
888 'enter l_counter=1');
889 END IF;
890 l_from_str := g_new_line || p_trans_table_name || ' A ' || g_new_line || ',' || p_denorm_table_name || ' B ';
891 IF (l_alias_rule1_tbl(i) IS NOT NULL) THEN
892 l_from_str := l_from_str || g_new_line || ',' || l_alias_rule1_tbl(i);
893 END IF;
894
898 END IF;
895 l_where_str := g_new_line || 'WHERE 1 = 1';
896 IF (p_new_mode_fetch <> 'Y') THEN
897 l_where_str := l_where_str || g_new_line || 'AND a.worker_id = :p_worker_id ';
899
900 l_predicate := g_new_line ||
901 build_predicate_for_operator(
902 l_op_where_tbl(i)
903 ,l_op_eql_tbl(i)
904 ,l_op_like_tbl(i)
905 ,l_op_between_tbl(i)
906 ,g_new_line);
907 -- debug message
908 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
909 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
910 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
911 'l_predicate='||l_predicate);
912 END IF;
913 -- SOLIN
914 ELSIF l_alias_rule1 = l_alias_rule1_tbl(i)
915 THEN
916 NULL;
917 ELSE
918 -- SOLIN end
919 -- ELSIF l_alias_rule1 <> l_alias_rule1_tbl(i)
920 -- debug message
921 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
922 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
923 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
924 'enter l_alias_rule1<>l_alias_rule1_tbl(i)');
925 END IF;
926 IF (l_alias_rule1_tbl(i) IS NOT NULL) THEN
927 l_from_str := l_from_str || g_new_line || ',' || l_alias_rule1_tbl(i);
928 END IF;
929
930 l_predicate := l_predicate || g_new_line ||
931 build_predicate_for_operator(
932 l_op_where_tbl(i)
933 ,l_op_eql_tbl(i)
934 ,l_op_like_tbl(i)
935 ,l_op_between_tbl(i)
936 ,g_new_line);
937 -- debug message
938 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
939 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
940 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
941 'l_predicate='||l_predicate);
942 END IF;
943 END IF; /* end IF (l_counter = 1) */
944 l_counter := l_counter + 1;
945 l_alias_rule1 := l_alias_rule1_tbl(i);
946 END LOOP; /* end loop FOR i IN l_qual_usg_id_tbl.FIRST .. l_qual_usg_id_tbl.LAST */
947 END IF; /* end IF (l_qual_usg_id_tbl.COUNT > 0) */
948
949 -- debug message
950 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
951 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
952 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
953 'l_from_str1='|| l_from_str
954 || ',l_predicate='|| l_predicate);
955 END IF;
956 l_from_str :=
957 append_inlineview(
958 p_source_id => p_source_id,
959 p_trans_id => p_trans_id,
960 p_mode => p_mode,
961 p_qual_relation_prd => p_qual_relation_prd,
962 p_from_str => l_from_str,
963 p_new_mode_fetch => p_new_mode_fetch);
964 -- debug message
965 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
966 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
967 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules',
968 'l_from_str2='|| l_from_str);
969 END IF;
970
971 p_sql := l_from_str || g_new_line || l_where_str || g_new_line || l_predicate || g_new_line ||
972 'AND ILV.terr_id = B.terr_id ' || g_new_line ||
973 'AND A.txn_date BETWEEN B.start_date and B.end_date ' || g_new_line ||
974 'AND B.source_id = ' || p_source_id || g_new_line ||
975 'AND B.trans_type_id = ' || p_trans_id;
976
977 -- debug message
978 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
979 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
980 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules.end',
981 'End of the procedure JTY_TAE_GEN_PVT.build_qualifier_rules');
982 END IF;
983
984 retcode := 0;
985 errbuf := null;
986 EXCEPTION
987 WHEN OTHERS THEN
988 RETCODE := 2;
989 ERRBUF := SQLCODE || ' : ' || SQLERRM;
990 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
991 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
992 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules.others',
993 substr(errbuf, 1, 4000));
994 END IF;
995
996 END build_qualifier_rules;
997
998
999 /* this procedure build the matching sql based on rules for qualifier combinations */
1000 PROCEDURE build_qualifier_rules1(
1001 p_source_id IN NUMBER,
1002 p_trans_id IN NUMBER,
1003 p_program_name IN VARCHAR2,
1004 p_mode IN VARCHAR2,
1005 p_qual_relation_prd IN NUMBER,
1006 p_trans_table_name IN VARCHAR2,
1007 p_match_table_name IN VARCHAR2,
1008 p_denorm_table_name IN VARCHAR2,
1009 p_new_mode_fetch IN VARCHAR2,
1010 p_sql OUT NOCOPY CLOB,
1011 retcode OUT NOCOPY VARCHAR2,
1012 errbuf OUT NOCOPY VARCHAR2)
1013 AS
1014 l_ilv1_sql CLOB;
1015 l_ilv2_sql CLOB;
1019 l_industry VARCHAR2(30);
1016 l_rel_prod1 NUMBER;
1017
1018 l_status VARCHAR2(30);
1020 l_owner VARCHAR2(30);
1021 first_time BOOLEAN;
1022 l_indent VARCHAR2(30);
1023
1024 CURSOR c_column_names(cl_table_name IN VARCHAR2, cl_owner IN VARCHAR2) is
1025 SELECT column_name
1026 FROM all_tab_columns
1027 WHERE table_name = cl_table_name
1028 AND owner = cl_owner
1029 AND column_name not in ('LAST_UPDATE_DATE', 'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN',
1030 'REQUEST_ID', 'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE');
1031
1032 L_SCHEMA_NOTFOUND EXCEPTION;
1033 BEGIN
1034
1035 -- debug message
1036 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1037 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1038 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules1.start',
1039 'Start of the procedure JTY_TAE_GEN_PVT.build_qualifier_rules1');
1040 END IF;
1041
1042 IF mod(p_qual_relation_prd,79) = 0 THEN
1043 l_rel_prod1 := 79;
1044 ELSIF mod(p_qual_relation_prd,137) = 0 THEN
1045 l_rel_prod1 := 137;
1046 ELSIF mod(p_qual_relation_prd,113) = 0 THEN
1047 l_rel_prod1 := 113;
1048 ELSIF mod(p_qual_relation_prd,131) = 0 THEN
1049 l_rel_prod1 := 131;
1050 ELSIF mod(p_qual_relation_prd,139) = 0 THEN
1051 l_rel_prod1 := 139;
1052 ELSIF mod(p_qual_relation_prd,163) = 0 THEN
1053 l_rel_prod1 := 163;
1054 ELSIF mod(p_qual_relation_prd,167) = 0 THEN
1055 l_rel_prod1 := 167;
1056 END IF;
1057
1058 p_sql := 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) USE_HASH(ILV1 ILV2) */ ';
1059
1060 IF (p_new_mode_fetch = 'Y') THEN
1061 /* Get the schema name corresponding to JTF application */
1062 IF (FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_owner)) THEN
1063 NULL;
1064 END IF;
1065
1066 IF (l_owner IS NULL) THEN
1067 RAISE L_SCHEMA_NOTFOUND;
1068 END IF;
1069
1070 /* Initialize local variables */
1071 first_time := TRUE;
1072 l_indent := ' ';
1073
1074 FOR column_names in c_column_names(p_trans_table_name, l_owner) LOOP
1075 IF (first_time) THEN
1076 p_sql := p_sql || g_new_line || l_indent || 'ILV2.' || column_names.column_name;
1077 first_time := FALSE;
1078 ELSE
1079 p_sql := p_sql || g_new_line || l_indent || ',' || 'ILV2.' || column_names.column_name;
1080 END IF;
1081 END LOOP;
1082
1083 p_sql := p_sql || g_new_line || l_indent || ', ILV2.TERR_ID'
1084 || g_new_line || l_indent || ', ILV2.ABSOLUTE_RANK'
1085 || g_new_line || l_indent || ', ILV2.TOP_LEVEL_TERR_ID'
1086 || g_new_line || l_indent || ', ILV2.NUM_WINNERS';
1087 ELSE
1088 p_sql := p_sql || g_new_line || l_indent || ' ILV2.trans_object_id'
1089 || g_new_line || l_indent || ', ILV2.trans_detail_object_id'
1090 || g_new_line || l_indent || ', ILV2.worker_id'
1091 || g_new_line || l_indent || ', ILV2.terr_id'
1092 || g_new_line || l_indent || ', ILV2.absolute_rank'
1093 || g_new_line || l_indent || ', ILV2.top_level_terr_id'
1094 || g_new_line || l_indent || ', ILV2.num_winners'
1095 || g_new_line || l_indent || ', ILV2.org_id'
1096 || g_new_line || l_indent || ', ILV2.txn_date';
1097 END IF; /* end IF (p_new_mode_fetch = 'Y') */
1098
1099 p_sql := p_sql || g_new_line || l_indent || ' FROM ( /* INLINE VIEW1 */ ';
1100
1101 build_ilv1(
1102 p_source_id => p_source_id,
1103 p_trans_id => p_trans_id,
1104 p_qual_relation_prd => p_qual_relation_prd,
1105 p_relation_factor => l_rel_prod1,
1106 p_mode => p_mode,
1107 p_denorm_table_name => p_denorm_table_name,
1108 p_new_mode_fetch => p_new_mode_fetch,
1109 p_sql => l_ilv1_sql,
1110 retcode => retcode,
1111 errbuf => errbuf);
1112
1113 IF (retcode <> 0) THEN
1114 -- debug message
1115 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1116 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1117 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules1.build_ilv1',
1118 'JTY_TAE_GEN_PVT.build_ilv1 API has failed');
1119 END IF;
1120
1121 RAISE FND_API.G_EXC_ERROR;
1122 END IF;
1123
1124
1125 p_sql := p_sql || l_ilv1_sql;
1126
1127 p_sql := p_sql || g_new_line || l_indent || ' ) ILV1, ';
1128 p_sql := p_sql || g_new_line || l_indent || ' ( /* INLINE VIEW2 */ ';
1129
1130 build_ilv2(
1131 p_source_id => p_source_id,
1132 p_trans_id => p_trans_id,
1133 p_program_name => p_program_name,
1134 p_mode => p_mode,
1135 p_qual_relation_prd => p_qual_relation_prd,
1136 p_relation_factor => l_rel_prod1,
1137 p_trans_table_name => p_trans_table_name,
1138 p_match_table_name => p_match_table_name,
1139 p_denorm_table_name => p_denorm_table_name,
1140 p_new_mode_fetch => p_new_mode_fetch,
1141 p_sql => l_ilv2_sql,
1142 retcode => retcode,
1146 -- debug message
1143 errbuf => errbuf);
1144
1145 IF (retcode <> 0) THEN
1147 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1149 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules1.build_qualifier_rules',
1150 'JTY_TAE_GEN_PVT.build_qualifier_rules API has failed');
1151 END IF;
1152
1153 RAISE FND_API.G_EXC_ERROR;
1154 END IF;
1155
1156 p_sql := p_sql || l_ilv2_sql;
1157
1158 p_sql := p_sql || g_new_line || l_indent || ' ) ILV2 ';
1159 p_sql := p_sql || g_new_line || l_indent || ' WHERE ILV1.terr_id = ILV2.terr_id ';
1160
1161 IF l_rel_prod1 = 79 THEN
1162 p_sql := p_sql || g_new_line || l_indent || ' AND ILV1.customer_id = ILV2.squal_num01';
1163 p_sql := p_sql || g_new_line || l_indent || ' AND ( (ILV1.address_id IS NULL )';
1164 p_sql := p_sql || g_new_line || l_indent || ' OR ';
1165 p_sql := p_sql || g_new_line || l_indent || ' (ILV1.address_id= ILV2.squal_num02)';
1166 p_sql := p_sql || g_new_line || l_indent || ' )';
1167 ELSIF l_rel_prod1 = 137 THEN
1168 p_sql := p_sql || g_new_line || l_indent || ' AND ILV1.sales_lead_id = ILV2.trans_object_id';
1169 ELSIF l_rel_prod1 = 113 THEN
1170 p_sql := p_sql || g_new_line || l_indent || ' AND ILV1.sales_lead_id = ILV2.trans_object_id';
1171 ELSIF l_rel_prod1 = 131 THEN
1172 p_sql := p_sql || g_new_line || l_indent || ' AND ILV1.sales_lead_id = ILV2.trans_object_id';
1173 ELSIF l_rel_prod1 = 139 THEN
1174 p_sql := p_sql || g_new_line || l_indent || ' AND ILV1.lead_id = ILV2.trans_object_id';
1175 ELSIF l_rel_prod1 = 163 THEN
1176 p_sql := p_sql || g_new_line || l_indent || ' AND ILV1.lead_id = ILV2.trans_object_id';
1177 ELSIF l_rel_prod1 = 167 THEN
1178 p_sql := p_sql || g_new_line || l_indent || ' AND ILV1.lead_id = ILV2.trans_object_id';
1179 END IF;
1180
1181 -- debug message
1182 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1183 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1184 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules1.end',
1185 'End of the procedure JTY_TAE_GEN_PVT.build_qualifier_rules1');
1186 END IF;
1187
1188 retcode := 0;
1189 errbuf := null;
1190 EXCEPTION
1191 WHEN FND_API.G_EXC_ERROR THEN
1192 RETCODE := 2;
1193 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1194 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1195 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules1.g_exc_error',
1196 'API JTY_TAE_GEN_PVT..build_qualifier_rules1 has failed with FND_API.G_EXC_ERROR exception');
1197 END IF;
1198
1199 WHEN L_SCHEMA_NOTFOUND THEN
1200 errbuf := 'Schema name corresponding to JTF application not found';
1201 retcode := 2;
1202 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1204 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules1.l_schema_notfound',
1205 errbuf);
1206 END IF;
1207
1208 WHEN OTHERS THEN
1209 RETCODE := 2;
1210 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1211 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1212 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1213 'jtf.plsql.JTY_TAE_GEN_PVT.build_qualifier_rules1.others',
1214 substr(errbuf, 1, 4000));
1215 END IF;
1216
1217 END build_qualifier_rules1;
1218
1219 /* thie procedure builds the matching sql for qualifier combinations */
1220 PROCEDURE gen_terr_rules_recurse (
1221 p_source_id IN NUMBER,
1222 p_trans_id IN NUMBER,
1223 p_program_name IN VARCHAR2,
1224 p_mode IN VARCHAR2,
1225 p_qual_relation_prd IN NUMBER,
1226 p_trans_table_name IN VARCHAR2,
1227 p_match_table_name IN VARCHAR2,
1228 p_denorm_table_name IN VARCHAR2,
1229 p_new_mode_fetch IN VARCHAR2,
1230 p_match_sql OUT NOCOPY CLOB,
1231 retcode OUT NOCOPY VARCHAR2,
1232 errbuf OUT NOCOPY VARCHAR2)
1233 AS
1234
1235 l_procedure_name VARCHAR2(30);
1236 l_procedure_desc VARCHAR2(255);
1237 l_parameter_list1 VARCHAR2(255);
1238 l_parameter_list2 VARCHAR2(360);
1239
1240 l_str_len NUMBER;
1241 l_start NUMBER;
1242 l_get_nchar NUMBER;
1243 l_next_newline NUMBER;
1244 l_rule_str VARCHAR2(256);
1245 l_indent VARCHAR2(30);
1246
1247 l_insert_nm_trans VARCHAR2(3000);
1248 l_select_nm_trans VARCHAR2(3000);
1249
1250 l_match_sql CLOB;
1251 l_sql CLOB;
1252 BEGIN
1253 -- debug message
1254 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1255 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1256 'jtf.plsql.JTY_TAE_GEN_PVT.gen_terr_rules_recurse.start',
1257 'Start of the procedure JTY_TAE_GEN_PVT.gen_terr_rules_recurse');
1258 END IF;
1259
1260 IF (p_new_mode_fetch = 'Y') THEN
1261 get_insert_select_nmtrans(
1262 p_match_table_name => p_match_table_name,
1263 p_insert_stmt => l_insert_nm_trans,
1264 p_select_stmt => l_select_nm_trans,
1265 errbuf => errbuf,
1269 -- debug message
1266 retcode => retcode);
1267
1268 IF (retcode <> 0) THEN
1270 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1271 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1272 'jtf.plsql.JTY_TAE_GEN_PVT.gen_terr_rules_recurse.get_insert_select_nmtrans',
1273 'JTY_TAE_GEN_PVT.get_insert_select_nmtrans API has failed');
1274 END IF;
1275
1276 RAISE FND_API.G_EXC_ERROR;
1277 END IF;
1278
1279 END IF;
1280
1281 IF (p_new_mode_fetch = 'Y') THEN
1282 l_match_sql := l_insert_nm_trans;
1283 ELSE
1284 l_match_sql :=
1285 ' INSERT INTO '|| p_match_table_name || ' i' ||
1286 ' (' ||
1287 ' trans_object_id' ||
1288 ' ,trans_detail_object_id' ||
1289 ' ,worker_id' ||
1290 ' ,source_id' ||
1291 ' ,trans_object_type_id' ||
1292 ' ,last_update_date' ||
1293 ' ,last_updated_by' ||
1294 ' ,creation_date' ||
1295 ' ,created_by' ||
1296 ' ,last_update_login' ||
1297 ' ,request_id' ||
1298 ' ,program_application_id' ||
1299 ' ,program_id' ||
1300 ' ,program_update_date' ||
1301 ' ,terr_id' ||
1302 ' ,absolute_rank' ||
1303 ' ,top_level_terr_id' ||
1304 ' ,num_winners' ||
1305 ' ,org_id' ||
1306 ' ,txn_date' ||
1307 ' )' ;
1308 END IF; /* end IF ((p_new_mode_fetch = 'Y') AND (p_qual_relation_prd <> 4841)) */
1309
1310 IF (p_new_mode_fetch = 'Y') THEN
1311 l_match_sql := l_match_sql || ' ' || l_select_nm_trans;
1312 ELSE
1313 IF ((mod(p_qual_relation_prd,79) = 0 and p_qual_relation_prd/79 <> 1) or -- account classification
1314 (mod(p_qual_relation_prd,137) = 0 and p_qual_relation_prd/137 <> 1) or -- lead expected purchase
1315 (mod(p_qual_relation_prd,113) = 0 and p_qual_relation_prd/113 <> 1) or -- purchase amount
1316 (mod(p_qual_relation_prd,131) = 0 and p_qual_relation_prd/131 <> 1) or -- lead inventory item
1317 (mod(p_qual_relation_prd,163) = 0 and p_qual_relation_prd/163 <> 1) or -- opportunity inventory item
1318 (mod(p_qual_relation_prd,167) = 0 and p_qual_relation_prd/167 <> 1) or -- opportunity classification
1319 (mod(p_qual_relation_prd,139) = 0 and p_qual_relation_prd/139 <> 1)) THEN -- opportunity expected purchase
1320
1321 l_match_sql := l_match_sql ||
1322 ' SELECT /*+ USE_CONCAT */ DISTINCT ' ||
1323 ' ILV2.trans_object_id' ||
1324 ' ,ILV2.trans_detail_object_id' ||
1325 ' ,ILV2.worker_id' ||
1326 ' ,' || p_source_id ||
1327 ' ,' || p_trans_id ||
1328 ' ,:LAST_UPDATED_DATE ' ||
1329 ' ,:LAST_UPDATED_BY ' ||
1330 ' ,:CREATION_DATE ' ||
1331 ' ,:CREATED_BY ' ||
1332 ' ,:LAST_UPDATE_LOGIN ' ||
1333 ' ,:REQUEST_ID ' ||
1334 ' ,:PROGRAM_APPLICATION_ID ' ||
1335 ' ,:PROGRAM_ID ' ||
1336 ' ,:PROGRAM_UPDATE_DATE ' ||
1337 ' ,ILV2.terr_id' ||
1338 ' ,ILV2.absolute_rank' ||
1339 ' ,ILV2.top_level_terr_id' ||
1340 ' ,ILV2.num_winners' ||
1341 ' ,ILV2.org_id' ||
1342 ' ,ILV2.txn_date' ||
1343 ' FROM ';
1344 ELSE
1345 l_match_sql := l_match_sql ||
1346 ' SELECT /*+ USE_CONCAT */ DISTINCT ' ||
1347 ' A.trans_object_id' ||
1348 ' ,A.trans_detail_object_id' ||
1349 ' ,A.worker_id' ||
1350 ' ,' || p_source_id ||
1351 ' ,' || p_trans_id ||
1352 ' ,:LAST_UPDATED_DATE ' ||
1353 ' ,:LAST_UPDATED_BY ' ||
1354 ' ,:CREATION_DATE ' ||
1355 ' ,:CREATED_BY ' ||
1356 ' ,:LAST_UPDATE_LOGIN ' ||
1357 ' ,:REQUEST_ID ' ||
1358 ' ,:PROGRAM_APPLICATION_ID ' ||
1359 ' ,:PROGRAM_ID ' ||
1360 ' ,:PROGRAM_UPDATE_DATE ' ||
1361 ' ,ILV.terr_id' ||
1362 ' ,ILV.absolute_rank' ||
1363 ' ,ILV.top_level_terr_id' ||
1364 ' ,ILV.num_winners' ||
1365 ' ,ILV.org_id' ||
1366 ' ,A.txn_date' ||
1367 ' FROM ';
1368 END IF;
1369 END IF; /* end if (p_new_mode_fetch = 'Y') */
1370
1371 IF ((mod(p_qual_relation_prd,79) = 0 and p_qual_relation_prd/79 <> 1) or -- account classification
1372 (mod(p_qual_relation_prd,137) = 0 and p_qual_relation_prd/137 <> 1) or -- lead expected purchase
1373 (mod(p_qual_relation_prd,113) = 0 and p_qual_relation_prd/113 <> 1) or -- purchase amount
1374 (mod(p_qual_relation_prd,131) = 0 and p_qual_relation_prd/131 <> 1) or -- lead inventory item
1375 (mod(p_qual_relation_prd,163) = 0 and p_qual_relation_prd/163 <> 1) or -- opportunity inventory item
1379 /* need bracket */
1376 (mod(p_qual_relation_prd,167) = 0 and p_qual_relation_prd/167 <> 1) or -- opportunity classification
1377 (mod(p_qual_relation_prd,139) = 0 and p_qual_relation_prd/139 <> 1)) THEN -- opportunity expected purchase
1378
1380 l_match_sql := l_match_sql || ' ( ';
1381
1382 Build_Qualifier_Rules1(
1383 p_source_id => p_source_id
1384 ,p_trans_id => p_trans_id
1385 ,p_program_name => p_program_name
1386 ,p_mode => p_mode
1387 ,p_qual_relation_prd => p_qual_relation_prd
1388 ,p_trans_table_name => p_trans_table_name
1389 ,p_match_table_name => p_match_table_name
1390 ,p_denorm_table_name => p_denorm_table_name
1391 ,p_new_mode_fetch => p_new_mode_fetch
1392 ,p_sql => l_sql
1393 ,retcode => retcode
1394 ,errbuf => errbuf);
1395
1396 IF (retcode <> 0) THEN
1397 -- debug message
1398 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1399 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1400 'jtf.plsql.JTY_TAE_GEN_PVT.gen_terr_rules_recurse.Build_Qualifier_Rules1',
1401 'JTY_TAE_GEN_PVT.Build_Qualifier_Rules1 API has failed');
1402 END IF;
1403
1404 RAISE FND_API.G_EXC_ERROR;
1405 END IF;
1406
1407 l_match_sql := l_match_sql || l_sql;
1408
1409 IF p_new_mode_fetch = 'Y' THEN
1410 l_match_sql := l_match_sql || ' ) A ';
1411 ELSE
1412 l_match_sql := l_match_sql || ' ) ILV2 ';
1413 END IF;
1414 ELSE
1415 /* brackets are not needed after FROM clause */
1416 Build_Qualifier_Rules(
1417 p_source_id => p_source_id
1418 ,p_trans_id => p_trans_id
1419 ,p_program_name => p_program_name
1420 ,p_mode => p_mode
1421 ,p_qual_relation_prd => p_qual_relation_prd
1422 ,p_relation_factor => 1
1423 ,p_trans_table_name => p_trans_table_name
1424 ,p_match_table_name => p_match_table_name
1425 ,p_denorm_table_name => p_denorm_table_name
1426 ,p_new_mode_fetch => p_new_mode_fetch
1427 ,p_sql => l_sql
1428 ,retcode => retcode
1429 ,errbuf => errbuf);
1430
1431 IF (retcode <> 0) THEN
1432 -- debug message
1433 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1434 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1435 'jtf.plsql.JTY_TAE_GEN_PVT.gen_terr_rules_recurse.Build_Qualifier_Rules',
1436 'JTY_TAE_GEN_PVT.Build_Qualifier_Rules API has failed');
1437 END IF;
1438
1439 RAISE FND_API.G_EXC_ERROR;
1440 END IF;
1441
1442 l_match_sql := l_match_sql || l_sql;
1443 END IF;
1444
1445 -- debug message
1446 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1447 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1448 'jtf.plsql.JTY_TAE_GEN_PVT.gen_terr_rules_recurse.end',
1449 'End of the procedure JTY_TAE_GEN_PVT.gen_terr_rules_recurse');
1450 END IF;
1451
1452 p_match_sql := l_match_sql;
1453
1454 retcode := 0;
1455 errbuf := null;
1456
1457 EXCEPTION
1458 WHEN FND_API.G_EXC_ERROR THEN
1459 RETCODE := 2;
1460 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1461 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1462 'jtf.plsql.JTY_TAE_GEN_PVT.gen_terr_rules_recurse.g_exc_error',
1463 'API JTY_TAE_GEN_PVT.gen_terr_rules_recurse has failed with FND_API.G_EXC_ERROR exception');
1464 END IF;
1465
1466 WHEN OTHERS THEN
1467 RETCODE := 2;
1468 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1469 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1471 'jtf.plsql.JTY_TAE_GEN_PVT.gen_terr_rules_recurse.others',
1472 substr(errbuf, 1, 4000));
1473 END IF;
1474 END gen_terr_rules_recurse;
1475
1476
1477 /* entry point of this package, that generates the batch matching sql */
1478 PROCEDURE gen_batch_sql (
1479 p_source_id IN NUMBER,
1480 p_trans_id IN NUMBER,
1481 p_mode IN VARCHAR2,
1482 p_qual_prd_tbl IN JTY_TERR_ENGINE_GEN_PVT.qual_prd_tbl_type,
1483 x_Return_Status OUT NOCOPY VARCHAR2,
1484 x_Msg_Count OUT NOCOPY NUMBER,
1485 x_Msg_Data OUT NOCOPY VARCHAR2,
1486 errbuf OUT NOCOPY VARCHAR2,
1487 retcode OUT NOCOPY VARCHAR2)
1488
1489 AS
1490
1491 CURSOR c_pgm_details (cl_mode VARCHAR2, cl_source_id NUMBER, cl_trans_id NUMBER) IS
1492 select
1493 a.program_name
1494 ,decode(cl_mode, 'DATE EFFECTIVE', a.batch_dea_trans_table_name, a.batch_trans_table_name)
1495 ,a.batch_nm_trans_table_name
1496 ,a.batch_match_table_name
1497 from jty_trans_usg_pgm_details a
1498 where a.source_id = cl_source_id
1499 and a.trans_type_id = cl_trans_id
1500 and a.batch_enable_flag = 'Y';
1501
1502 TYPE l_pgm_name_tbl_type IS TABLE OF jty_trans_usg_pgm_details.program_name%TYPE;
1503 TYPE l_trans_tbl_type IS TABLE OF jty_trans_usg_pgm_details.batch_trans_table_name%TYPE;
1504 TYPE l_nm_trans_tbl_type IS TABLE OF jty_trans_usg_pgm_details.batch_nm_trans_table_name%TYPE;
1508 l_trans_tbl l_trans_tbl_type;
1505 TYPE l_match_tbl_type IS TABLE OF jty_trans_usg_pgm_details.batch_match_table_name%TYPE;
1506
1507 l_pgm_name_tbl l_pgm_name_tbl_type;
1509 l_nm_trans_tbl l_nm_trans_tbl_type;
1510 l_match_tbl l_match_tbl_type;
1511
1512 l_denorm_table_name VARCHAR2(50);
1513 l_no_of_records NUMBER;
1514 l_match_sql CLOB;
1515 l_nm_match_sql CLOB;
1516 l_nmc_match_sql CLOB;
1517
1518 BEGIN
1519
1520 -- debug message
1521 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1522 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1523 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.start',
1524 'Start of the procedure JTY_TAE_GEN_PVT.gen_batch_sql');
1525 END IF;
1526
1527 IF (p_qual_prd_tbl.COUNT = 0) THEN
1528 -- debug message
1529 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1530 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1531 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.no_qual_product',
1532 'JTY_TAE_GEN_PVT.gen_batch_sql API has failed as there is no qual product to be processed');
1533 END IF;
1534
1535 RAISE FND_API.G_EXC_ERROR;
1536 END IF;
1537
1538 IF (p_mode = 'DATE EFFECTIVE') THEN
1539 SELECT denorm_dea_value_table_name
1540 INTO l_denorm_table_name
1541 FROM jtf_sources_all
1542 WHERE source_id = p_source_id;
1543
1544 jty_tae_control_pvt.Classify_dea_Territories (
1545 p_source_id => p_source_id,
1546 p_trans_id => p_trans_id,
1547 p_qual_prd_tbl => p_qual_prd_tbl,
1548 x_Return_Status => x_Return_Status,
1549 x_Msg_Count => x_Msg_Count,
1550 x_Msg_Data => x_Msg_Data,
1551 ERRBUF => errbuf,
1552 RETCODE => retcode );
1553
1554 IF (retcode <> 0) THEN
1555 -- debug message
1556 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1557 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1558 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.Classify_dea_Territories',
1559 'jty_tae_control_pvt.Classify_dea_Territories API has failed');
1560 END IF;
1561
1562 RAISE FND_API.G_EXC_ERROR;
1563 END IF;
1564 ELSE
1565 SELECT denorm_value_table_name
1566 INTO l_denorm_table_name
1567 FROM jtf_sources_all
1568 WHERE source_id = p_source_id;
1569
1570 jty_tae_control_pvt.Classify_Territories (
1571 p_source_id => p_source_id,
1572 p_trans_id => p_trans_id,
1573 p_mode => p_mode,
1574 p_qual_prd_tbl => p_qual_prd_tbl,
1575 x_Return_Status => x_Return_Status,
1576 x_Msg_Count => x_Msg_Count,
1577 x_Msg_Data => x_Msg_Data,
1578 ERRBUF => errbuf,
1579 RETCODE => retcode );
1580
1581 IF (retcode <> 0) THEN
1582 -- debug message
1583 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1584 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1585 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.Classify_Territories',
1586 'jty_tae_control_pvt.Classify_Territories API has failed');
1587 END IF;
1588
1589 RAISE FND_API.G_EXC_ERROR;
1590 END IF;
1591
1592 END IF; /* end IF (p_mode = 'DATE EFFECTIVE') */
1593
1594 OPEN c_pgm_details(p_mode, p_source_id, p_trans_id);
1595 FETCH c_pgm_details BULK COLLECT INTO
1596 l_pgm_name_tbl
1597 ,l_trans_tbl
1598 ,l_nm_trans_tbl
1599 ,l_match_tbl;
1600 CLOSE c_pgm_details;
1601
1602 IF (l_pgm_name_tbl.COUNT > 0) THEN
1603 FOR i IN l_pgm_name_tbl.FIRST .. l_pgm_name_tbl.LAST LOOP
1604 FOR j IN p_qual_prd_tbl.FIRST .. p_qual_prd_tbl.LAST LOOP
1605
1606 IF (p_mode = 'DATE EFFECTIVE') THEN
1607 SELECT count(*)
1608 INTO l_no_of_records
1609 FROM jty_dea_attr_products_sql
1610 WHERE source_id = p_source_id
1611 AND trans_type_id = p_trans_id
1612 AND program_name = l_pgm_name_tbl(i)
1613 AND attr_relation_product = p_qual_prd_tbl(j);
1614 ELSE
1615 SELECT count(*)
1616 INTO l_no_of_records
1617 FROM jty_tae_attr_products_sql
1618 WHERE source_id = p_source_id
1619 AND trans_type_id = p_trans_id
1620 AND program_name = l_pgm_name_tbl(i)
1621 AND attr_relation_product = p_qual_prd_tbl(j);
1622 END IF;
1623
1624 IF (l_no_of_records = 0) THEN
1625 gen_terr_rules_recurse (
1626 p_source_id => p_source_id,
1627 p_trans_id => p_trans_id,
1628 p_program_name => l_pgm_name_tbl(i),
1629 p_mode => p_mode,
1630 p_qual_relation_prd => p_qual_prd_tbl(j),
1631 p_trans_table_name => l_trans_tbl(i),
1632 p_match_table_name => l_match_tbl(i),
1633 p_denorm_table_name => l_denorm_table_name,
1634 p_new_mode_fetch => 'N',
1635 p_match_sql => l_match_sql,
1636 retcode => retcode,
1637 errbuf => errbuf);
1638
1639 IF (retcode <> 0) THEN
1640 -- debug message
1641 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1645 END IF;
1642 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1643 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.gen_terr_rules_recurse1',
1644 'JTY_TAE_GEN_PVT.gen_terr_rules_recurse API has failed');
1646
1647 RAISE FND_API.G_EXC_ERROR;
1648 END IF;
1649
1650 IF (p_mode <> 'DATE EFFECTIVE') THEN
1651 gen_terr_rules_recurse (
1652 p_source_id => p_source_id,
1653 p_trans_id => p_trans_id,
1654 p_program_name => l_pgm_name_tbl(i),
1655 p_mode => p_mode,
1656 p_qual_relation_prd => p_qual_prd_tbl(j),
1657 p_trans_table_name => l_trans_tbl(i),
1658 p_match_table_name => l_nm_trans_tbl(i),
1659 p_denorm_table_name => l_denorm_table_name,
1660 p_new_mode_fetch => 'Y',
1661 p_match_sql => l_nmc_match_sql,
1662 retcode => retcode,
1663 errbuf => errbuf);
1664
1665 IF (retcode <> 0) THEN
1666 -- debug message
1667 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1668 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1669 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.gen_terr_rules_recurse2',
1670 'JTY_TAE_GEN_PVT.gen_terr_rules_recurse API has failed');
1671 END IF;
1672
1673 RAISE FND_API.G_EXC_ERROR;
1674 END IF;
1675
1676 gen_terr_rules_recurse (
1677 p_source_id => p_source_id,
1678 p_trans_id => p_trans_id,
1679 p_program_name => l_pgm_name_tbl(i),
1680 p_mode => p_mode,
1681 p_qual_relation_prd => p_qual_prd_tbl(j),
1682 p_trans_table_name => l_nm_trans_tbl(i),
1683 p_match_table_name => l_match_tbl(i),
1684 p_denorm_table_name => l_denorm_table_name,
1685 p_new_mode_fetch => 'N',
1686 p_match_sql => l_nm_match_sql,
1687 retcode => retcode,
1688 errbuf => errbuf);
1689
1690 IF (retcode <> 0) THEN
1691 -- debug message
1692 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1693 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1694 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.gen_terr_rules_recurse3',
1695 'JTY_TAE_GEN_PVT.gen_terr_rules_recurse API has failed');
1696 END IF;
1697
1698 RAISE FND_API.G_EXC_ERROR;
1699 END IF;
1700
1701 INSERT INTO JTY_TAE_ATTR_PRODUCTS_SQL(
1702 ATTR_PRODUCTS_SQL_ID
1703 ,SOURCE_ID
1704 ,TRANS_TYPE_ID
1705 ,PROGRAM_NAME
1706 ,ATTR_RELATION_PRODUCT
1707 ,LAST_UPDATE_DATE
1708 ,LAST_UPDATED_BY
1709 ,CREATION_DATE
1710 ,CREATED_BY
1711 ,LAST_UPDATE_LOGIN
1712 ,BATCH_MATCH_SQL
1713 ,BATCH_NM_MATCH_SQL
1714 ,BATCH_NMC_MATCH_SQL
1715 ,KEEP_FLAG
1716 ,PROGRAM_ID
1717 ,PROGRAM_LOGIN_ID
1718 ,PROGRAM_APPLICATION_ID
1719 ,REQUEST_ID
1720 ,PROGRAM_UPDATE_DATE)
1721 VALUES(
1722 JTY_TAE_ATTR_PRODUCTS_SQL_S.NEXTVAL
1723 ,p_source_id
1724 ,p_trans_id
1725 ,l_pgm_name_tbl(i)
1726 ,p_qual_prd_tbl(j)
1727 ,G_SYSDATE
1728 ,G_USER_ID
1729 ,G_SYSDATE
1730 ,G_USER_ID
1731 ,G_USER_ID
1732 ,l_match_sql
1733 ,l_nm_match_sql
1734 ,l_nmc_match_sql
1735 ,'N'
1736 ,G_REQUEST_ID
1737 ,G_PROGRAM_APPL_ID
1738 ,G_PROGRAM_ID
1739 ,G_REQUEST_ID
1740 ,G_SYSDATE
1741 );
1742
1743 ELSE
1744 INSERT INTO JTY_DEA_ATTR_PRODUCTS_SQL(
1745 DEA_ATTR_PRODUCTS_SQL_ID
1746 ,SOURCE_ID
1747 ,TRANS_TYPE_ID
1748 ,PROGRAM_NAME
1749 ,ATTR_RELATION_PRODUCT
1750 ,LAST_UPDATE_DATE
1751 ,LAST_UPDATED_BY
1752 ,CREATION_DATE
1753 ,CREATED_BY
1754 ,LAST_UPDATE_LOGIN
1755 ,BATCH_DEA_MATCH_SQL
1756 ,KEEP_FLAG
1757 ,PROGRAM_ID
1758 ,PROGRAM_LOGIN_ID
1759 ,PROGRAM_APPLICATION_ID
1760 ,REQUEST_ID
1761 ,PROGRAM_UPDATE_DATE)
1762 VALUES(
1763 JTY_DEA_ATTR_PRODUCTS_SQL_S.NEXTVAL
1764 ,p_source_id
1765 ,p_trans_id
1766 ,l_pgm_name_tbl(i)
1767 ,p_qual_prd_tbl(j)
1768 ,G_SYSDATE
1769 ,G_USER_ID
1770 ,G_SYSDATE
1771 ,G_USER_ID
1772 ,G_USER_ID
1773 ,l_match_sql
1774 ,'N'
1775 ,G_REQUEST_ID
1776 ,G_PROGRAM_APPL_ID
1777 ,G_PROGRAM_ID
1778 ,G_REQUEST_ID
1779 ,G_SYSDATE
1780 );
1781 END IF; /* end IF (p_mode <> 'DATE EFFECTIVE') */
1782 END IF; /* end IF (l_no_of_records = 0) */
1783 END LOOP; /* end loop FOR j IN p_qual_prd_tbl.FIRST .. p_qual_prd_tbl.LAST */
1784 END LOOP; /* end loop FOR i IN l_pgm_name_tbl.FIRST .. l_pgm_name_tbl.LAST */
1785 END IF; /* end IF (l_pgm_name_tbl.COUNT > 0) */
1786
1787 -- debug message
1788 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1789 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1790 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.end',
1791 'End of the procedure JTY_TAE_GEN_PVT.gen_batch_sql');
1792 END IF;
1793
1794 retcode := 0;
1795 errbuf := null;
1796
1797 EXCEPTION
1798 WHEN FND_API.G_EXC_ERROR THEN
1799 RETCODE := 2;
1800 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1801 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1802 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.g_exc_error',
1803 'API JTY_TAE_GEN_PVT.gen_batch_sql has failed with FND_API.G_EXC_ERROR exception');
1804 END IF;
1805
1806 WHEN OTHERS THEN
1807 RETCODE := 2;
1808 ERRBUF := SQLCODE || ' : ' || SQLERRM;
1809 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1810 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1811 'jtf.plsql.JTY_TAE_GEN_PVT.gen_batch_sql.others',
1812 substr(errbuf, 1, 4000));
1813 END IF;
1814 END gen_batch_sql;
1815
1816 END JTY_TAE_GEN_PVT;