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