[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_RUNNING_TOTAL_PVT
Source
1 PACKAGE BODY AMS_List_running_total_pvt AS
2 /* $Header: amslrutb.pls 120.7 2006/06/28 19:16:25 dgyawali ship $*/
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMS_List_running_total_pvt
7 --
8 -- PURPOSE
9 -- This package calculates the running totals
10 --
11 -- Procedures:
12 --
13 --
14 -- NOTES
15 --
16 --
17 -- HISTORY
18 -- 10/29/2003 usingh created
19 -- End of Comments
20
21 PROCEDURE gen_constant_filter (
22 x_filter_sql IN OUT NOCOPY VARCHAR2,
23 x_string_params IN OUT NOCOPY sql_string_4k,
24 x_num_params IN OUT NOCOPY NUMBER,
25 p_template_id IN NUMBER
26 );
27
28 PROCEDURE calculate_running_totals(
29 Errbuf OUT NOCOPY VARCHAR2,
30 Retcode OUT NOCOPY VARCHAR2,
31 p_template_id NUMBER
32 ) IS
33 l_request_id number;
34 l_return_status VARCHAR2(1);
35 l_profile_value VARCHAR2(30);
36 l_fnd_return boolean;
37 l_application_short_name VARCHAR2(200);
38 l_status_AppInfo varchar2(1);
39 l_industry_AppInfo varchar2(1);
40 l_ams_schema VARCHAR2(200);
41 l_drop_string VARCHAR2(32767);
42 l_owner VARCHAR2(30);
43 l_view_name VARCHAR2(200);
44
45 cursor c_view_exists(c_view_name VARCHAR2) is
46 select owner from sys.all_tables
47 where table_name = c_view_name;
48
49 BEGIN
50 IF NVL(FND_PROFILE.VALUE('AMS_ENABLE_RECALC_AND_PREVIEW'), 'N') = 'Y'
51 THEN
52 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
53 application => 'AMS',
54 program => 'AMSGMVTP',
55 argument1 => p_template_id);
56
57 IF l_request_id = 0 THEN
58 RAISE FND_API.g_exc_unexpected_error;
59 end if;
60
61 UPDATE ams_query_template_all
62 SET recalc_table_status = 'IN_PROGRESS',
63 mv_available_flag = 'N',
64 request_id = l_request_id,
65 LAST_UPDATE_DATE = SYSDATE,
66 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
67 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
68 PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
69 PROGRAM_UPDATE_DATE = SYSDATE
70 WHERE template_id = p_template_id;
71 ELSE
72 UPDATE ams_query_template_all
73 SET recalc_table_status = 'DRAFT',
74 mv_available_flag = 'N',
75 LAST_UPDATE_DATE = SYSDATE,
76 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
77 WHERE template_id = p_template_id;
78
79 l_view_name := 'AMS_QT_'||to_char(p_template_id)||'_MV';
80 open c_view_exists(l_view_name);
81 fetch c_view_exists into l_owner;
82 close c_view_exists;
83
84 IF l_owner IS NOT NULL THEN
85 l_drop_string := 'DROP TABLE '||l_owner||'.'||l_view_name;
86 execute immediate l_drop_string;
87 END IF;
88 END IF;
89 commit;
90 retcode:= 0;
91 EXCEPTION
92 WHEN OTHERS THEN
93 errbuf:= substr(SQLERRM,1,254);
94 retcode:= 2;
95 raise;
96 end calculate_running_totals;
97 -- ----------------------------------------------------------
98 PROCEDURE generate_mv_for_template (
99 Errbuf OUT NOCOPY VARCHAR2,
100 Retcode OUT NOCOPY VARCHAR2,
101 p_template_id NUMBER
102 ) IS
103 l_create_string VARCHAR2(32767);
104 l_create1_string VARCHAR2(32767);
105 l_insert_string VARCHAR2(32767);
106 l_select_string VARCHAR2(32767);
107 l_from_string VARCHAR2(32767) := 'FROM';
108 l_where_string VARCHAR2(32767) := 'WHERE';
109 l_policy_where VARCHAR2(32767);
110 l_view_name VARCHAR2(200);
111 l_attribute_name VARCHAR2(30);
112 l_query_alias_id NUMBER;
113 l_q_alias_id NUMBER;
114 -- l_source_object_name VARCHAR2(30);
115 l_source_object_name VARCHAR2(2000);
116 l_master_flag VARCHAR2(1);
117 l_source_pk_field VARCHAR2(30);
118 l_comma VARCHAR2(1) := ',';
119 l_total_recs NUMBER := 0;
120 l_total_attributes NUMBER := 0;
121 l_column_alias VARCHAR2(5);
122 l_master_field VARCHAR2(200);
123 l_child_field VARCHAR2(200);
124 l_and VARCHAR2(3) := 'AND';
125 l_total_child NUMBER := 0;
126 l_index_column VARCHAR2(30);
127 l_index_string VARCHAR2(32767);
128 l_counter NUMBER := 0;
129 l_drop_string VARCHAR2(32767);
130 l_view_exists VARCHAR2(1);
131 l_master_view VARCHAR2(30);
132 l_master_total_records NUMBER;
133 l_master_string VARCHAR2(32767);
134 l_sample VARCHAR2(1) := 'N';
135 l_sample_records NUMBER;
136 l_sample_pct NUMBER := 0;
137 l_master_columns_string VARCHAR2(32767) ;
138 l_master_column VARCHAR2(30);
139 l_master_alias VARCHAR2(30);
140 l_policy VARCHAR2(30);
141 l_return_status VARCHAR2(1);
142 l_policy_exists VARCHAR2(1);
143 l_table_policy_exists VARCHAR2(1);
144 l_no_of_chunks Number;
145 -- l_tmp_string VARCHAR2(32767) ;
146 l_remote_drop_string VARCHAR2(32767);
147 l_truncate_string VARCHAR2(32767);
148 l_remote_truncate_string VARCHAR2(32767);
149
150 cursor c_attributes is
151 SELECT distinct non_variant_value, query_alias_id
152 from AMS_QUERY_COND_DISP_STRUCT_vl
153 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
154 and query_alias_id is not null;
155
156 cursor c_total_attributes is
157 select count(*) from (SELECT distinct non_variant_value, query_alias_id
158 from AMS_QUERY_COND_DISP_STRUCT_vl
159 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
160 and query_alias_id is not null) total_records;
161
162 cursor c_attributes_alias is
163 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.master_source_type_flag,alias2.source_object_pk_field,
164 alias2.line_num
165 from
166 (SELECT distinct non_variant_value, query_alias_id
167 from AMS_QUERY_COND_DISP_STRUCT_vl
168 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
169 and query_alias_id is not null) alias1,
170 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
171 'A'||to_char(rownum) line_num
172 from AMS_QUERY_ALIAS alias,ams_list_src_types types
173 where alias.template_id = p_template_id
174 and alias.object_name = types.source_type_code
175 and types.list_source_type = 'TARGET'
176 order by alias.query_alias_id) alias2
177 where alias1.query_alias_id = alias2.query_alias_id;
178
179 cursor c_where_policy is
180 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.master_source_type_flag,alias2.source_object_pk_field,
181 alias2.line_num
182 from
183 (SELECT distinct non_variant_value, query_alias_id
184 from AMS_QUERY_COND_DISP_STRUCT_vl
185 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
186 and query_alias_id is not null) alias1,
187 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
188 'A'||to_char(rownum) line_num
189 from AMS_QUERY_ALIAS alias,ams_list_src_types types
190 where alias.template_id = p_template_id
191 and alias.object_name = types.source_type_code
192 and types.list_source_type = 'TARGET'
193 order by alias.query_alias_id) alias2
194 where alias1.query_alias_id = alias2.query_alias_id
195 and alias2.source_object_name like 'HZ_%';
196
197 cursor c_tot_attributes_alias is
198 select count(*) from (
199 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.master_source_type_flag,alias2.source_object_pk_field,
200 alias2.line_num
201 from
202 (SELECT distinct non_variant_value, query_alias_id
203 from AMS_QUERY_COND_DISP_STRUCT_vl
204 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
205 and query_alias_id is not null) alias1,
206 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
207 'A'||to_char(rownum) line_num
208 from AMS_QUERY_ALIAS alias,ams_list_src_types types
209 where alias.template_id = p_template_id
210 and alias.object_name = types.source_type_code
211 and types.list_source_type = 'TARGET'
212 order by alias.query_alias_id) alias2
213 where alias1.query_alias_id = alias2.query_alias_id ) alias_count;
214
215 cursor c_Where_clause is
216 select types.master_source_type_flag,
217 decode(master_source_type_flag,'Y','A'||to_char(rownum)||'.'||source_object_pk_field||' = ',
218 'A'||to_char(rownum)||'.'||source_object_pk_field ||'(+)')
219 from AMS_QUERY_ALIAS alias,ams_list_src_types types
220 where alias.template_id = p_template_id
221 and alias.object_name = types.source_type_code
222 and types.list_source_type = 'TARGET'
223 order by alias.query_alias_id;
224
225 cursor c_total_child is
226 select count(*) from
227 (
228 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.master_source_type_flag,alias2.source_object_pk_field,
229 alias2.line_num
230 from
231 (SELECT distinct non_variant_value, query_alias_id
232 from AMS_QUERY_COND_DISP_STRUCT_vl
233 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
234 and query_alias_id is not null) alias1,
235 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
236 'A'||to_char(rownum) line_num
237 from AMS_QUERY_ALIAS alias,ams_list_src_types types
238 where alias.template_id = p_template_id
239 and alias.object_name = types.source_type_code
240 and types.list_source_type = 'TARGET'
241 order by alias.query_alias_id) alias2
242 where alias1.query_alias_id = alias2.query_alias_id
243 ) tab3
244 where master_source_type_flag = 'N';
245
246 cursor c_index_columns is
247 select column_name from sys.all_tab_columns where table_name = l_view_name
248 and column_name not in (
249 select flds.source_column_name
250 from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
251 ams_query_template_all qtemp
252 where alias.template_id = p_template_id
253 and alias.object_name = types.source_type_code
254 and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
255 and types.MASTER_SOURCE_TYPE_FLAG = 'Y'
256 and qtemp.template_id = alias.template_id
257 and qtemp.list_src_type = types.source_type_code);
258
259 cursor c_view_exists is
260 select 'Y' from sys.all_tables where table_name = l_view_name;
261
262 l_synonym_exists varchar2(1);
263 cursor c_synonym_exists is
264 select 'Y' from sys.all_objects
265 where object_name = l_view_name
266 and object_type = 'SYNONYM';
267
268
269 cursor c_master_view is
270 select types.source_object_name
271 from AMS_QUERY_ALIAS alias,ams_list_src_types types
272 where alias.template_id = p_template_id
273 and types.master_source_type_flag = 'Y'
274 and alias.object_name = types.source_type_code;
275
276 cursor c_from_clause is
277 select tab1.query_alias_id,
278 decode(tab1.master_source_type_flag,'Y',
279 '( select * from ( select * from '|| tab1.source_object_name||
280 ' order by dbms_random.value ) where rownum <=
281 (select (count(*) * '|| l_sample_pct ||'/100) from '|| tab1.source_object_name||') )' ,tab1.source_object_name),
282 tab1.master_source_type_flag,tab1.source_object_pk_field,
283 tab1.line_num
284 from
285 (
286 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.master_source_type_flag,alias2.source_object_pk_field,
287 alias2.line_num
288 from
289 (SELECT distinct non_variant_value, query_alias_id
290 from AMS_QUERY_COND_DISP_STRUCT_vl
291 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
292 and query_alias_id is not null) alias1,
293 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
294 'A'||to_char(rownum) line_num
295 from AMS_QUERY_ALIAS alias,ams_list_src_types types
296 where alias.template_id = p_template_id
297 and alias.object_name = types.source_type_code
298 and types.list_source_type = 'TARGET'
299 order by alias.query_alias_id) alias2
300 where alias1.query_alias_id = alias2.query_alias_id ) tab1;
301
302 /*
303 cursor c_master_columns is
304 select flds.source_column_name
305 from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
306 ams_query_template_all qtemp
307 where alias.template_id = p_template_id
308 and alias.object_name = types.source_type_code
309 and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
310 and types.MASTER_SOURCE_TYPE_FLAG = 'Y'
311 and qtemp.template_id = alias.template_id
312 and qtemp.list_src_type = types.source_type_code;
313 */
314
315 -- SOLIN, select primary key only to save table space
316 -- In preview entries, it will join master data source.
317 cursor c_master_columns is
318 --select flds.source_column_name
319 --from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
320 -- ams_query_template_all qtemp
321 --where alias.template_id = p_template_id
322 -- and alias.object_name = types.source_type_code
323 -- and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
324 -- and flds.ENABLED_FLAG = 'Y'
325 -- and flds.USED_IN_LIST_ENTRIES = 'Y'
326 -- and types.MASTER_SOURCE_TYPE_FLAG = 'Y'
327 -- and qtemp.template_id = alias.template_id
328 -- and qtemp.list_src_type = types.source_type_code
329 --Union
330 select typ.SOURCE_OBJECT_PK_FIELD
331 from ams_query_template_all tmp,ams_list_src_types typ
332 where tmp.template_id = p_template_id
333 and tmp.list_src_type = typ.SOURCE_TYPE_CODE;
334
335
336
337 cursor c_const_where is
338 select tab2.source_object_name,tab2.list_source_type_id,tab2.master_source_type_flag,tab2.line_num||'.'
339 from
340 (
341 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.master_source_type_flag,alias2.source_object_pk_field,
342 alias2.line_num, alias2.list_source_type_id
343 from
344 (SELECT distinct non_variant_value, query_alias_id
345 from AMS_QUERY_COND_DISP_STRUCT_vl
346 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
347 and query_alias_id is not null) alias1,
348 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
349 'A'||to_char(rownum) line_num,types.list_source_type_id
350 from AMS_QUERY_ALIAS alias,ams_list_src_types types
351 where alias.template_id = p_template_id
352 and alias.object_name = types.source_type_code
353 and types.list_source_type = 'TARGET'
354 order by alias.query_alias_id) alias2
355 where alias1.query_alias_id = alias2.query_alias_id
356 ) tab2;
357
358 l_master_lstype_id number;
359 l_mst_alias varchar2(30);
360 l_child_lstype_id number;
361 l_chd_alias varchar2(30);
362
363
364 cursor c_const_where_cols is
365 select nvl(assoc.master_source_type_pk_column,types.SOURCE_OBJECT_PK_FIELD ),assoc.sub_source_type_pk_column from
366 ams_list_src_type_assocs assoc , ams_list_src_types types
367 where assoc.master_source_type_id = l_master_lstype_id
368 and assoc.sub_source_type_id = l_child_lstype_id
369 and assoc.master_source_type_id = types.list_source_type_id;
370
371 l_mst_col varchar(60);
372 l_chd_col varchar2(60);
373 x_return_status varchar(1);
374 x_msg_count number;
375 x_msg_data varchar2(2000);
376
377 cursor c_master_pk is
378 select source_object_pk_field from ams_list_src_types where list_source_type_id = p_template_id;
379
380 l_master_pk varchar2(60);
381
382 cursor c_policy_exists is
383 select 'Y' from sys.dba_policies where object_name like 'HZ%';
384
385 cursor c_table_policy_exists is
386 select 'Y' from sys.dba_policies where object_name = l_source_object_name;
387
388 l_tablespace varchar2(1000);
389 l_index_tablespace varchar2(1000);
390 cursor c_tablespace is
391 select tablespace,INDEX_TABLESPACE from fnd_product_installations
392 where application_id = '530';
393
394 l_qal_id number;
395 l_mastcol_alias varchar2(100);
396 l_mastobj_name varchar2(100);
397 cursor c_find_mast_alias is
398 select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,'A'||to_char(rownum) line_num
399 from AMS_QUERY_ALIAS alias, ams_list_src_types types
400 where alias.template_id = p_template_id
401 and alias.object_name = types.source_type_code
402 and types.list_source_type = 'TARGET'
403 order by alias.query_alias_id;
404
405 cursor c_mast_alias is
406 select types.LIST_SOURCE_TYPE_ID, types.source_object_name, types.master_source_type_flag,'A'||to_char(rownum)||'.' line_num
407 from AMS_QUERY_ALIAS alias, ams_list_src_types types
408 where alias.template_id = p_template_id
409 and alias.object_name = types.source_type_code
410 and types.list_source_type = 'TARGET'
411 order by alias.query_alias_id;
412
413
414 l_filter_sql VARCHAR2(32767) ;
415 l_string_params AMS_List_running_total_pvt.sql_string_4k;
416 l_num_params number := 0;
417 TYPE table_char IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
418 l_table_char table_char;
419 l_string VARCHAR2(32767);
420 l_where1 VARCHAR2(100) := ' Where rownum < 1 ';
421 l_where2 VARCHAR2(100) := ' and rownum < 1 ';
422
423 l_apps_schema VARCHAR2(200) ;
424 cursor c_apps is
425 select sys_context( 'userenv', 'current_schema' ) "apps_schema" from dual;
426
427 -- SOLIN, Bug 3696553
428 CURSOR c_get_dblink is
429 select NVL(types.remote_flag, 'N'), database_link
430 from AMS_QUERY_ALIAS alias, ams_list_src_types types
431 where alias.template_id = p_template_id
432 and alias.object_name = types.source_type_code;
433
434 l_remote_flag VARCHAR2(1);
435 l_dblink VARCHAR2(120);
436 l_null varchar2(30) := null;
437 l_create_remote VARCHAR2(4000);
438 l_check_table_string VARCHAR2(500);
439 l_remote_index_string VARCHAR2(4000);
440 l_exist NUMBER;
441 l_number_of_index NUMBER;
442 -- SOLIN, end
443
444 l_application_short_name VARCHAR2(200) ;
445 cursor c_apps_short_name is
446 select application_short_name from fnd_application where application_id = 530;
447
448 l_ams_schema VARCHAR2(200) ;
449 l_fnd_return boolean;
450 l_status_AppInfo varchar2(1);
451 l_industry_AppInfo varchar2(1);
452 l_create_synonym varchar2(200);
453 l_drop_synonym varchar2(200);
454 l_remote_create_synonym varchar2(1000);
455
456 cursor c_get_db_version is
457 select to_number(substr(version, 1, instr(version,'.',1,2)-1))
458 from v$instance;
459
460 l_db_version number;
461 l_sample_size number;
462 --l_sample_pct number;
463
464 l_oders_view_used varchar2(30);
465 l_number_of_days NUMBER;
466
467 begin
468 Ams_Utility_Pvt.Write_Conc_log('+++++++++++++++++++++Start CM program : AMSGMVTP.+++++++++++++++++++++');
469 Ams_Utility_Pvt.Write_Conc_log('p_template_id = '||to_char(p_template_id));
470
471 -- Update the ams_query_template_all table to set mv_available_flag to N
472 -- so that list creation UI can grey out Recalc and Preview button
473 -- while the regeneration process is in progress
474 update ams_query_template_all
475 set MV_AVAILABLE_FLAG = 'N'
476 where TEMPLATE_ID = p_template_id;
477
478 commit;
479
480 open c_apps_short_name;
481 fetch c_apps_short_name into l_application_short_name;
482 close c_apps_short_name;
483 Ams_Utility_Pvt.Write_Conc_log('l_application_short_name = '||l_application_short_name);
484 l_fnd_return := fnd_installation.get_app_info(l_application_short_name,
485 l_status_AppInfo,
486 l_industry_AppInfo ,
487 l_ams_schema);
488 Ams_Utility_Pvt.Write_Conc_log('l_ams_schema = '||l_ams_schema);
489 open c_apps;
490 fetch c_apps into l_apps_schema;
491 close c_apps;
492 Ams_Utility_Pvt.Write_Conc_log('l_apps_schema = '||l_apps_schema);
493
494 open c_tablespace;
495 fetch c_tablespace into l_tablespace,l_index_tablespace;
496 close c_tablespace;
497 Ams_Utility_Pvt.Write_Conc_log('l_tablespace = '||l_tablespace);
498 Ams_Utility_Pvt.Write_Conc_log('l_index_tablespace = '||l_index_tablespace);
499
500 -- View Name
501 -- ---------
502 l_view_name := 'AMS_QT_'||to_char(p_template_id)||'_MV';
503 Ams_Utility_Pvt.Write_Conc_log('l_view_name = '||l_view_name);
504
505 -- SOLIN, Bug 3696553
506 OPEN c_get_dblink;
507 FETCH c_get_dblink INTO l_remote_flag, l_dblink;
508 CLOSE c_get_dblink;
509
510 --Get Database version ----aanjaria
511 OPEN c_get_db_version;
512 FETCH c_get_db_version INTO l_db_version;
513 CLOSE c_get_db_version;
514 Ams_Utility_Pvt.Write_Conc_log('Database version = '||to_char(l_db_version));
515
516 IF l_remote_flag = 'N'
517 THEN
518 -- SOLIN, end
519 l_create_string := ' CREATE TABLE '||l_ams_schema||'.'||l_view_name||' PCTFREE 0 TABLESPACE '||l_tablespace||' STORAGE (INITIAL 16k NEXT 10M PCTINCREASE 0 MAXEXTENTS UNLIMITED ) NOLOGGING ';
520 -- SOLIN, Bug 3696553
521 ELSE
522 l_create_string := ' CREATE TABLE ' ||l_view_name;
523 l_remote_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_view_name||'@'||l_dblink;
524 Ams_Utility_Pvt.Write_Conc_log('l_remote_create_synonym = '||l_remote_create_synonym);
525 l_remote_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
526 Ams_Utility_Pvt.Write_Conc_log('l_remote_drop_string = '||l_remote_drop_string);
527 l_remote_truncate_string := 'TRUNCATE TABLE '||l_view_name;
528 Ams_Utility_Pvt.Write_Conc_log('l_remote_truncate_string = '||l_remote_truncate_string);
529 END IF;
530 -- SOLIN, end
531
532 -- If database version 9.2 or higher then use TABLE COMPRESSION feature --aanjaria
533 IF l_db_version >= 9.2 THEN
534 l_create_string := l_create_string || ' COMPRESS AS SELECT ';
535 l_insert_string := ' INSERT /*+ APPEND */ INTO '||l_view_name||' SELECT ';
536 ELSE
537 l_create_string := l_create_string || ' AS SELECT ';
538 l_insert_string := ' INSERT INTO '||l_view_name||' SELECT ';
539 END IF;
540
541 Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
542 Ams_Utility_Pvt.Write_Conc_log('l_insert_string = '||l_insert_string);
543 l_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_ams_schema||'.'||l_view_name;
544 l_drop_synonym := 'DROP PUBLIC SYNONYM '||l_view_name;
545 Ams_Utility_Pvt.Write_Conc_log('l_create_synonym = '||l_create_synonym);
546 Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
547 open c_master_view;
548 fetch c_master_view into l_master_view;
549 close c_master_view;
550 /*
551 open c_policy_exists;
552 fetch c_policy_exists into l_policy_exists;
553 close c_policy_exists;
554 Ams_Utility_Pvt.Write_Conc_log('l_policy_exists : '||l_policy_exists);
555 */
556 Ams_Utility_Pvt.Write_Conc_log('QT Table cretion started for view : '||l_view_name);
557 -- Get the records in the master data source view
558 -- ----------------------------------------------
559 -- SOLIN, Bug 3696553
560 IF l_remote_flag = 'N'
561 THEN
562 -- SOLIN, end
563 l_master_string := 'begin select count(*) into :l_master_total_records from '||l_master_view||' ;end;';
564 -- SOLIN, Bug 3696553
565 ELSE
566 l_master_string := 'begin select count(*) into :l_master_total_records from '||l_master_view||'@'||l_dblink||' ;end;';
567 END IF;
568 -- SOLIN, end
569 execute immediate l_master_string using OUT l_master_total_records;
570 open c_total_attributes;
571 fetch c_total_attributes into l_total_attributes;
572 close c_total_attributes;
573
574 --Get values from profiles
575 l_sample_size := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_SIZE');
576 -- batoleti Bug# 4684584 the 'AMS_LIST_QT_SAMPLE_PCT' is considered only when taking the sample records.
577 -- in other cases, the sample_pct should be zero.
578 l_sample_pct := 0;
579
580 Ams_Utility_Pvt.Write_Conc_log('Profile Value of AMS_LIST_QT_SAMPLE_SIZE : '|| to_char(l_sample_size));
581 Ams_Utility_Pvt.Write_Conc_log('Profile Value of AMS_LIST_QT_SAMPLE_PCT : '|| to_char(l_sample_pct));
582
583 if l_master_total_records > l_sample_size then
584 l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
585 l_sample := 'Y';
586 l_sample_records := round((l_master_total_records * l_sample_pct)/100);
587 -- l_sample_pct := 30;
588 -- l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
589 -- Ams_Utility_Pvt.Write_Conc_log('Sampling percentage = '||l_sample_pct);
590 -- Ams_Utility_Pvt.Write_Conc_log('Sampling size = '||l_sample_size);
591 else
592 l_sample := 'N';
593 end if;
594 Ams_Utility_Pvt.Write_Conc_log('After getting total number of rows from the master DS : '|| to_char(l_master_total_records));
595
596 -- Get All The Columns
597 -- -------------------
598 Ams_Utility_Pvt.Write_Conc_log('Start: Get All The Columns - c_attributes' );
599 open c_attributes;
600 LOOP
601 fetch c_attributes into l_attribute_name, l_query_alias_id;
602 exit when c_attributes%notfound;
603 l_total_recs := l_total_recs + 1;
604 if l_total_recs = l_total_attributes then
605 l_comma := ' ';
606 end if;
607
608 -- Get All The Columns With Alias
609 -- ------------------------------
610 Ams_Utility_Pvt.Write_Conc_log('Start: Get All The Columns With Alias - c_attributes_alias ' );
611 open c_attributes_alias;
612 loop
613 fetch c_attributes_alias into l_q_alias_id, l_source_object_name,l_master_flag,l_source_pk_field,
614 l_column_alias;
615 exit when c_attributes_alias%notfound;
616 if l_master_flag = 'Y' then
617 l_master_alias := l_column_alias;
618 end if;
619 if l_query_alias_id = l_q_alias_id then
620 exit;
621 end if;
622 end loop;
623 l_select_string := l_select_string||' '||l_column_alias||'.'||l_attribute_name||' '
624 ||l_column_alias||'_'||l_attribute_name||' '||l_comma;
625 close c_attributes_alias;
626 Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string );
627 Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns With Alias - c_attributes_alias ' );
628 END LOOP;
629 close c_attributes;
630 Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns - c_attributes' );
631 l_comma := ',';
632
633 -- (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
634 -- Get All the Columns for MASTER view
635 --*
636 --*
637 if l_master_alias is null then
638 open c_find_mast_alias;
639 loop
640 fetch c_find_mast_alias into l_qal_id,l_mastobj_name, l_master_flag,l_mastcol_alias;
641 exit when c_find_mast_alias%notfound;
642 if l_master_flag = 'Y' then
643 l_master_alias := l_mastcol_alias;
644 exit;
645 end if;
646 end loop;
647 close c_find_mast_alias;
648 end if;
649
650 -- -----------------------------------
651 Ams_Utility_Pvt.Write_Conc_log('Start: Get All the Columns for MASTER view - c_master_columns ' );
652 open c_master_columns;
653 LOOP
654 fetch c_master_columns into l_master_column;
655 Exit when c_master_columns%notfound;
656 if l_master_column is not NULL then
657 l_master_columns_string := l_master_columns_string||
658 ' '||l_comma||' '||l_master_alias||'.'||l_master_column||' '||l_master_column;
659 l_master_column := null;
660 end if;
661 END LOOP;
662 close c_master_columns;
663 Ams_Utility_Pvt.Write_Conc_log('l_master_columns_string = '||l_master_columns_string );
664 Ams_Utility_Pvt.Write_Conc_log('End: Get All the Columns for MASTER view - c_master_columns ' );
665
666 l_select_string := l_select_string||' '||l_master_columns_string;
667 Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string);
668 -- }}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
669 l_total_attributes := 0;
670 l_total_recs := 0;
671 open c_tot_attributes_alias;
672 fetch c_tot_attributes_alias into l_total_attributes;
673 close c_tot_attributes_alias;
674 Ams_Utility_Pvt.Write_Conc_log('l_total_attributes = '||to_char(l_total_attributes));
675
676 -- dbms_output.put_line('l_total_attributes = '||to_char(l_total_attributes));
677
678 -- Construct The From Clause
679 -- -------------------------
680 Ams_Utility_Pvt.Write_Conc_log('Start: Construct The From Clause ');
681 Ams_Utility_Pvt.Write_Conc_log('l_sample = '||l_sample);
682 if l_sample = 'N' then
683 open c_attributes_alias;
684 loop
685 fetch c_attributes_alias into l_q_alias_id, l_source_object_name,l_master_flag,l_source_pk_field,
686 l_column_alias;
687 exit when c_attributes_alias%notfound;
688 l_total_recs := l_total_recs + 1;
689 if l_total_recs = l_total_attributes then
690 l_comma := ' ';
691 end if;
692 l_from_string := l_from_string||' '||l_source_object_name||' '||l_column_alias||' '||l_comma;
693 Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
694
695 Ams_Utility_Pvt.Write_Conc_log('l_mastobj_name = '||l_mastobj_name);
696 Ams_Utility_Pvt.Write_Conc_log('l_master_flag = '||l_master_flag);
697
698 end loop;
699 close c_attributes_alias;
700
701 if l_mastobj_name is not null then
702 Ams_Utility_Pvt.Write_Conc_log('inside the if');
703 l_from_string := l_from_string||', '||l_mastobj_name||' '||l_master_alias||' '||l_comma;
704 Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
705 end if;
706
707 -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
708 Ams_Utility_Pvt.Write_Conc_log('sample (N) l_from_string = '||l_from_string);
709
710
711 end if;
712 if l_sample = 'Y' then
713 open c_from_clause;
714 loop
715 fetch c_from_clause into l_q_alias_id, l_source_object_name,l_master_flag,l_source_pk_field,
716 l_column_alias;
717 exit when c_from_clause%notfound;
718 l_total_recs := l_total_recs + 1;
719 if l_total_recs = l_total_attributes then
720 l_comma := ' ';
721 end if;
722 Ams_Utility_Pvt.Write_Conc_log('l_from_string:'||l_from_string||'----l_source_object_name:'||l_source_object_name||'--l_column_alias'||l_column_alias);
723 l_from_string := l_from_string||' '||l_source_object_name||' '||l_column_alias||' '||l_comma;
724 end loop;
725 close c_from_clause;
726 Ams_Utility_Pvt.Write_Conc_log('sample (Y) l_from_string = '||l_from_string);
727
728 end if;
729 Ams_Utility_Pvt.Write_Conc_log('End: Construct The From Clause ');
730
731 -- dbms_output.put_line('coming after the from clause........ ');
732
733 -- Construct The Where Clause
734 -- --------------------------
735 -- --------------------------------------------------------
736
737 l_number_of_days := FND_PROFILE.VALUE('AMS_ORDER_LOOK_BACK_PRD_DAYS');
738 Ams_Utility_Pvt.Write_Conc_log('Profile value for Orders look back period: '||l_number_of_days);
739 Ams_Utility_Pvt.Write_Conc_log('Start: Construct The Where Clause ');
740 l_total_attributes := 0;
741 open c_tot_attributes_alias;
742 fetch c_tot_attributes_alias into l_total_attributes;
743 close c_tot_attributes_alias;
744 Ams_Utility_Pvt.Write_Conc_log('l_total_attributes = '||to_char(l_total_attributes));
745 if l_total_attributes = 1 AND l_mastobj_name is null then
746 l_where_string := null;
747 end if;
748 if l_total_attributes > 1 or (l_total_attributes = 1 AND l_mastobj_name is not null) then
749 open c_const_where;
750 loop
751 fetch c_const_where into l_oders_view_used,l_master_lstype_id,l_master_flag,l_mst_alias;
752 exit when c_const_where%notfound;
753 if l_master_flag = 'Y' then
754 exit;
755 end if;
756 end loop;
757 close c_const_where;
758 if (l_master_flag = 'N' or l_master_flag is null) then
759 open c_mast_alias;
760 loop
761 fetch c_mast_alias into l_master_lstype_id,l_mastobj_name, l_master_flag,l_mst_alias;
762 exit when c_mast_alias%notfound;
763 if l_master_flag = 'Y' then
764 exit;
765 end if;
766 end loop;
767 close c_mast_alias;
768 end if;
769
770 l_total_recs := 0;
771 open c_total_child;
772 fetch c_total_child into l_total_child;
773 close c_total_child;
774 Ams_Utility_Pvt.Write_Conc_log('l_total_child = '||to_char(l_total_child));
775 If l_total_child = 0 then
776 l_where_string := null;
777 end if;
778 if l_total_child > 0 then
779 open c_const_where;
780 loop
781 fetch c_const_where into l_oders_view_used,l_child_lstype_id,l_master_flag,l_chd_alias;
782 exit when c_const_where%notfound;
783 if l_master_flag = 'N' then
784 l_total_recs := l_total_recs + 1;
785 if l_total_recs = l_total_child then
786 l_and := ' ';
787 end if;
788 open c_const_where_cols;
789 fetch c_const_where_cols into l_mst_col, l_chd_col;
790 close c_const_where_cols;
791 if (l_number_of_days is NOT NULL) OR (l_number_of_days > 0) then
792 if (l_oders_view_used = 'AMS_DS_ORDERS_V') then
793 l_where_string := l_where_string||' '||l_mst_alias||l_mst_col||' = '||l_chd_alias||l_chd_col||'(+) AND '||l_chd_alias||'creation_date > sysdate - '||l_number_of_days||' '||l_and;
794 else
795 l_where_string := l_where_string||' '||l_mst_alias||l_mst_col||' = '||l_chd_alias||l_chd_col||'(+) '||l_and;
796 end if;
797 else
798 l_where_string := l_where_string||' '||l_mst_alias||l_mst_col||' = '||l_chd_alias||l_chd_col||'(+) '||l_and;
799 end if;
800 end if; -- l_master_flag = 'N'
801 if l_total_recs = l_total_child then
802 exit;
803 end if;
804 end loop;
805 close c_const_where;
806
807 Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
808 end if;
809 end if; -- if l_total_attributes > 1 then
810 Ams_Utility_Pvt.Write_Conc_log('End: Construct The Where Clause ');
811 Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
812 l_no_of_chunks := 0;
813 l_no_of_chunks := ceil(length(l_select_string)/2000 );
814 for i in 1 ..l_no_of_chunks
815 loop
816 Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||substrb(l_select_string,(2000*i) - 1999,2000));
817 end loop;
818
819 l_create1_string := l_create_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
820 if l_where_string is null then
821 l_create1_string := l_create1_string||' '||l_where1;
822 else
823 l_create1_string := l_create1_string||' '||l_where2;
824 end if;
825
826 -- Construct the LOV condirion Start --------------------------- *********************************
827 Ams_Utility_Pvt.Write_Conc_log('Start Construction LOV condition. ');
828 AMS_List_running_total_pvt.gen_lov_filter_for_templmv(
829 l_filter_sql,
830 l_string_params,
831 l_num_params,
832 p_template_id
833 ) ;
834
835 Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
836 Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
837 Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
838 if l_string_params.count > 100 then
839 Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
840 Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
841 errbuf:= substr(SQLERRM,1,254);
842 retcode:= 2;
843 return;
844 end if;
845
846 AMS_List_running_total_pvt.gen_constant_filter(
847 l_filter_sql,
848 l_string_params,
849 l_num_params,
850 p_template_id
851 ) ;
852
853 Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
854 Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
855 Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
856 if l_string_params.count > 100 then
857 Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
858 Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
859 errbuf:= substr(SQLERRM,1,254);
860 retcode:= 2;
861 return;
862 end if;
863 begin
864 for i in 1 .. l_string_params.count loop
865 Ams_Utility_Pvt.Write_Conc_log('param '||to_char(i)||' = '|| l_string_params(i));
866 end loop;
867 exception
868 when others then
869 Ams_Utility_Pvt.Write_Conc_log('Exception in loop : '||SQLERRM);
870 end;
871 Ams_Utility_Pvt.Write_Conc_log('End Construction LOV condition. ');
872 -- Construct the LOV condirion End --------------------------- *********************************
873
874 if l_num_params > 0 and l_filter_sql is not NULL then
875 if l_where_string is null then
876 l_where_string := ' WHERE '||l_filter_sql;
877 else
878 l_where_string := l_where_string||' AND '||l_filter_sql;
879 end if;
880 end if;
881 Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
882 Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
883 -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
884 l_create_string := l_create_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
885 l_insert_string := l_insert_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
886 l_no_of_chunks := 0;
887 l_no_of_chunks := ceil(length(l_create_string)/2000 );
888 for i in 1 ..l_no_of_chunks
889 loop
890 Ams_Utility_Pvt.Write_Conc_log('Final l_create_string = '||substrb(l_create_string,(2000*i) - 1999,2000));
891 end loop;
892
893 l_no_of_chunks := ceil(length(l_insert_string)/2000 );
894 Ams_Utility_Pvt.Write_Conc_log('Final l_insert_string chunks= '||l_no_of_chunks);
895 for i in 1 ..l_no_of_chunks
896 loop
897 Ams_Utility_Pvt.Write_Conc_log(substrb(l_insert_string,(2000*i) - 1999,2000));
898 end loop;
899
900 -- SOLIN, bug 3696553
901 IF l_remote_flag = 'N'
902 THEN
903 -- ELSE part is close to the end of the procedure
904 -- SOLIN, end
905 open c_view_exists;
906 fetch c_view_exists into l_view_exists;
907 close c_view_exists;
908 Ams_Utility_Pvt.Write_Conc_log('l_view_exists = '||l_view_exists);
909 if l_view_exists = 'Y' then
910 -- l_truncate_string := 'TRUNCATE TABLE '||l_ams_schema||'.'||l_view_name;
911 l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
912 -- l_drop_string := 'DROP MATERIALIZED VIEW '||l_view_name;
913 -- Ams_Utility_Pvt.Write_Conc_log('local l_truncate_string = '||l_truncate_string);
914 -- execute immediate l_truncate_string;
915 Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
916 execute immediate l_drop_string;
917
918 Ams_Utility_Pvt.Write_Conc_log('Table Droped ');
919 end if;
920 open c_synonym_exists;
921 fetch c_synonym_exists into l_synonym_exists;
922 close c_synonym_exists;
923 if l_synonym_exists = 'Y' then
924 Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
925 execute immediate l_drop_synonym;
926 Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
927 end if;
928 /*
929 if l_policy_exists = 'Y' then
930 Ams_Utility_Pvt.Write_Conc_log('Disable Contant Source Security ');
931 hz_common_pub.disable_cont_source_security;
932 end if;
933 */
934 if l_num_params = 0 or l_num_params is null then
935 execute immediate l_create1_string;
936 Ams_Utility_Pvt.Write_Conc_log('Table created. ');
937 execute immediate l_create_synonym;
938 Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
939 execute immediate l_insert_string;
940 Ams_Utility_Pvt.Write_Conc_log('Insert statement executed. ');
941 end if;
942
943 if l_num_params > 0 and l_filter_sql is not NULL then
944 l_no_of_chunks := 0;
945 l_no_of_chunks := ceil(length(l_create1_string)/2000 );
946 for i in 1 ..l_no_of_chunks
947 loop
948 Ams_Utility_Pvt.Write_Conc_log('Final l_create1_string = '||substrb(l_create1_string,(2000*i) - 1999,2000));
949 end loop;
950 execute immediate l_create1_string;
951 Ams_Utility_Pvt.Write_Conc_log('Table creation with LOV conditions. ');
952 execute immediate l_create_synonym;
953 Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
954 for i in 1 .. 100 loop
955 l_table_char(i) := ' ';
956 end loop;
957 for i in 1 .. l_string_params.count
958 loop
959 l_table_char(i) := l_string_params(i);
960 Ams_Utility_Pvt.Write_Conc_log('l_table_char(i) '||to_char(i)||' = '|| l_table_char(i));
961 end loop;
962 l_string := 'DECLARE ' ||
963 'l_string1 varchar2(10000) ; ' ||
964 'begin ' ||
965 ' l_string1 := :1 || ' || ' :2 || ' || ' :3 || ' || ' :4 || ' ||
966 ' :5 || ' || ' :6 || ' || ' :7 || ' || ' :8 || ' ||
967 ' :9 || ' || ' :10 || ' || ' :11 || ' || ' :12 || ' ||
968 ' :13 || ' || ' :14 || ' || ' :15 || ' || ' :16 || ' ||
969 ' :17 || ' || ' :18 || ' || ' :19 || ' || ' :20 || ' ||
970 ' :21 || ' || ' :22 || ' || ' :23 || ' || ' :24 || ' ||
971 ' :25 || ' || ' :26 || ' || ' :27 || ' || ' :28 || ' ||
972 ' :29 || ' || ' :30 || ' || ' :31 || ' || ' :32 || ' ||
973 ' :33 || ' || ' :34 || ' || ' :35 || ' || ' :36 || ' ||
974 ' :37 || ' || ' :38 || ' || ' :39 || ' || ' :40 || ' ||
975 ' :41 || ' || ' :42 || ' || ' :43 || ' || ' :44 || ' ||
976 ' :45 || ' || ' :46 || ' || ' :47 || ' || ' :48 || ' ||
977 ' :49 || ' || ' :50 || ' || ' :51 || ' || ' :52 || ' ||
978 ' :53 || ' || ' :54 || ' || ' :55 || ' || ' :56 || ' ||
979 ' :57 || ' || ' :58 || ' || ' :59 || ' || ' :60 || ' ||
980 ' :61 || ' || ' :62 || ' || ' :63 || ' || ' :64 || ' ||
981 ' :65 || ' || ' :66 || ' || ' :67 || ' || ' :68 || ' ||
982 ' :69 || ' || ' :70 || ' || ' :71 || ' || ' :72 || ' ||
983 ' :73 || ' || ' :74 || ' || ' :75 || ' || ' :76 || ' ||
984 ' :77 || ' || ' :78 || ' || ' :79 || ' || ' :80 || ' ||
985 ' :81 || ' || ' :82 || ' || ' :83 || ' || ' :84 || ' ||
986 ' :85 || ' || ' :86 || ' || ' :87 || ' || ' :88 || ' ||
987 ' :89 || ' || ' :90 || ' || ' :91 || ' || ' :92 || ' ||
988 ' :93 || ' || ' :94 || ' || ' :95 || ' || ' :96 || ' ||
989 ' :97 || ' || ' :98 || ' || ' :99 || ' || ' :100 ; ' ||
990 l_insert_string||
991 '; exception when others then Ams_Utility_Pvt.Write_Conc_log(SQLERRM); end; ' ;
992 l_no_of_chunks := 0;
993 l_no_of_chunks := ceil(length(l_string)/2000 );
994 for i in 1 ..l_no_of_chunks
995 loop
996 Ams_Utility_Pvt.Write_Conc_log('Final l_string = '||substrb(l_string,(2000*i) - 1999,2000));
997 end loop;
998 for i in 1 .. l_table_char.count
999 loop
1000 Ams_Utility_Pvt.Write_Conc_log('l_table_char '||to_char(i)||' = '||l_table_char(i));
1001 end loop;
1002 execute immediate l_string
1003 using l_table_char(1), l_table_char(2), l_table_char(3), l_table_char(4),
1004 l_table_char(5), l_table_char(6), l_table_char(7), l_table_char(8),
1005 l_table_char(9), l_table_char(10), l_table_char(11), l_table_char(12),
1006 l_table_char(13), l_table_char(14), l_table_char(15), l_table_char(16),
1007 l_table_char(17), l_table_char(18), l_table_char(19), l_table_char(20),
1008 l_table_char(21), l_table_char(22), l_table_char(23), l_table_char(24),
1009 l_table_char(25), l_table_char(26), l_table_char(27), l_table_char(28),
1010 l_table_char(29), l_table_char(30), l_table_char(31), l_table_char(32),
1011 l_table_char(33), l_table_char(34), l_table_char(35), l_table_char(36),
1012 l_table_char(37), l_table_char(38), l_table_char(39), l_table_char(40),
1013 l_table_char(41), l_table_char(42), l_table_char(43), l_table_char(44),
1014 l_table_char(45), l_table_char(46), l_table_char(47), l_table_char(48),
1015 l_table_char(49), l_table_char(50),
1016 l_table_char(51), l_table_char(52), l_table_char(53), l_table_char(54),
1017 l_table_char(55), l_table_char(56), l_table_char(57), l_table_char(58),
1018 l_table_char(59), l_table_char(60), l_table_char(61), l_table_char(62),
1019 l_table_char(63), l_table_char(64), l_table_char(65), l_table_char(66),
1020 l_table_char(67), l_table_char(68), l_table_char(69), l_table_char(70),
1021 l_table_char(71), l_table_char(72), l_table_char(73), l_table_char(74),
1022 l_table_char(75), l_table_char(76), l_table_char(77), l_table_char(78),
1023 l_table_char(79), l_table_char(80), l_table_char(81), l_table_char(82),
1024 l_table_char(83), l_table_char(84), l_table_char(85), l_table_char(86),
1025 l_table_char(87), l_table_char(88), l_table_char(89), l_table_char(90),
1026 l_table_char(91), l_table_char(92), l_table_char(93), l_table_char(94),
1027 l_table_char(95), l_table_char(96), l_table_char(97), l_table_char(98),
1028 l_table_char(79), l_table_char(100);
1029 commit;
1030 end if;
1031
1032 Ams_Utility_Pvt.Write_Conc_log('QT Table Created ');
1033 /*
1034 if l_policy_exists = 'Y' then
1035 hz_common_pub.enable_cont_source_security;
1036 Ams_Utility_Pvt.Write_Conc_log('Enable Contant Source Security ');
1037 end if;
1038 */
1039 -- Update the ams_query_template_all table
1040 update ams_query_template_all
1041 set mv_name = l_view_name,
1042 MV_AVAILABLE_FLAG = 'Y',
1043 SAMPLE_PCT = l_sample_pct,
1044 SAMPLE_PCT_RECORDS = l_sample_records,
1045 MASTER_DS_REC_NUMBERS = l_master_total_records,
1046 RECALC_TABLE_STATUS = 'AVAILABLE'
1047 where TEMPLATE_ID = p_TEMPLATE_ID;
1048
1049 Ams_Utility_Pvt.Write_Conc_log('QT Table creation Finish. ');
1050
1051 commit;
1052
1053 Ams_Utility_Pvt.Write_Conc_log('Start Table Analyze .');
1054 Ams_Utility_Pvt.Write_Conc_log(' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ');
1055 execute immediate ' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ';
1056 Ams_Utility_Pvt.Write_Conc_log('Finish Table Analyze .');
1057
1058 -- Bitmap Index Creation
1059 -- ---------------------
1060 Ams_Utility_Pvt.Write_Conc_log('Start: Bitmap Index Creation ');
1061
1062 open c_index_columns;
1063 loop
1064 fetch c_index_columns into l_index_column;
1065 exit when c_index_columns%notfound;
1066 l_counter := l_counter + 1;
1067 l_index_string := 'CREATE BITMAP INDEX '||l_view_name||'_N_'||to_char(l_counter)||' ON '||l_view_name||'('||l_index_column||')';
1068 Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1069 execute immediate l_index_string;
1070 Ams_Utility_Pvt.Write_Conc_log('Index Created ');
1071 Ams_Utility_Pvt.Write_Conc_log('Start Index Analyze .');
1072 Ams_Utility_Pvt.Write_Conc_log(' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ');
1073 execute immediate ' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ';
1074 Ams_Utility_Pvt.Write_Conc_log('Finish Index Analyze .');
1075 end loop;
1076 close c_index_columns;
1077
1078 Ams_Utility_Pvt.Write_Conc_log('End: Bitmap Index Creation ');
1079
1080 commit;
1081 retcode:= 0;
1082 -- SOLIN, Bug 3696553
1083 -- IF part is very up above
1084 ELSE
1085 l_check_table_string := 'begin select 1 into :l_exist from sys.all_tables'||'@'||l_dblink||' where table_name = '''||l_view_name||''' ;end;';
1086 Ams_Utility_Pvt.Write_Conc_log('l_check_table_string = '||l_check_table_string);
1087
1088 BEGIN
1089 execute immediate l_check_table_string using OUT l_exist;
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 l_exist := 0;
1093 END;
1094
1095 IF l_exist = 1
1096 THEN
1097 Ams_Utility_Pvt.Write_Conc_log('remote l_remote_truncate_string = '||l_remote_truncate_string);
1098 l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_remote_truncate_string||'''); END;';
1099 Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1100 execute immediate l_create_remote;
1101
1102 l_drop_string := 'DROP TABLE '||l_view_name;
1103 Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1104 l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_drop_string ||'''); END;';
1105 Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1106 execute immediate l_create_remote;
1107 END IF;
1108
1109 Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1110 Ams_Utility_Pvt.Write_Conc_log('remote l_create1_string = '||l_create1_string);
1111 Ams_Utility_Pvt.Write_Conc_log('remote l_insert_string = '||l_insert_string);
1112 l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_create1_string ||'''); END;';
1113 Ams_Utility_Pvt.Write_Conc_log('create command = '||l_create_remote);
1114 execute immediate l_create_remote;
1115
1116 execute immediate
1117 'BEGIN
1118 AMS_Remote_ListGen_PKG.remote_list_gen'||'@'||l_dblink||'(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12)'||';'||
1119 ' END;'
1120 using '1',
1121 l_null,
1122 'T',
1123 l_null,
1124 OUT x_return_status,
1125 OUT x_msg_count,
1126 OUT x_msg_data,
1127 TO_NUMBER(NULL), --g_list_header_id,
1128 --l_create1_string,
1129 l_insert_string,
1130 l_null,
1131 OUT l_total_recs,
1132 'EXECUTE_STRING';
1133
1134 Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1135
1136 -- Update the ams_query_template_all table
1137 update ams_query_template_all
1138 set mv_name = l_view_name,
1139 MV_AVAILABLE_FLAG = 'Y',
1140 SAMPLE_PCT = l_sample_pct,
1141 SAMPLE_PCT_RECORDS = l_sample_records,
1142 MASTER_DS_REC_NUMBERS = l_master_total_records,
1143 RECALC_TABLE_STATUS = 'AVAILABLE'
1144 where TEMPLATE_ID = p_TEMPLATE_ID;
1145 -- SOLIN, bug 4103157
1146 l_synonym_exists := NULL;
1147 -- SOLIN, end
1148 open c_synonym_exists;
1149 fetch c_synonym_exists into l_synonym_exists;
1150 close c_synonym_exists;
1151 if l_synonym_exists = 'Y' then
1152 Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
1153 execute immediate l_drop_synonym;
1154 Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
1155 end if;
1156 execute immediate l_remote_create_synonym;
1157 Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
1158
1159 -- create index for remote table?
1160 l_remote_index_string := 'begin select max(COLUMN_ID) into :l_number_of_index from sys.all_tab_columns'||'@'||l_dblink||' where table_name = '''||l_view_name||''' and column_name like ''A%'';end;';
1161 Ams_Utility_Pvt.Write_Conc_log('l_remote_index_string = '||l_remote_index_string);
1162 BEGIN
1163 execute immediate l_remote_index_string using OUT l_number_of_index;
1164 Ams_Utility_Pvt.Write_Conc_log('number of index = '||l_number_of_index);
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 l_number_of_index := 0;
1168 END;
1169 l_counter := 1;
1170 WHILE l_counter <= l_number_of_index
1171 LOOP
1172
1173 l_remote_index_string := 'BEGIN select column_name into :l_index_column from sys.all_tab_columns'||'@'||l_dblink||' where table_name = '''||l_view_name||''' and column_name like ''A%'' and column_id = '||l_counter||'; END;';
1174 Ams_Utility_Pvt.Write_Conc_log('get column name string = '||l_remote_index_string);
1175 BEGIN
1176 execute immediate l_remote_index_string using OUT l_index_column;
1177 Ams_Utility_Pvt.Write_Conc_log('index column = '||l_index_column);
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 NULL;
1181 END;
1182 l_index_string := 'BEGIN dbms_utility.exec_ddl_statement'||'@'
1183 ||l_dblink||'(''CREATE BITMAP INDEX '||l_view_name||'_N_'
1184 ||to_char(l_counter)||' ON '||l_view_name||'('||l_index_column
1185 ||')'||'''); END;';
1186 Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1187
1188 execute immediate l_index_string;
1189
1190 Ams_Utility_Pvt.Write_Conc_log('remote Index Created ');
1191 l_counter := l_counter + 1;
1192 end loop;
1193 END IF;
1194 -- SOLIN, END
1195 Ams_Utility_Pvt.Write_Conc_log('+++++++++++++++++++++End CM program : AMSGMVTP.+++++++++++++++++++++');
1196
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 -- hz_common_pub.enable_cont_source_security;
1200 Ams_Utility_Pvt.Write_Conc_log('Exception in generate_mv_for_template : '||SQLERRM);
1201 errbuf:= substr(SQLERRM,1,254);
1202 retcode:= 2;
1203
1204 update ams_query_template_all
1205 set RECALC_TABLE_STATUS = 'FAILED'
1206 where TEMPLATE_ID = p_TEMPLATE_ID;
1207
1208 commit;
1209
1210 -- SOLIN, BUG 3736770
1211 l_view_exists := NULL;
1212 open c_view_exists;
1213 fetch c_view_exists into l_view_exists;
1214 close c_view_exists;
1215 Ams_Utility_Pvt.Write_Conc_log('l_view_exists = '||l_view_exists);
1216 if l_view_exists = 'Y' then
1217 IF l_remote_flag = 'N'
1218 THEN
1219 l_truncate_string := 'TRUNCATE TABLE '||l_ams_schema||'.'||l_view_name;
1220 l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
1221 Ams_Utility_Pvt.Write_Conc_log('local l_truncate_string = '||l_truncate_string);
1225 ELSE
1222 execute immediate l_truncate_string;
1223 Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
1224 execute immediate l_drop_string;
1226 Ams_Utility_Pvt.Write_Conc_log('remote l_remote_truncate_string = '||l_remote_truncate_string);
1227 l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_remote_truncate_string||'''); END;';
1228 Ams_Utility_Pvt.Write_Conc_log('truncate command = '||l_create_remote);
1229 execute immediate l_create_remote;
1230
1231 l_drop_string := 'DROP TABLE '||l_view_name;
1232 Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1233 l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_drop_string ||'''); END;';
1234 Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1235 execute immediate l_create_remote;
1236 END IF;
1237
1238 Ams_Utility_Pvt.Write_Conc_log('Table Droped in exception ');
1239 Ams_Utility_Pvt.Write_Conc_log('This query template is returning lot of data , please modify the query. ');
1240 Ams_Utility_Pvt.Write_Conc_log('To restruct the size use LOV filter conditions.');
1241 end if;
1242 -- SOLIN, end
1243 raise;
1244 End generate_mv_for_template;
1245 -- -------------------------------
1246
1247
1248 PROCEDURE calc_tot_for_all_templates (
1249 Errbuf OUT NOCOPY VARCHAR2,
1250 Retcode OUT NOCOPY VARCHAR2
1251 ) IS
1252 l_template_id number;
1253 l_mv_name varchar2(30);
1254 l_delete_string varchar2(2000);
1255 l_Errbuf varchar2(2000);
1256 l_Retcode varchar2(1);
1257
1258 cursor c_all_templates is
1259 select template_id, mv_name from ams_query_template_all where mv_name is not null;
1260 begin
1261
1262 open c_all_templates;
1263 loop
1264 fetch c_all_templates into l_template_id, l_mv_name;
1265 exit when c_all_templates%notfound;
1266
1267 l_delete_string := ' DROP MATERIALIZED VIEW '||l_mv_name;
1268 execute immediate l_delete_string;
1269
1270 update ams_query_template_all
1271 set MV_AVAILABLE_FLAG = 'N'
1272 where TEMPLATE_ID = L_TEMPLATE_ID;
1273
1274
1275 Calculate_running_totals (
1276 L_Errbuf ,
1277 L_Retcode,
1278 l_template_id);
1279
1280 end loop;
1281 close c_all_templates;
1282
1283 retcode:= 0;
1284 EXCEPTION
1285 WHEN OTHERS THEN
1286 errbuf:= substr(SQLERRM,1,254);
1287 retcode:= 2;
1288 raise;
1289 end calc_tot_for_all_templates;
1290 -- ---------------------------
1291
1292 PROCEDURE process_query (
1293 p_sql_string IN sql_string_4k,
1294 p_total_parameters IN t_number,
1295 p_string_parameters IN sql_string_4k,
1296 p_template_id IN NUMBER,
1297 p_parameters IN sql_string_4k,
1298 p_parameters_value IN t_number,
1299 p_sql_results OUT NOCOPY t_number
1300 ) IS
1301
1302 l_total number;
1303 l_sql_count number;
1304 l_i number := 1; -- 0;
1305 l_create_string VARCHAR2(32767);
1306 l_total_param number;
1307 l_param_start number := 1; -- 0;
1308 l_bind_string VARCHAR2(32767);
1309 l_total_bind number := 100;
1310 l_bind_avail number ;
1311 l_sample_pct number;
1312 l_master_ds_rec_numbers number;
1313 l_sample_pct_recrods number;
1314
1315 l_return_status varchar2(1);
1316
1317 cursor c_master_ds_rec_numbers is
1318 select nvl(SAMPLE_PCT,0),MASTER_DS_REC_NUMBERS,SAMPLE_PCT_RECORDS from ams_query_template_all
1319 where TEMPLATE_ID = P_TEMPLATE_ID;
1320
1321
1322 -- SOLIN, Bug 3696553
1323 CURSOR c_get_dblink is
1324 select NVL(types.remote_flag, 'N'), database_link
1325 from AMS_QUERY_ALIAS alias, ams_list_src_types types
1326 where alias.template_id = p_template_id
1327 and alias.object_name = types.source_type_code;
1328
1329 l_remote_flag VARCHAR2(1);
1330 l_dblink VARCHAR2(120);
1331 l_null varchar2(30) := null;
1332 l_msg_count number;
1333 l_msg_data varchar2(2000);
1334 -- SOLIN, end
1335
1336 cur BINARY_INTEGER;
1337 l_exec BINARY_INTEGER;
1338 l_count number; --DBMS_SQL.NUMBER_TABLE;
1339 idx varchar2(6);
1340
1341 begin
1342 delete from ams_act_logs where ARC_ACT_LOG_USED_BY =
1343 'RECL' and ACT_LOG_USED_BY_ID = p_template_id;
1344 -- dbms_output.put_line('IN AMS_List_running_total_pvt----------------------- ');
1345 AMS_Utility_PVT.Create_Log (
1346 x_return_status => l_return_status,
1347 p_arc_log_used_by => 'RECL',
1348 p_log_used_by_id => p_template_id,
1349 p_msg_data => 'Start : Recalculate Process at '||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'),
1350 p_msg_type => 'DEBUG');
1351 open c_master_ds_rec_numbers;
1352 fetch c_master_ds_rec_numbers into l_sample_pct, l_master_ds_rec_numbers,l_sample_pct_recrods;
1353 close c_master_ds_rec_numbers;
1354
1355 -- Get the total # of sqls
1356 l_sql_count := p_sql_string.count;
1360 p_arc_log_used_by => 'RECL',
1357 -- dbms_output.put_line('l_sql_count = '||to_char(l_sql_count));
1358 AMS_Utility_PVT.Create_Log (
1359 x_return_status => l_return_status,
1361 p_log_used_by_id => p_template_id,
1362 p_msg_data => 'l_sql_count := '||l_sql_count,
1363 p_msg_type => 'DEBUG');
1364 LOOP
1365 if l_i > l_sql_count then
1366 exit;
1367 end if;
1368
1369 l_count := 0;
1370 -- Get the total # of attributes for each sql
1371 l_total_param := p_total_parameters(l_i);
1372 -- dbms_output.put_line('l_total_param = '||to_char(l_total_param));
1373 AMS_Utility_PVT.Create_Log (
1374 x_return_status => l_return_status,
1375 p_arc_log_used_by => 'RECL',
1376 p_log_used_by_id => p_template_id,
1377 p_msg_data => 'l_total_param := '||l_total_param||' l_i:'||l_i,
1378 p_msg_type => 'DEBUG');
1379
1380 cur := DBMS_SQL.OPEN_CURSOR;
1381
1382 AMS_Utility_PVT.Create_Log (
1383 x_return_status => l_return_status,
1384 p_arc_log_used_by => 'RECL',
1385 p_log_used_by_id => p_template_id,
1386 p_msg_data => substr('SQL_QUERY:= '||p_sql_string(l_i),1,255),
1387 p_msg_type => 'DEBUG');
1388
1389 DBMS_SQL.PARSE (cur, p_sql_string(l_i), DBMS_SQL.NATIVE);
1390 DBMS_SQL.DEFINE_COLUMN (cur, 1, l_count);
1391
1392 FOR i IN 1..l_total_param
1393 LOOP
1394 --bind variables
1395 DBMS_SQL.BIND_VARIABLE (cur, ''||i||'', p_string_parameters(l_param_start));
1396 l_param_start:=l_param_start+1;
1397 END LOOP;
1398
1399 l_exec := DBMS_SQL.EXECUTE(cur);
1400
1401 IF DBMS_SQL.FETCH_ROWS (cur) > 0 THEN
1402 DBMS_SQL.COLUMN_VALUE (cur, 1, l_count);
1403 ELSE
1404 l_count := 0;
1405 END IF;
1406
1407 DBMS_SQL.CLOSE_CURSOR (cur);
1408
1409 -- dbms_output.put_line('Total from query -- l_total = '|| to_char(l_total));
1410 AMS_Utility_PVT.Create_Log (
1411 x_return_status => l_return_status,
1412 p_arc_log_used_by => 'RECL',
1413 p_log_used_by_id => p_template_id,
1414 p_msg_data => 'l_total := '||l_count,
1415 p_msg_type => 'DEBUG');
1416 if l_sample_pct = 0 then
1417 p_sql_results(l_i) := l_count;
1418 -- dbms_output.put_line('p_sql_results(l_i) = '|| p_sql_results(l_i));
1419 AMS_Utility_PVT.Create_Log (
1420 x_return_status => l_return_status,
1421 p_arc_log_used_by => 'RECL',
1422 p_log_used_by_id => p_template_id,
1423 p_msg_data => 'Result going bcak -- p_sql_results(l_i) := '||p_sql_results(l_i),
1424 p_msg_type => 'DEBUG');
1425 end if;
1426 if l_sample_pct > 0 then
1427 p_sql_results(l_i) := round((l_count / l_sample_pct_recrods) * l_master_ds_rec_numbers) ;
1428 AMS_Utility_PVT.Create_Log (
1429 x_return_status => l_return_status,
1430 p_arc_log_used_by => 'RECL',
1431 p_log_used_by_id => p_template_id,
1432 p_msg_data => 'Results with sampling p_sql_results(l_i) := '||p_sql_results(l_i)
1433 ||'-'||to_char(l_sample_pct_recrods)||'-'||to_char(l_master_ds_rec_numbers),
1434 p_msg_type => 'DEBUG');
1435 end if;
1436
1437 if l_total_param = 0 then
1438 l_param_start := 0;
1439 end if;
1440 l_i := l_i +1;
1441 l_total_param := null;
1442 -- l_param_start := l_param_start + 1;
1443 l_total := null;
1444 l_create_string := null;
1445 /*
1446 AMS_Utility_PVT.Create_Log (
1447 x_return_status => l_return_status,
1448 p_arc_log_used_by => 'RECL',
1449 p_log_used_by_id => p_template_id,
1450 p_msg_data => 'Last line l_param_start := '||l_param_start,
1451 p_msg_type => 'DEBUG');
1452 */
1453 -- dbms_output.put_line('l_param_start = '||to_char(l_param_start));
1454 END LOOP;
1455 AMS_Utility_PVT.Create_Log (
1456 x_return_status => l_return_status,
1457 p_arc_log_used_by => 'RECL',
1458 p_log_used_by_id => p_template_id,
1459 p_msg_data => 'End : Recalculate process ',
1460 p_msg_type => 'DEBUG');
1461 -- dbms_output.put_line('IN AMS_List_running_total_pvt--------------------------------------- ');
1462
1463 EXCEPTION
1464 WHEN others THEN
1465 AMS_Utility_PVT.Create_Log (
1466 x_return_status => l_return_status,
1467 p_arc_log_used_by => 'RECL',
1468 p_log_used_by_id => p_template_id,
1469 p_msg_data => 'Error in process_query for recalculate '|| SQLERRM||' '||SQLCODE,
1470 p_msg_type => 'DEBUG');
1471 raise;
1472 end process_query;
1473
1474 -- ---------------------------------------------------------
1475
1476
1477 PROCEDURE gen_lov_filter_for_templmv (
1478 x_filter_sql OUT NOCOPY VARCHAR2,
1479 x_string_params OUT NOCOPY sql_string_4k,
1480 x_num_params OUT NOCOPY NUMBER,
1481 p_template_id IN NUMBER
1485 select distinct cond.query_condition_id cond_id
1482 ) IS
1483
1484 cursor c_qualify_conds is
1486 from AMS_QUERY_CONDITION cond, AMS_QUERY_COND_DISP_STRUCT_all struct,
1487 AMS_COND_STRUCT_RESTRICT_VALUE res_values
1488 where cond.template_id = p_template_id
1489 and cond.value1_type = 'LOV'
1490 and struct.QUERY_CONDITION_ID = cond.query_condition_id
1491 and struct.QUERY_COND_DISP_STRUCT_ID = res_values.query_cond_disp_struct_id(+)
1492 and struct.token_type= 'OPERATOR'
1493 and cond.mandatory_flag = 'Y'
1494 UNION
1495 select distinct cond.query_condition_id cond_id
1496 from AMS_QUERY_CONDITION cond, AMS_QUERY_COND_DISP_STRUCT_all struct,
1497 AMS_COND_STRUCT_RESTRICT_VALUE res_values
1498 where cond.template_id = p_template_id
1499 and cond.value1_type = 'LOV'
1500 and struct.QUERY_CONDITION_ID = cond.query_condition_id
1501 and struct.QUERY_COND_DISP_STRUCT_ID = res_values.query_cond_disp_struct_id
1502 and struct.token_type= 'OPERATOR'
1503 and cond.mandatory_flag = 'Y'
1504 and ( ( upper(res_values.code) in ('IS', 'IN', 'LIKE') and upper(res_values.code) not in ('IS NOT', 'NOT IN', 'NOT LIKE'))
1505 or ( upper(res_values.code) in ('IS NOT', 'NOT IN', 'NOT LIKE') and upper(res_values.code) not in ('IS', 'IN', 'LIKE'))
1506 )
1507 and upper(res_values.code) not in ('>', '>=', 'BETWEEN', '<', '<=');
1508
1509 cursor c_left_oprand (p_cond_id NUMBER) is
1510 select struct.non_variant_value attr_name, source_object_name table_name,src_types.list_source_type_id
1511 -- || alias.ALIAS_SEQ table_name
1512 from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_QUERY_ALIAS alias, ams_list_src_types src_types
1513 where struct.query_condition_id = p_cond_id
1514 and struct.token_type = 'ATTRIBUTE'
1515 and alias.query_alias_id = struct.query_alias_id
1516 and alias.OBJECT_NAME = src_types.SOURCE_TYPE_CODE;
1517 cursor c_operator (p_cond_id NUMBER) is
1518 select upper(res_values.code) operator
1519 from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_COND_STRUCT_RESTRICT_VALUE res_values
1520 where struct.query_condition_id = p_cond_id and
1521 struct.token_type = 'OPERATOR'
1522 and struct.QUERY_COND_DISP_STRUCT_ID = res_values.query_cond_disp_struct_id;
1523
1524 cursor c_lov_values (p_cond_id NUMBER) is
1525 select upper(res_values.code) lov_value
1526 from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_COND_STRUCT_RESTRICT_VALUE res_values
1527 where struct.query_condition_id = p_cond_id and
1528 struct.token_type = 'VALUE1'
1529 and struct.QUERY_COND_DISP_STRUCT_ID = res_values.query_cond_disp_struct_id;
1530
1531
1532 cursor c_lov_sql (l_list_source_type_id NUMBER,l_source_field varchar2) is
1533 select qu.QUERY --sql_string is obsolete bug 4604653
1534 from ams_list_src_fields flds,ams_attb_lov_b lovb, ams_list_queries_all qu
1535 where flds.LIST_SOURCE_TYPE_ID = l_list_source_type_id
1536 and flds.SOURCE_COLUMN_NAME = l_source_field
1537 and flds.attb_lov_id = lovb.attb_lov_id
1538 and lovb.CREATION_TYPE = 'SQL'
1539 and qu.ACT_LIST_QUERY_USED_BY_ID = lovb.attb_lov_id
1540 and qu.ARC_ACT_LIST_QUERY_USED_BY = 'LOV' ;
1541
1542 l_lov_sql varchar2(32767);
1543 l_final_lov_sql varchar2(32767);
1544 l_qa_id number;
1545 l_object_name varchar2(60);
1546 l_object_alias varchar2(30);
1547
1548 cursor c_lov_type (l_list_source_type_id NUMBER,l_source_field varchar2) is
1549 select lovb.CREATION_TYPE
1550 from ams_list_src_fields flds ,ams_attb_lov_b lovb
1551 where flds.LIST_SOURCE_TYPE_ID = l_list_source_type_id
1552 and flds.SOURCE_COLUMN_NAME = l_source_field
1553 and flds.attb_lov_id = lovb.attb_lov_id;
1554 l_lov_type varchar2(60);
1555
1556
1557 cursor c_lov_user (l_list_source_type_id NUMBER,l_source_field varchar2) is
1558 select valb.value_code
1559 from ams_list_src_fields flds ,ams_attb_lov_b lovb, ams_attb_lov_values_b valb
1560 where flds.LIST_SOURCE_TYPE_ID = l_list_source_type_id
1561 and flds.SOURCE_COLUMN_NAME = l_source_field
1562 and flds.attb_lov_id = lovb.attb_lov_id
1563 and lovb.CREATION_TYPE = 'USER'
1564 and lovb.attb_lov_id = valb.attb_lov_id;
1565 l_lov_code varchar2(60);
1566 l_numb number := 1;
1567 cursor c_alias is
1568 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.line_num
1569 from
1570 (SELECT distinct non_variant_value, query_alias_id
1571 from AMS_QUERY_COND_DISP_STRUCT_vl
1572 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
1573 and query_alias_id is not null) alias1,
1574 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
1575 'A'||to_char(rownum) line_num
1576 from AMS_QUERY_ALIAS alias,ams_list_src_types types
1577 where alias.template_id = p_template_id
1578 and alias.object_name = types.source_type_code
1579 order by alias.query_alias_id) alias2
1580 where alias1.query_alias_id = alias2.query_alias_id;
1581
1582 l_operator VARCHAR2(255);
1583 l_position number;
1584 l_left_operand_rec c_left_oprand%rowtype;
1585 l_left_operand VARCHAR2(2000);
1586 l_cond_count number := 0;
1587 l_value_count number := 0;
1588
1589 l_param_count number := 0;
1590 l_cond_conn varchar2(20);
1591 l_or varchar2(30) := ' OR ';
1592 l_test varchar2(100);
1593 l_count number := 0;
1594 l_code_tbl JTF_VARCHAR2_TABLE_100;
1595 l_meaning_tbl JTF_VARCHAR2_TABLE_100;
1596 l_param_remove_count number := 0;
1597 l_p_count number := 0;
1601 begin
1598 l_tot_count number := 0;
1599 l_tot_loop number := 0;
1600
1602
1603 Ams_Utility_Pvt.Write_Conc_log('start gen_lov_filter_for_templmv');
1604 for l_qualify_cond_rec IN c_qualify_conds
1605 loop
1606 begin
1607 Ams_Utility_Pvt.Write_Conc_log('l_cond_count = '||l_cond_count);
1608 if l_cond_count > 0 then
1609 -- x_filter_sql := x_filter_sql || 'and (';
1610 x_filter_sql := x_filter_sql || 'OR (';
1611 Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1612 else
1613 x_filter_sql := x_filter_sql || '(';
1614 Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1615 end if;
1616 l_cond_count := l_cond_count + 1;
1617
1618 l_value_count := 0;
1619
1620 open c_left_oprand (l_qualify_cond_rec.cond_id);
1621 fetch c_left_oprand into l_left_operand_rec;
1622 close c_left_oprand;
1623 Ams_Utility_Pvt.Write_Conc_log('After c_left_oprand list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1624
1625 l_qa_id := null;
1626 l_object_name := null;
1627 l_object_alias := null;
1628
1629 open c_alias;
1630 Loop
1631 fetch c_alias into l_qa_id,l_object_name,l_object_alias;
1632 exit when c_alias%notfound;
1633 if l_object_name = l_left_operand_rec.table_name then
1634 exit;
1635 end if;
1636 End loop;
1637 close c_alias;
1638
1639 l_left_operand := ' UPPER('||l_object_alias || '.' || l_left_operand_rec.attr_name||')';
1640 Ams_Utility_Pvt.Write_Conc_log('l_left_operand = '||l_left_operand);
1641 -- l_left_operand := l_left_operand_rec.table_name || '.' || l_left_operand_rec.attr_name;
1642 open c_operator (l_qualify_cond_rec.cond_id);
1643 loop
1644 fetch c_operator into l_operator;
1645 exit when c_operator%notfound;
1646 Ams_Utility_Pvt.Write_Conc_log('After c_operator-- l_operator = '||l_operator);
1647
1648 if l_operator in ('IS', 'IN', 'LIKE','=') then
1649 l_operator := ' LIKE ';
1650 l_cond_conn := ' or ';
1651 end if;
1652 end loop;
1653 if l_operator is null and l_cond_conn is null then
1654 -- l_operator := ' NOT IN ';
1655 -- l_cond_conn := ' and ';
1656 l_operator := ' LIKE ';
1657 l_cond_conn := ' or ';
1658 end if;
1659 close c_operator;
1660 Ams_Utility_Pvt.Write_Conc_log('After c_operator LOOP -- l_operator = '||l_operator);
1661 Ams_Utility_Pvt.Write_Conc_log('After c_operator LOOP -- l_cond_conn= '||l_cond_conn);
1662
1663
1664 open c_lov_values (l_qualify_cond_rec.cond_id);
1665 loop
1666 fetch c_lov_values into l_test;
1667 exit when c_lov_values%notfound ;
1668 -- l_count := l_count + 1;
1669 l_tot_count := l_tot_count + 1;
1670 end loop;
1671 close c_lov_values;
1672 Ams_Utility_Pvt.Write_Conc_log('***** After c_lov_values LOOP -- l_count = '||l_count);
1673 Ams_Utility_Pvt.Write_Conc_log('***** After c_lov_values LOOP -- l_tot_count = '||l_tot_count);
1674
1675 -- if l_count = 0 then
1676 if l_tot_count = 0 then
1677 Ams_Utility_Pvt.Write_Conc_log('After c_lov_values --SELECT ALL is used for lov values ');
1678 open c_lov_type(l_left_operand_rec.list_source_type_id,l_left_operand_rec.attr_name);
1679 fetch c_lov_type into l_lov_type;
1680 close c_lov_type;
1681 Ams_Utility_Pvt.Write_Conc_log('LOV type -- l_lov_type = '||l_lov_type);
1682 if l_lov_type = 'SQL' then
1683 open c_lov_sql(l_left_operand_rec.list_source_type_id,l_left_operand_rec.attr_name);
1684 fetch c_lov_sql into l_lov_sql;
1685 close c_lov_sql;
1686 Ams_Utility_Pvt.Write_Conc_log(' l_lov_sql = '||l_lov_sql);
1687 l_position := instrb(upper(l_lov_sql),'FROM');
1688 Ams_Utility_Pvt.Write_Conc_log(' l_position = '||l_position);
1689 l_final_lov_sql := substr(l_lov_sql,1,l_position - 1)||' BULK COLLECT INTO :1 ,:2 '||substr(l_lov_sql,l_position);
1690 Ams_Utility_Pvt.Write_Conc_log(' l_final_lov_sql = '||l_final_lov_sql);
1691 EXECUTE IMMEDIATE 'BEGIN '||l_final_lov_sql||' ; END; ' USING OUT l_code_tbl ,OUT l_meaning_tbl;
1692 Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1693 end if;
1694 if l_lov_type = 'USER' then
1695 Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1696 Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.attr_name = '||l_left_operand_rec.attr_name);
1697
1698 EXECUTE IMMEDIATE ' BEGIN select valb.value_code '||' BULK COLLECT INTO :1 '||
1699 ' from ams_list_src_fields flds ,ams_attb_lov_b lovb, ams_attb_lov_values_b valb'||
1700 ' where flds.LIST_SOURCE_TYPE_ID = :2'||
1701 ' and flds.SOURCE_COLUMN_NAME = :3 '||
1702 ' and flds.attb_lov_id = lovb.attb_lov_id and lovb.CREATION_TYPE = '||''''|| 'USER'||''''||
1703 ' and lovb.attb_lov_id = valb.attb_lov_id ; END; ' USING OUT l_code_tbl, IN l_left_operand_rec.list_source_type_id, l_left_operand_rec.attr_name;
1704 end if;
1705 Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1706 for i in 1 .. l_code_tbl.count
1707 loop
1711 l_tot_loop := 0;
1708 Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl(i) = '||l_code_tbl(i));
1709 end loop;
1710 -- [[[[[[[[[[[[[[[[[[
1712 for i in 1 .. l_code_tbl.count
1713 loop
1714 -- Ams_Utility_Pvt.Write_Conc_log(' coming in l_code_tbl.count LOOP ');
1715 l_tot_loop := l_tot_loop + 1;
1716 begin
1717 x_string_params (l_param_count + 1) := l_code_tbl(i);
1718 -- dbms_output.put_line('l_param_count = '|| l_param_count);
1719 -- if l_param_count = l_count then
1720 -- if l_param_count+1 = l_code_tbl.count then
1721 Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1722 Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop);
1723 if (l_param_count+1 = l_code_tbl.count + l_p_count) or (l_tot_count = l_tot_loop) then
1724 Ams_Utility_Pvt.Write_Conc_log(' l_param_count+1 '||to_char(l_param_count+1));
1725 l_or := ' ';
1726 else
1727 l_or := ' OR ';
1728 end if;
1729 if l_value_count > 0 then
1730 x_filter_sql := x_filter_sql || l_cond_conn || l_left_operand || l_operator
1731 || ' UPPER( :' || to_char(l_param_count+1)||' ) '||l_or;
1732 -- dbms_output.put_line('>0 x_filter_sql = '|| x_filter_sql);
1733 else
1734 x_filter_sql := x_filter_sql || l_left_operand || l_operator
1735 || ' UPPER( :' || to_char(l_param_count+1)|| ' ) '||l_or;
1736 -- dbms_output.put_line('0else x_filter_sql = '|| x_filter_sql);
1737 end if;
1738
1739 l_param_count := l_param_count + 1;
1740 -- dbms_output.put_line('l_param_count = '|| l_param_count);
1741 end;
1742 end loop;
1743 l_p_count := l_param_count;
1744 Ams_Utility_Pvt.Write_Conc_log('x_num_params = '||x_num_params);
1745 -- [[[[[[[[[[[[[[[[
1746 -- l_param_remove_count := l_param_remove_count + l_param_count;
1747 -- l_param_remove_count := l_param_remove_count + l_p_count ;
1748 l_param_remove_count := l_p_count ;
1749 Ams_Utility_Pvt.Write_Conc_log('in 000 l_param_remove_count = '||l_param_remove_count);
1750 end if; -- l_count = 0
1751
1752 -- if l_count > 0 then
1753 if l_tot_count > 0 then
1754 l_tot_loop := 0;
1755 for l_lov_values_rec in c_lov_values (l_qualify_cond_rec.cond_id)
1756 loop
1757 begin
1758 l_tot_loop := l_tot_loop +1;
1759 x_string_params (l_param_count + 1) := l_lov_values_rec.lov_value;
1760 -- if l_param_count = l_count then
1761 -- if l_param_count+1 - l_param_remove_count = l_count then
1762 Ams_Utility_Pvt.Write_Conc_log(' l_param_remove_count = '||l_param_remove_count);
1763 Ams_Utility_Pvt.Write_Conc_log(' l_param_count = '||l_param_count);
1764 Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1765 Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop );
1766 -- if l_param_count+1 - l_param_remove_count = l_count then
1767 if (l_param_count+1 - l_param_remove_count = l_tot_count) or (l_tot_count = l_tot_loop) then
1768 l_or := ' ';
1769 else
1770 l_or := ' OR ';
1771 end if;
1772 if l_value_count > 0 then
1773 x_filter_sql := x_filter_sql || l_cond_conn || l_left_operand || l_operator
1774 || ' UPPER( :' || to_char(l_param_count+1)||' ) '||l_or;
1775 -- dbms_output.put_line('>0 x_filter_sql = '|| x_filter_sql);
1776 else
1777 x_filter_sql := x_filter_sql || l_left_operand || l_operator
1778 || ' UPPER( :' || to_char(l_param_count+1)|| ' ) '||l_or;
1779 -- dbms_output.put_line('0else x_filter_sql = '|| x_filter_sql);
1780 end if;
1781
1782 l_param_count := l_param_count + 1;
1783 -- dbms_output.put_line('l_param_count = '|| l_param_count);
1784 end;
1785 end loop;
1786 l_p_count := l_param_count;
1787 end if; -- l_count > 0
1788 l_tot_count := 0;
1789 -- x_filter_sql := '('||x_filter_sql || '))';
1790 x_filter_sql := x_filter_sql || ')';
1791 end;
1792 end loop; -- c_qualify_conds
1793 x_filter_sql := '('||x_filter_sql || ')';
1794 x_num_params := l_param_count ;
1795 Ams_Utility_Pvt.Write_Conc_log('Final x_num_params = '||x_num_params);
1796 Ams_Utility_Pvt.Write_Conc_log('end gen_lov_filter_for_templmv');
1797
1798 EXCEPTION
1799 WHEN OTHERS THEN
1800 Ams_Utility_Pvt.Write_Conc_log('Exception in gen_lov_filter_for_templmv : '||SQLERRM);
1801 -- errbuf:= substr(SQLERRM,1,254);
1802 --retcode:= 2;
1803 raise;
1804 end;
1805 -- ---------------------------------------------------------
1806
1807 PROCEDURE gen_constant_filter (
1808 x_filter_sql IN OUT NOCOPY VARCHAR2,
1809 x_string_params IN OUT NOCOPY sql_string_4k,
1810 x_num_params IN OUT NOCOPY NUMBER,
1811 p_template_id IN NUMBER
1812 ) IS
1813 --Query conditions
1814 CURSOR C_query_cond_main IS
1815 select cond.query_condition_id cond_id, count(*)
1816 from AMS_QUERY_CONDITION cond, AMS_QUERY_COND_DISP_STRUCT_all struct,
1817 AMS_COND_STRUCT_RESTRICT_VALUE res_values
1821 and struct.QUERY_COND_DISP_STRUCT_ID = res_values.query_cond_disp_struct_id
1818 where cond.template_id = p_template_id
1819 and cond.value1_type = 'CONSTANT'
1820 and struct.QUERY_CONDITION_ID = cond.query_condition_id
1822 and struct.token_type= 'OPERATOR'
1823 and cond.mandatory_flag = 'Y'
1824 group by cond.query_condition_id
1825 having count(*) = 1;
1826
1827 --Operand
1828 CURSOR C_left_oprand (p_cond_id NUMBER) IS
1829 select struct.non_variant_value attr_name, source_object_name table_name, src_types.list_source_type_id
1830 from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_QUERY_ALIAS alias, ams_list_src_types src_types
1831 where struct.query_condition_id = p_cond_id
1832 and struct.token_type = 'ATTRIBUTE'
1833 and alias.query_alias_id = struct.query_alias_id
1834 and alias.OBJECT_NAME = src_types.SOURCE_TYPE_CODE;
1835
1836 --Operator
1837 CURSOR C_operator(p_query_cond_id NUMBER) IS
1838 select upper(res_values.code) operator, count(*)
1839 from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_COND_STRUCT_RESTRICT_VALUE res_values
1840 where struct.query_condition_id = p_query_cond_id and
1841 struct.token_type = 'OPERATOR'
1842 and struct.QUERY_COND_DISP_STRUCT_ID = res_values.query_cond_disp_struct_id
1843 group by upper(res_values.code)
1844 having count(*) = 1; --has only one operator
1845
1846 --Values
1847 CURSOR C_display_values(p_query_cond_id NUMBER) IS
1848 select display_text
1849 from AMS_QUERY_COND_DISP_STRUCT_vl struct
1850 where struct.query_condition_id = p_query_cond_id and
1851 struct.token_type in ('VALUE1');
1852
1853 CURSOR C_display_value2(p_query_cond_id NUMBER) IS
1854 select display_text
1855 from AMS_QUERY_COND_DISP_STRUCT_vl struct
1856 where struct.query_condition_id = p_query_cond_id and
1857 struct.token_type in ('VALUE2');
1858
1859 --Alias
1860 CURSOR C_alias(p_template_id NUMBER) IS
1861 select distinct alias2.query_alias_id,alias2.source_object_name, alias2.line_num
1862 from
1863 (SELECT distinct non_variant_value, query_alias_id
1864 from AMS_QUERY_COND_DISP_STRUCT_vl
1865 where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
1866 and query_alias_id is not null) alias1,
1867 (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
1868 'A'||to_char(rownum) line_num
1869 from AMS_QUERY_ALIAS alias,ams_list_src_types types
1870 where alias.template_id = p_template_id
1871 and alias.object_name = types.source_type_code
1872 order by alias.query_alias_id) alias2
1873 where alias1.query_alias_id = alias2.query_alias_id;
1874
1875 l_dummy number;
1876 l_query_cond_id number;
1877 l_cond_count number := 0;
1878 l_num_params number := 0;
1879 l_left_operand_rec C_left_oprand%rowtype;
1880 l_left_operand VARCHAR2(2000);
1881 l_operator varchar2(255);
1882
1883 l_qa_id number;
1884 l_object_name varchar2(60);
1885 l_object_alias varchar2(30);
1886
1887 l_value1 varchar2(2000);
1888 l_value2 varchar2(2000);
1889
1890 BEGIN
1891 Ams_Utility_Pvt.Write_Conc_log('Start gen_constant_filter');
1892
1893 l_num_params := x_num_params;
1894 l_cond_count := x_num_params;
1895
1896 if l_cond_count = 0 then
1897 x_filter_sql := null;
1898 end if;
1899
1900 --Loop thru each mandatory condition having one operator
1901 --and a value associated with it
1902 FOR cond_rec IN C_query_cond_main LOOP
1903
1904 Ams_Utility_Pvt.Write_Conc_log('Processing cond_id: '||cond_rec.cond_id);
1905
1906 --resetting variables
1907 l_value1 := null;
1908 l_value2 := null;
1909 l_operator := null;
1910 l_left_operand := null;
1911
1912 -------
1913 --start building the filter_sql
1914 if l_cond_count > 0 then
1915 x_filter_sql := x_filter_sql || ' AND (';
1916 Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1917 else
1918 x_filter_sql := x_filter_sql || '(';
1919 Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1920 end if;
1921
1922 l_cond_count := l_cond_count + 1;
1923 -------
1924 --Get the letf operand value
1925 open C_left_oprand (cond_rec.cond_id);
1926 fetch C_left_oprand into l_left_operand_rec;
1927 close C_left_oprand;
1928 Ams_Utility_Pvt.Write_Conc_log('Left Operand: '||l_left_operand_rec.table_name||'.'||l_left_operand_rec.attr_name
1929 ||' list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1930
1931 l_qa_id := null;
1932 l_object_name := null;
1933 l_object_alias := null;
1934 -------
1935 --Get attribute and table name and alias
1936 OPEN C_alias(p_template_id);
1937 LOOP
1938 fetch C_alias into l_qa_id,l_object_name,l_object_alias;
1939 exit when C_alias%notfound;
1940 if l_object_name = l_left_operand_rec.table_name then
1941 exit;
1942 end if;
1943 END LOOP;
1944 CLOSE C_alias;
1945
1946 l_left_operand := 'UPPER('||l_object_alias || '.' || l_left_operand_rec.attr_name||') ';
1947 Ams_Utility_Pvt.Write_Conc_log('l_left_operand = '||l_left_operand);
1948
1949 -------
1950 --Get Operator
1954
1951 OPEN C_operator(cond_rec.cond_id);
1952 FETCH C_operator INTO l_operator, l_dummy;
1953 CLOSE C_operator;
1955 Ams_Utility_Pvt.Write_Conc_log('Operator = '||l_operator);
1956
1957 IF l_operator is not null THEN
1958
1959 IF l_operator <> 'IN' THEN
1960 OPEN C_display_values(cond_rec.cond_id);
1961 FETCH C_display_values INTO l_value1;
1962 CLOSE C_display_values;
1963 Ams_Utility_Pvt.Write_Conc_log('Value1 = '||l_value1);
1964 END IF;
1965
1966 IF l_operator = 'BETWEEN' THEN
1967
1968 OPEN C_display_value2(cond_rec.cond_id);
1969 FETCH C_display_value2 INTO l_value2;
1970 CLOSE C_display_value2;
1971 Ams_Utility_Pvt.Write_Conc_log('Value2 = '||l_value2);
1972
1973 END IF;
1974 ELSE --if l_operator is not null
1975 Ams_Utility_Pvt.Write_Conc_log('** Operator is null.. **');
1976 END IF;
1977
1978 --Build filter clause
1979 -------
1980 IF l_operator is not null then
1981 if l_value1 is not null and l_operator <> 'BETWEEN' then
1982 l_num_params := l_num_params + 1;
1983 x_filter_sql := x_filter_sql || l_left_operand || ' ' ||l_operator
1984 || ' UPPER(:'|| to_char(l_num_params) ||')) ';
1985 x_string_params (l_num_params) := l_value1;
1986
1987 elsif l_value2 is not null and l_operator = 'BETWEEN' then
1988 l_num_params := l_num_params + 2;
1989 x_filter_sql := x_filter_sql ||l_left_operand || ' ' ||l_operator
1990 || ' UPPER(:'|| to_char(l_num_params-1) ||') AND UPPER(:'|| to_char(l_num_params) ||')) ' ;
1991 x_string_params (l_num_params-1) := l_value1;
1992 x_string_params (l_num_params) := l_value2;
1993 end if;
1994 end if;
1995
1996 END LOOP;
1997 x_num_params := l_num_params;
1998 Ams_Utility_Pvt.Write_Conc_log('Final param count: '||to_char(x_num_params));
1999 Ams_Utility_Pvt.Write_Conc_log('Final filter sql : '||x_filter_sql);
2000 Ams_Utility_Pvt.Write_Conc_log('End gen_constant_filter.');
2001
2002 EXCEPTION
2003 WHEN OTHERS THEN
2004 Ams_Utility_Pvt.Write_Conc_log('Exception in gen_constant_filter : '||SQLERRM);
2005 Raise;
2006
2007 END gen_constant_filter;
2008 ------------------------------------
2009
2010 END AMS_List_running_total_pvt;