[Home] [Help]
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
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
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);
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);
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;
520:
521: open c_tablespace;
522: fetch c_tablespace into l_tablespace,l_index_tablespace;
523: close c_tablespace;
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:
527: -- View Name
528: -- ---------
521: open c_tablespace;
522: fetch c_tablespace into l_tablespace,l_index_tablespace;
523: close c_tablespace;
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:
527: -- View Name
528: -- ---------
529: l_view_name := 'AMS_QT_'||to_char(p_template_id)||'_MV';
526:
527: -- View Name
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;
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
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);
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
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
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
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);
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);
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;
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: /*
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: -- ----------------------------------------------
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'
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));
648:
649: if l_master_total_records > l_sample_size then
650: l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
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));
648:
649: if l_master_total_records > l_sample_size then
650: l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
651: l_sample := 'Y';
651: l_sample := 'Y';
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;
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));
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' );
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;
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;
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' );
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 := ',';
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
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;
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);
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: -- }}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
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;
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
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
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,
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:
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;
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:
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:
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);
774: Ams_Utility_Pvt.Write_Conc_log('sample (N) l_from_string = '||l_from_string);
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);
774: Ams_Utility_Pvt.Write_Conc_log('sample (N) l_from_string = '||l_from_string);
775:
776:
777: end if;
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);
774: Ams_Utility_Pvt.Write_Conc_log('sample (N) l_from_string = '||l_from_string);
775:
776:
777: end if;
778: if l_sample = 'Y' then
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);
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:
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;
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;
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
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
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);
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
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
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
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,
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);
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->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
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);
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->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
906: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
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);
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->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
906: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
907: errbuf:= substr(SQLERRM,1,254);
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);
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->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
906: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
907: errbuf:= substr(SQLERRM,1,254);
908: retcode:= 2;
909: return;
902: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
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->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
906: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
907: errbuf:= substr(SQLERRM,1,254);
908: retcode:= 2;
909: return;
910: end if;
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->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
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.');
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);
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;
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;
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);
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:
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
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||' ';
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;
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 );
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);
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;
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'
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;
978: -- l_drop_string := 'DROP MATERIALIZED VIEW '||l_view_name;
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;
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;
981: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
982: execute immediate l_drop_string;
983:
977: l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
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;
981: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
982: execute immediate l_drop_string;
983:
984: Ams_Utility_Pvt.Write_Conc_log('Table Droped ');
985: end if;
980: -- execute immediate l_truncate_string;
981: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_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;
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: /*
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 ');
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
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. ');
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:
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;
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;
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) := ' ';
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
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 ' ||
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
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));
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),
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 ');
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
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 .');
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:
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
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 ');
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;
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 ');
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 .');
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;
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;
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:
1144: Ams_Utility_Pvt.Write_Conc_log('End: Bitmap Index Creation ');
1140: Ams_Utility_Pvt.Write_Conc_log('Finish Index Analyze .');
1141: end loop;
1142: close c_index_columns;
1143:
1144: Ams_Utility_Pvt.Write_Conc_log('End: Bitmap Index Creation ');
1145:
1146: commit;
1147: retcode:= 0;
1148: -- SOLIN, Bug 3696553
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
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:
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);
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;
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);
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);
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;
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:
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
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,
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;
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:
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);
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
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;
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
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 ');
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
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:
1263: EXCEPTION
1264: WHEN OTHERS THEN
1265: -- hz_common_pub.enable_cont_source_security;
1262:
1263: EXCEPTION
1264: WHEN OTHERS THEN
1265: -- hz_common_pub.enable_cont_source_security;
1266: Ams_Utility_Pvt.Write_Conc_log('Exception in generate_mv_for_template : '||SQLERRM);
1267: errbuf:= substr(SQLERRM,1,254);
1268: retcode:= 2;
1269:
1270: update ams_query_template_all
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;
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
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;';
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:
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);
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;
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 ');
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
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;
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;
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----------------------- ');
1411: AMS_Utility_PVT.Create_Log (
1412: x_return_status => l_return_status,
1413: p_arc_log_used_by => 'RECL',
1414: p_log_used_by_id => p_template_id,
1415: p_msg_data => 'Start : Recalculate Process at '||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'),
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,
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),
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)
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 ',
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,
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);
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);
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;
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;
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;
1708: open c_operator (l_qualify_cond_rec.cond_id);
1709: loop
1710: fetch c_operator into l_operator;
1711: exit when c_operator%notfound;
1712: Ams_Utility_Pvt.Write_Conc_log('After c_operator-- l_operator = '||l_operator);
1713:
1714: if l_operator in ('IS', 'IN', 'LIKE','=') then
1715: l_operator := ' LIKE ';
1716: l_cond_conn := ' or ';
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);
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
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
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 ');
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);
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;
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);
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);
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
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);
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'||
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;
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
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);
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 := ' ';
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
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;
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 ;
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
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
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
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 := ' ';
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
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
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);
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;
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:
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;
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;
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;
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);
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
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:
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.. **');
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: -------
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
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
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);
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: ------------------------------------