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