[Home] [Help]
464: l_oders_view_used varchar2(30);
465: l_number_of_days NUMBER;
466:
467: begin
468: Ams_Utility_Pvt.Write_Conc_log('+++++++++++++++++++++Start CM program : AMSGMVTP.+++++++++++++++++++++');
469: Ams_Utility_Pvt.Write_Conc_log('p_template_id = '||to_char(p_template_id));
470:
471: -- Update the ams_query_template_all table to set mv_available_flag to N
472: -- so that list creation UI can grey out Recalc and Preview button
465: l_number_of_days NUMBER;
466:
467: begin
468: Ams_Utility_Pvt.Write_Conc_log('+++++++++++++++++++++Start CM program : AMSGMVTP.+++++++++++++++++++++');
469: Ams_Utility_Pvt.Write_Conc_log('p_template_id = '||to_char(p_template_id));
470:
471: -- Update the ams_query_template_all table to set mv_available_flag to N
472: -- so that list creation UI can grey out Recalc and Preview button
473: -- while the regeneration process is in progress
479:
480: open c_apps_short_name;
481: fetch c_apps_short_name into l_application_short_name;
482: close c_apps_short_name;
483: Ams_Utility_Pvt.Write_Conc_log('l_application_short_name = '||l_application_short_name);
484: l_fnd_return := fnd_installation.get_app_info(l_application_short_name,
485: l_status_AppInfo,
486: l_industry_AppInfo ,
487: l_ams_schema);
484: l_fnd_return := fnd_installation.get_app_info(l_application_short_name,
485: l_status_AppInfo,
486: l_industry_AppInfo ,
487: l_ams_schema);
488: Ams_Utility_Pvt.Write_Conc_log('l_ams_schema = '||l_ams_schema);
489: open c_apps;
490: fetch c_apps into l_apps_schema;
491: close c_apps;
492: Ams_Utility_Pvt.Write_Conc_log('l_apps_schema = '||l_apps_schema);
488: Ams_Utility_Pvt.Write_Conc_log('l_ams_schema = '||l_ams_schema);
489: open c_apps;
490: fetch c_apps into l_apps_schema;
491: close c_apps;
492: Ams_Utility_Pvt.Write_Conc_log('l_apps_schema = '||l_apps_schema);
493:
494: open c_tablespace;
495: fetch c_tablespace into l_tablespace,l_index_tablespace;
496: close c_tablespace;
493:
494: open c_tablespace;
495: fetch c_tablespace into l_tablespace,l_index_tablespace;
496: close c_tablespace;
497: Ams_Utility_Pvt.Write_Conc_log('l_tablespace = '||l_tablespace);
498: Ams_Utility_Pvt.Write_Conc_log('l_index_tablespace = '||l_index_tablespace);
499:
500: -- View Name
501: -- ---------
494: open c_tablespace;
495: fetch c_tablespace into l_tablespace,l_index_tablespace;
496: close c_tablespace;
497: Ams_Utility_Pvt.Write_Conc_log('l_tablespace = '||l_tablespace);
498: Ams_Utility_Pvt.Write_Conc_log('l_index_tablespace = '||l_index_tablespace);
499:
500: -- View Name
501: -- ---------
502: l_view_name := 'AMS_QT_'||to_char(p_template_id)||'_MV';
499:
500: -- View Name
501: -- ---------
502: l_view_name := 'AMS_QT_'||to_char(p_template_id)||'_MV';
503: Ams_Utility_Pvt.Write_Conc_log('l_view_name = '||l_view_name);
504:
505: -- SOLIN, Bug 3696553
506: OPEN c_get_dblink;
507: FETCH c_get_dblink INTO l_remote_flag, l_dblink;
510: --Get Database version ----aanjaria
511: OPEN c_get_db_version;
512: FETCH c_get_db_version INTO l_db_version;
513: CLOSE c_get_db_version;
514: Ams_Utility_Pvt.Write_Conc_log('Database version = '||to_char(l_db_version));
515:
516: IF l_remote_flag = 'N'
517: THEN
518: -- SOLIN, end
520: -- SOLIN, Bug 3696553
521: ELSE
522: l_create_string := ' CREATE TABLE ' ||l_view_name;
523: l_remote_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_view_name||'@'||l_dblink;
524: Ams_Utility_Pvt.Write_Conc_log('l_remote_create_synonym = '||l_remote_create_synonym);
525: l_remote_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
526: Ams_Utility_Pvt.Write_Conc_log('l_remote_drop_string = '||l_remote_drop_string);
527: l_remote_truncate_string := 'TRUNCATE TABLE '||l_view_name;
528: Ams_Utility_Pvt.Write_Conc_log('l_remote_truncate_string = '||l_remote_truncate_string);
522: l_create_string := ' CREATE TABLE ' ||l_view_name;
523: l_remote_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_view_name||'@'||l_dblink;
524: Ams_Utility_Pvt.Write_Conc_log('l_remote_create_synonym = '||l_remote_create_synonym);
525: l_remote_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
526: Ams_Utility_Pvt.Write_Conc_log('l_remote_drop_string = '||l_remote_drop_string);
527: l_remote_truncate_string := 'TRUNCATE TABLE '||l_view_name;
528: Ams_Utility_Pvt.Write_Conc_log('l_remote_truncate_string = '||l_remote_truncate_string);
529: END IF;
530: -- SOLIN, end
524: Ams_Utility_Pvt.Write_Conc_log('l_remote_create_synonym = '||l_remote_create_synonym);
525: l_remote_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
526: Ams_Utility_Pvt.Write_Conc_log('l_remote_drop_string = '||l_remote_drop_string);
527: l_remote_truncate_string := 'TRUNCATE TABLE '||l_view_name;
528: Ams_Utility_Pvt.Write_Conc_log('l_remote_truncate_string = '||l_remote_truncate_string);
529: END IF;
530: -- SOLIN, end
531:
532: -- If database version 9.2 or higher then use TABLE COMPRESSION feature --aanjaria
537: l_create_string := l_create_string || ' AS SELECT ';
538: l_insert_string := ' INSERT INTO '||l_view_name||' SELECT ';
539: END IF;
540:
541: Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
542: Ams_Utility_Pvt.Write_Conc_log('l_insert_string = '||l_insert_string);
543: l_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_ams_schema||'.'||l_view_name;
544: l_drop_synonym := 'DROP PUBLIC SYNONYM '||l_view_name;
545: Ams_Utility_Pvt.Write_Conc_log('l_create_synonym = '||l_create_synonym);
538: l_insert_string := ' INSERT INTO '||l_view_name||' SELECT ';
539: END IF;
540:
541: Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
542: Ams_Utility_Pvt.Write_Conc_log('l_insert_string = '||l_insert_string);
543: l_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_ams_schema||'.'||l_view_name;
544: l_drop_synonym := 'DROP PUBLIC SYNONYM '||l_view_name;
545: Ams_Utility_Pvt.Write_Conc_log('l_create_synonym = '||l_create_synonym);
546: Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
541: Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
542: Ams_Utility_Pvt.Write_Conc_log('l_insert_string = '||l_insert_string);
543: l_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_ams_schema||'.'||l_view_name;
544: l_drop_synonym := 'DROP PUBLIC SYNONYM '||l_view_name;
545: Ams_Utility_Pvt.Write_Conc_log('l_create_synonym = '||l_create_synonym);
546: Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
547: open c_master_view;
548: fetch c_master_view into l_master_view;
549: close c_master_view;
542: Ams_Utility_Pvt.Write_Conc_log('l_insert_string = '||l_insert_string);
543: l_create_synonym := 'CREATE PUBLIC SYNONYM '||l_view_name||' FOR '||l_ams_schema||'.'||l_view_name;
544: l_drop_synonym := 'DROP PUBLIC SYNONYM '||l_view_name;
545: Ams_Utility_Pvt.Write_Conc_log('l_create_synonym = '||l_create_synonym);
546: Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
547: open c_master_view;
548: fetch c_master_view into l_master_view;
549: close c_master_view;
550: /*
550: /*
551: open c_policy_exists;
552: fetch c_policy_exists into l_policy_exists;
553: close c_policy_exists;
554: Ams_Utility_Pvt.Write_Conc_log('l_policy_exists : '||l_policy_exists);
555: */
556: Ams_Utility_Pvt.Write_Conc_log('QT Table cretion started for view : '||l_view_name);
557: -- Get the records in the master data source view
558: -- ----------------------------------------------
552: fetch c_policy_exists into l_policy_exists;
553: close c_policy_exists;
554: Ams_Utility_Pvt.Write_Conc_log('l_policy_exists : '||l_policy_exists);
555: */
556: Ams_Utility_Pvt.Write_Conc_log('QT Table cretion started for view : '||l_view_name);
557: -- Get the records in the master data source view
558: -- ----------------------------------------------
559: -- SOLIN, Bug 3696553
560: IF l_remote_flag = 'N'
576: -- batoleti Bug# 4684584 the 'AMS_LIST_QT_SAMPLE_PCT' is considered only when taking the sample records.
577: -- in other cases, the sample_pct should be zero.
578: l_sample_pct := 0;
579:
580: Ams_Utility_Pvt.Write_Conc_log('Profile Value of AMS_LIST_QT_SAMPLE_SIZE : '|| to_char(l_sample_size));
581: Ams_Utility_Pvt.Write_Conc_log('Profile Value of AMS_LIST_QT_SAMPLE_PCT : '|| to_char(l_sample_pct));
582:
583: if l_master_total_records > l_sample_size then
584: l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
577: -- in other cases, the sample_pct should be zero.
578: l_sample_pct := 0;
579:
580: Ams_Utility_Pvt.Write_Conc_log('Profile Value of AMS_LIST_QT_SAMPLE_SIZE : '|| to_char(l_sample_size));
581: Ams_Utility_Pvt.Write_Conc_log('Profile Value of AMS_LIST_QT_SAMPLE_PCT : '|| to_char(l_sample_pct));
582:
583: if l_master_total_records > l_sample_size then
584: l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
585: l_sample := 'Y';
585: l_sample := 'Y';
586: l_sample_records := round((l_master_total_records * l_sample_pct)/100);
587: -- l_sample_pct := 30;
588: -- l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
589: -- Ams_Utility_Pvt.Write_Conc_log('Sampling percentage = '||l_sample_pct);
590: -- Ams_Utility_Pvt.Write_Conc_log('Sampling size = '||l_sample_size);
591: else
592: l_sample := 'N';
593: end if;
586: l_sample_records := round((l_master_total_records * l_sample_pct)/100);
587: -- l_sample_pct := 30;
588: -- l_sample_pct := FND_PROFILE.VALUE('AMS_LIST_QT_SAMPLE_PCT');
589: -- Ams_Utility_Pvt.Write_Conc_log('Sampling percentage = '||l_sample_pct);
590: -- Ams_Utility_Pvt.Write_Conc_log('Sampling size = '||l_sample_size);
591: else
592: l_sample := 'N';
593: end if;
594: Ams_Utility_Pvt.Write_Conc_log('After getting total number of rows from the master DS : '|| to_char(l_master_total_records));
590: -- Ams_Utility_Pvt.Write_Conc_log('Sampling size = '||l_sample_size);
591: else
592: l_sample := 'N';
593: end if;
594: Ams_Utility_Pvt.Write_Conc_log('After getting total number of rows from the master DS : '|| to_char(l_master_total_records));
595:
596: -- Get All The Columns
597: -- -------------------
598: Ams_Utility_Pvt.Write_Conc_log('Start: Get All The Columns - c_attributes' );
594: Ams_Utility_Pvt.Write_Conc_log('After getting total number of rows from the master DS : '|| to_char(l_master_total_records));
595:
596: -- Get All The Columns
597: -- -------------------
598: Ams_Utility_Pvt.Write_Conc_log('Start: Get All The Columns - c_attributes' );
599: open c_attributes;
600: LOOP
601: fetch c_attributes into l_attribute_name, l_query_alias_id;
602: exit when c_attributes%notfound;
606: end if;
607:
608: -- Get All The Columns With Alias
609: -- ------------------------------
610: Ams_Utility_Pvt.Write_Conc_log('Start: Get All The Columns With Alias - c_attributes_alias ' );
611: open c_attributes_alias;
612: loop
613: fetch c_attributes_alias into l_q_alias_id, l_source_object_name,l_master_flag,l_source_pk_field,
614: l_column_alias;
622: end loop;
623: l_select_string := l_select_string||' '||l_column_alias||'.'||l_attribute_name||' '
624: ||l_column_alias||'_'||l_attribute_name||' '||l_comma;
625: close c_attributes_alias;
626: Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string );
627: Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns With Alias - c_attributes_alias ' );
628: END LOOP;
629: close c_attributes;
630: Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns - c_attributes' );
623: l_select_string := l_select_string||' '||l_column_alias||'.'||l_attribute_name||' '
624: ||l_column_alias||'_'||l_attribute_name||' '||l_comma;
625: close c_attributes_alias;
626: Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string );
627: Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns With Alias - c_attributes_alias ' );
628: END LOOP;
629: close c_attributes;
630: Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns - c_attributes' );
631: l_comma := ',';
626: Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string );
627: Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns With Alias - c_attributes_alias ' );
628: END LOOP;
629: close c_attributes;
630: Ams_Utility_Pvt.Write_Conc_log('End: Get All The Columns - c_attributes' );
631: l_comma := ',';
632:
633: -- (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
634: -- Get All the Columns for MASTER view
647: close c_find_mast_alias;
648: end if;
649:
650: -- -----------------------------------
651: Ams_Utility_Pvt.Write_Conc_log('Start: Get All the Columns for MASTER view - c_master_columns ' );
652: open c_master_columns;
653: LOOP
654: fetch c_master_columns into l_master_column;
655: Exit when c_master_columns%notfound;
659: l_master_column := null;
660: end if;
661: END LOOP;
662: close c_master_columns;
663: Ams_Utility_Pvt.Write_Conc_log('l_master_columns_string = '||l_master_columns_string );
664: Ams_Utility_Pvt.Write_Conc_log('End: Get All the Columns for MASTER view - c_master_columns ' );
665:
666: l_select_string := l_select_string||' '||l_master_columns_string;
667: Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string);
660: end if;
661: END LOOP;
662: close c_master_columns;
663: Ams_Utility_Pvt.Write_Conc_log('l_master_columns_string = '||l_master_columns_string );
664: Ams_Utility_Pvt.Write_Conc_log('End: Get All the Columns for MASTER view - c_master_columns ' );
665:
666: l_select_string := l_select_string||' '||l_master_columns_string;
667: Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string);
668: -- }}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
663: Ams_Utility_Pvt.Write_Conc_log('l_master_columns_string = '||l_master_columns_string );
664: Ams_Utility_Pvt.Write_Conc_log('End: Get All the Columns for MASTER view - c_master_columns ' );
665:
666: l_select_string := l_select_string||' '||l_master_columns_string;
667: Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||l_select_string);
668: -- }}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
669: l_total_attributes := 0;
670: l_total_recs := 0;
671: open c_tot_attributes_alias;
670: l_total_recs := 0;
671: open c_tot_attributes_alias;
672: fetch c_tot_attributes_alias into l_total_attributes;
673: close c_tot_attributes_alias;
674: Ams_Utility_Pvt.Write_Conc_log('l_total_attributes = '||to_char(l_total_attributes));
675:
676: -- dbms_output.put_line('l_total_attributes = '||to_char(l_total_attributes));
677:
678: -- Construct The From Clause
676: -- dbms_output.put_line('l_total_attributes = '||to_char(l_total_attributes));
677:
678: -- Construct The From Clause
679: -- -------------------------
680: Ams_Utility_Pvt.Write_Conc_log('Start: Construct The From Clause ');
681: Ams_Utility_Pvt.Write_Conc_log('l_sample = '||l_sample);
682: if l_sample = 'N' then
683: open c_attributes_alias;
684: loop
677:
678: -- Construct The From Clause
679: -- -------------------------
680: Ams_Utility_Pvt.Write_Conc_log('Start: Construct The From Clause ');
681: Ams_Utility_Pvt.Write_Conc_log('l_sample = '||l_sample);
682: if l_sample = 'N' then
683: open c_attributes_alias;
684: loop
685: fetch c_attributes_alias into l_q_alias_id, l_source_object_name,l_master_flag,l_source_pk_field,
689: if l_total_recs = l_total_attributes then
690: l_comma := ' ';
691: end if;
692: l_from_string := l_from_string||' '||l_source_object_name||' '||l_column_alias||' '||l_comma;
693: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
694:
695: Ams_Utility_Pvt.Write_Conc_log('l_mastobj_name = '||l_mastobj_name);
696: Ams_Utility_Pvt.Write_Conc_log('l_master_flag = '||l_master_flag);
697:
691: end if;
692: l_from_string := l_from_string||' '||l_source_object_name||' '||l_column_alias||' '||l_comma;
693: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
694:
695: Ams_Utility_Pvt.Write_Conc_log('l_mastobj_name = '||l_mastobj_name);
696: Ams_Utility_Pvt.Write_Conc_log('l_master_flag = '||l_master_flag);
697:
698: end loop;
699: close c_attributes_alias;
692: l_from_string := l_from_string||' '||l_source_object_name||' '||l_column_alias||' '||l_comma;
693: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
694:
695: Ams_Utility_Pvt.Write_Conc_log('l_mastobj_name = '||l_mastobj_name);
696: Ams_Utility_Pvt.Write_Conc_log('l_master_flag = '||l_master_flag);
697:
698: end loop;
699: close c_attributes_alias;
700:
698: end loop;
699: close c_attributes_alias;
700:
701: if l_mastobj_name is not null then
702: Ams_Utility_Pvt.Write_Conc_log('inside the if');
703: l_from_string := l_from_string||', '||l_mastobj_name||' '||l_master_alias||' '||l_comma;
704: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
705: end if;
706:
700:
701: if l_mastobj_name is not null then
702: Ams_Utility_Pvt.Write_Conc_log('inside the if');
703: l_from_string := l_from_string||', '||l_mastobj_name||' '||l_master_alias||' '||l_comma;
704: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
705: end if;
706:
707: -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
708: Ams_Utility_Pvt.Write_Conc_log('sample (N) l_from_string = '||l_from_string);
703: l_from_string := l_from_string||', '||l_mastobj_name||' '||l_master_alias||' '||l_comma;
704: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
705: end if;
706:
707: -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
708: Ams_Utility_Pvt.Write_Conc_log('sample (N) l_from_string = '||l_from_string);
709:
710:
711: end if;
704: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
705: end if;
706:
707: -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
708: Ams_Utility_Pvt.Write_Conc_log('sample (N) l_from_string = '||l_from_string);
709:
710:
711: end if;
712: if l_sample = 'Y' then
718: l_total_recs := l_total_recs + 1;
719: if l_total_recs = l_total_attributes then
720: l_comma := ' ';
721: end if;
722: Ams_Utility_Pvt.Write_Conc_log('l_from_string:'||l_from_string||'----l_source_object_name:'||l_source_object_name||'--l_column_alias'||l_column_alias);
723: l_from_string := l_from_string||' '||l_source_object_name||' '||l_column_alias||' '||l_comma;
724: end loop;
725: close c_from_clause;
726: Ams_Utility_Pvt.Write_Conc_log('sample (Y) l_from_string = '||l_from_string);
722: Ams_Utility_Pvt.Write_Conc_log('l_from_string:'||l_from_string||'----l_source_object_name:'||l_source_object_name||'--l_column_alias'||l_column_alias);
723: l_from_string := l_from_string||' '||l_source_object_name||' '||l_column_alias||' '||l_comma;
724: end loop;
725: close c_from_clause;
726: Ams_Utility_Pvt.Write_Conc_log('sample (Y) l_from_string = '||l_from_string);
727:
728: end if;
729: Ams_Utility_Pvt.Write_Conc_log('End: Construct The From Clause ');
730:
725: close c_from_clause;
726: Ams_Utility_Pvt.Write_Conc_log('sample (Y) l_from_string = '||l_from_string);
727:
728: end if;
729: Ams_Utility_Pvt.Write_Conc_log('End: Construct The From Clause ');
730:
731: -- dbms_output.put_line('coming after the from clause........ ');
732:
733: -- Construct The Where Clause
734: -- --------------------------
735: -- --------------------------------------------------------
736:
737: l_number_of_days := FND_PROFILE.VALUE('AMS_ORDER_LOOK_BACK_PRD_DAYS');
738: Ams_Utility_Pvt.Write_Conc_log('Profile value for Orders look back period: '||l_number_of_days);
739: Ams_Utility_Pvt.Write_Conc_log('Start: Construct The Where Clause ');
740: l_total_attributes := 0;
741: open c_tot_attributes_alias;
742: fetch c_tot_attributes_alias into l_total_attributes;
735: -- --------------------------------------------------------
736:
737: l_number_of_days := FND_PROFILE.VALUE('AMS_ORDER_LOOK_BACK_PRD_DAYS');
738: Ams_Utility_Pvt.Write_Conc_log('Profile value for Orders look back period: '||l_number_of_days);
739: Ams_Utility_Pvt.Write_Conc_log('Start: Construct The Where Clause ');
740: l_total_attributes := 0;
741: open c_tot_attributes_alias;
742: fetch c_tot_attributes_alias into l_total_attributes;
743: close c_tot_attributes_alias;
740: l_total_attributes := 0;
741: open c_tot_attributes_alias;
742: fetch c_tot_attributes_alias into l_total_attributes;
743: close c_tot_attributes_alias;
744: Ams_Utility_Pvt.Write_Conc_log('l_total_attributes = '||to_char(l_total_attributes));
745: if l_total_attributes = 1 AND l_mastobj_name is null then
746: l_where_string := null;
747: end if;
748: if l_total_attributes > 1 or (l_total_attributes = 1 AND l_mastobj_name is not null) then
770: l_total_recs := 0;
771: open c_total_child;
772: fetch c_total_child into l_total_child;
773: close c_total_child;
774: Ams_Utility_Pvt.Write_Conc_log('l_total_child = '||to_char(l_total_child));
775: If l_total_child = 0 then
776: l_where_string := null;
777: end if;
778: if l_total_child > 0 then
803: end if;
804: end loop;
805: close c_const_where;
806:
807: Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
808: end if;
809: end if; -- if l_total_attributes > 1 then
810: Ams_Utility_Pvt.Write_Conc_log('End: Construct The Where Clause ');
811: Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
806:
807: Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
808: end if;
809: end if; -- if l_total_attributes > 1 then
810: Ams_Utility_Pvt.Write_Conc_log('End: Construct The Where Clause ');
811: Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
812: l_no_of_chunks := 0;
813: l_no_of_chunks := ceil(length(l_select_string)/2000 );
814: for i in 1 ..l_no_of_chunks
807: Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
808: end if;
809: end if; -- if l_total_attributes > 1 then
810: Ams_Utility_Pvt.Write_Conc_log('End: Construct The Where Clause ');
811: Ams_Utility_Pvt.Write_Conc_log('l_create_string = '||l_create_string);
812: l_no_of_chunks := 0;
813: l_no_of_chunks := ceil(length(l_select_string)/2000 );
814: for i in 1 ..l_no_of_chunks
815: loop
812: l_no_of_chunks := 0;
813: l_no_of_chunks := ceil(length(l_select_string)/2000 );
814: for i in 1 ..l_no_of_chunks
815: loop
816: Ams_Utility_Pvt.Write_Conc_log('l_select_string = '||substrb(l_select_string,(2000*i) - 1999,2000));
817: end loop;
818:
819: l_create1_string := l_create_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
820: if l_where_string is null then
823: l_create1_string := l_create1_string||' '||l_where2;
824: end if;
825:
826: -- Construct the LOV condirion Start --------------------------- *********************************
827: Ams_Utility_Pvt.Write_Conc_log('Start Construction LOV condition. ');
828: AMS_List_running_total_pvt.gen_lov_filter_for_templmv(
829: l_filter_sql,
830: l_string_params,
831: l_num_params,
831: l_num_params,
832: p_template_id
833: ) ;
834:
835: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
836: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
837: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
838: if l_string_params.count > 100 then
839: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
832: p_template_id
833: ) ;
834:
835: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
836: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
837: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
838: if l_string_params.count > 100 then
839: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
840: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
833: ) ;
834:
835: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
836: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
837: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
838: if l_string_params.count > 100 then
839: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
840: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
841: errbuf:= substr(SQLERRM,1,254);
835: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
836: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
837: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
838: if l_string_params.count > 100 then
839: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
840: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
841: errbuf:= substr(SQLERRM,1,254);
842: retcode:= 2;
843: return;
836: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
837: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
838: if l_string_params.count > 100 then
839: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
840: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
841: errbuf:= substr(SQLERRM,1,254);
842: retcode:= 2;
843: return;
844: end if;
849: l_num_params,
850: p_template_id
851: ) ;
852:
853: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
854: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
855: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
856: if l_string_params.count > 100 then
857: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
850: p_template_id
851: ) ;
852:
853: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
854: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
855: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
856: if l_string_params.count > 100 then
857: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
858: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
851: ) ;
852:
853: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
854: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
855: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
856: if l_string_params.count > 100 then
857: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
858: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
859: errbuf:= substr(SQLERRM,1,254);
853: Ams_Utility_Pvt.Write_Conc_log('l_filter_sql = '||l_filter_sql);
854: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
855: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
856: if l_string_params.count > 100 then
857: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
858: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
859: errbuf:= substr(SQLERRM,1,254);
860: retcode:= 2;
861: return;
854: Ams_Utility_Pvt.Write_Conc_log('l_num_params = '||l_num_params);
855: Ams_Utility_Pvt.Write_Conc_log('l_string_params.count = '||l_string_params.count);
856: if l_string_params.count > 100 then
857: Ams_Utility_Pvt.Write_Conc_log('ERROR->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
858: Ams_Utility_Pvt.Write_Conc_log('Maximum 100 LOV value selections are allowed. Please reselect the LOV values.');
859: errbuf:= substr(SQLERRM,1,254);
860: retcode:= 2;
861: return;
862: end if;
861: return;
862: end if;
863: begin
864: for i in 1 .. l_string_params.count loop
865: Ams_Utility_Pvt.Write_Conc_log('param '||to_char(i)||' = '|| l_string_params(i));
866: end loop;
867: exception
868: when others then
869: Ams_Utility_Pvt.Write_Conc_log('Exception in loop : '||SQLERRM);
865: Ams_Utility_Pvt.Write_Conc_log('param '||to_char(i)||' = '|| l_string_params(i));
866: end loop;
867: exception
868: when others then
869: Ams_Utility_Pvt.Write_Conc_log('Exception in loop : '||SQLERRM);
870: end;
871: Ams_Utility_Pvt.Write_Conc_log('End Construction LOV condition. ');
872: -- Construct the LOV condirion End --------------------------- *********************************
873:
867: exception
868: when others then
869: Ams_Utility_Pvt.Write_Conc_log('Exception in loop : '||SQLERRM);
870: end;
871: Ams_Utility_Pvt.Write_Conc_log('End Construction LOV condition. ');
872: -- Construct the LOV condirion End --------------------------- *********************************
873:
874: if l_num_params > 0 and l_filter_sql is not NULL then
875: if l_where_string is null then
877: else
878: l_where_string := l_where_string||' AND '||l_filter_sql;
879: end if;
880: end if;
881: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
882: Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
883: -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
884: l_create_string := l_create_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
885: l_insert_string := l_insert_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
878: l_where_string := l_where_string||' AND '||l_filter_sql;
879: end if;
880: end if;
881: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
882: Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
883: -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
884: l_create_string := l_create_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
885: l_insert_string := l_insert_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
886: l_no_of_chunks := 0;
879: end if;
880: end if;
881: Ams_Utility_Pvt.Write_Conc_log('l_from_string = '||l_from_string);
882: Ams_Utility_Pvt.Write_Conc_log('l_where_string = '||l_where_string);
883: -- Ams_Utility_Pvt.Write_Conc_log('FINAL -- l_tmp_string = '||l_tmp_string);
884: l_create_string := l_create_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
885: l_insert_string := l_insert_string ||' '||l_select_string||' '||l_from_string||' '||l_where_string||' '||l_policy_where||' ';
886: l_no_of_chunks := 0;
887: l_no_of_chunks := ceil(length(l_create_string)/2000 );
886: l_no_of_chunks := 0;
887: l_no_of_chunks := ceil(length(l_create_string)/2000 );
888: for i in 1 ..l_no_of_chunks
889: loop
890: Ams_Utility_Pvt.Write_Conc_log('Final l_create_string = '||substrb(l_create_string,(2000*i) - 1999,2000));
891: end loop;
892:
893: l_no_of_chunks := ceil(length(l_insert_string)/2000 );
894: Ams_Utility_Pvt.Write_Conc_log('Final l_insert_string chunks= '||l_no_of_chunks);
890: Ams_Utility_Pvt.Write_Conc_log('Final l_create_string = '||substrb(l_create_string,(2000*i) - 1999,2000));
891: end loop;
892:
893: l_no_of_chunks := ceil(length(l_insert_string)/2000 );
894: Ams_Utility_Pvt.Write_Conc_log('Final l_insert_string chunks= '||l_no_of_chunks);
895: for i in 1 ..l_no_of_chunks
896: loop
897: Ams_Utility_Pvt.Write_Conc_log(substrb(l_insert_string,(2000*i) - 1999,2000));
898: end loop;
893: l_no_of_chunks := ceil(length(l_insert_string)/2000 );
894: Ams_Utility_Pvt.Write_Conc_log('Final l_insert_string chunks= '||l_no_of_chunks);
895: for i in 1 ..l_no_of_chunks
896: loop
897: Ams_Utility_Pvt.Write_Conc_log(substrb(l_insert_string,(2000*i) - 1999,2000));
898: end loop;
899:
900: -- SOLIN, bug 3696553
901: IF l_remote_flag = 'N'
904: -- SOLIN, end
905: open c_view_exists;
906: fetch c_view_exists into l_view_exists;
907: close c_view_exists;
908: Ams_Utility_Pvt.Write_Conc_log('l_view_exists = '||l_view_exists);
909: if l_view_exists = 'Y' then
910: -- l_truncate_string := 'TRUNCATE TABLE '||l_ams_schema||'.'||l_view_name;
911: l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
912: -- l_drop_string := 'DROP MATERIALIZED VIEW '||l_view_name;
909: if l_view_exists = 'Y' then
910: -- l_truncate_string := 'TRUNCATE TABLE '||l_ams_schema||'.'||l_view_name;
911: l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
912: -- l_drop_string := 'DROP MATERIALIZED VIEW '||l_view_name;
913: -- Ams_Utility_Pvt.Write_Conc_log('local l_truncate_string = '||l_truncate_string);
914: -- execute immediate l_truncate_string;
915: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
916: execute immediate l_drop_string;
917:
911: l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
912: -- l_drop_string := 'DROP MATERIALIZED VIEW '||l_view_name;
913: -- Ams_Utility_Pvt.Write_Conc_log('local l_truncate_string = '||l_truncate_string);
914: -- execute immediate l_truncate_string;
915: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
916: execute immediate l_drop_string;
917:
918: Ams_Utility_Pvt.Write_Conc_log('Table Droped ');
919: end if;
914: -- execute immediate l_truncate_string;
915: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
916: execute immediate l_drop_string;
917:
918: Ams_Utility_Pvt.Write_Conc_log('Table Droped ');
919: end if;
920: open c_synonym_exists;
921: fetch c_synonym_exists into l_synonym_exists;
922: close c_synonym_exists;
920: open c_synonym_exists;
921: fetch c_synonym_exists into l_synonym_exists;
922: close c_synonym_exists;
923: if l_synonym_exists = 'Y' then
924: Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
925: execute immediate l_drop_synonym;
926: Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
927: end if;
928: /*
922: close c_synonym_exists;
923: if l_synonym_exists = 'Y' then
924: Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
925: execute immediate l_drop_synonym;
926: Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
927: end if;
928: /*
929: if l_policy_exists = 'Y' then
930: Ams_Utility_Pvt.Write_Conc_log('Disable Contant Source Security ');
926: Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
927: end if;
928: /*
929: if l_policy_exists = 'Y' then
930: Ams_Utility_Pvt.Write_Conc_log('Disable Contant Source Security ');
931: hz_common_pub.disable_cont_source_security;
932: end if;
933: */
934: if l_num_params = 0 or l_num_params is null then
932: end if;
933: */
934: if l_num_params = 0 or l_num_params is null then
935: execute immediate l_create1_string;
936: Ams_Utility_Pvt.Write_Conc_log('Table created. ');
937: execute immediate l_create_synonym;
938: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
939: execute immediate l_insert_string;
940: Ams_Utility_Pvt.Write_Conc_log('Insert statement executed. ');
934: if l_num_params = 0 or l_num_params is null then
935: execute immediate l_create1_string;
936: Ams_Utility_Pvt.Write_Conc_log('Table created. ');
937: execute immediate l_create_synonym;
938: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
939: execute immediate l_insert_string;
940: Ams_Utility_Pvt.Write_Conc_log('Insert statement executed. ');
941: end if;
942:
936: Ams_Utility_Pvt.Write_Conc_log('Table created. ');
937: execute immediate l_create_synonym;
938: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
939: execute immediate l_insert_string;
940: Ams_Utility_Pvt.Write_Conc_log('Insert statement executed. ');
941: end if;
942:
943: if l_num_params > 0 and l_filter_sql is not NULL then
944: l_no_of_chunks := 0;
944: l_no_of_chunks := 0;
945: l_no_of_chunks := ceil(length(l_create1_string)/2000 );
946: for i in 1 ..l_no_of_chunks
947: loop
948: Ams_Utility_Pvt.Write_Conc_log('Final l_create1_string = '||substrb(l_create1_string,(2000*i) - 1999,2000));
949: end loop;
950: execute immediate l_create1_string;
951: Ams_Utility_Pvt.Write_Conc_log('Table creation with LOV conditions. ');
952: execute immediate l_create_synonym;
947: loop
948: Ams_Utility_Pvt.Write_Conc_log('Final l_create1_string = '||substrb(l_create1_string,(2000*i) - 1999,2000));
949: end loop;
950: execute immediate l_create1_string;
951: Ams_Utility_Pvt.Write_Conc_log('Table creation with LOV conditions. ');
952: execute immediate l_create_synonym;
953: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
954: for i in 1 .. 100 loop
955: l_table_char(i) := ' ';
949: end loop;
950: execute immediate l_create1_string;
951: Ams_Utility_Pvt.Write_Conc_log('Table creation with LOV conditions. ');
952: execute immediate l_create_synonym;
953: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
954: for i in 1 .. 100 loop
955: l_table_char(i) := ' ';
956: end loop;
957: for i in 1 .. l_string_params.count
956: end loop;
957: for i in 1 .. l_string_params.count
958: loop
959: l_table_char(i) := l_string_params(i);
960: Ams_Utility_Pvt.Write_Conc_log('l_table_char(i) '||to_char(i)||' = '|| l_table_char(i));
961: end loop;
962: l_string := 'DECLARE ' ||
963: 'l_string1 varchar2(10000) ; ' ||
964: 'begin ' ||
987: ' :89 || ' || ' :90 || ' || ' :91 || ' || ' :92 || ' ||
988: ' :93 || ' || ' :94 || ' || ' :95 || ' || ' :96 || ' ||
989: ' :97 || ' || ' :98 || ' || ' :99 || ' || ' :100 ; ' ||
990: l_insert_string||
991: '; exception when others then Ams_Utility_Pvt.Write_Conc_log(SQLERRM); end; ' ;
992: l_no_of_chunks := 0;
993: l_no_of_chunks := ceil(length(l_string)/2000 );
994: for i in 1 ..l_no_of_chunks
995: loop
992: l_no_of_chunks := 0;
993: l_no_of_chunks := ceil(length(l_string)/2000 );
994: for i in 1 ..l_no_of_chunks
995: loop
996: Ams_Utility_Pvt.Write_Conc_log('Final l_string = '||substrb(l_string,(2000*i) - 1999,2000));
997: end loop;
998: for i in 1 .. l_table_char.count
999: loop
1000: Ams_Utility_Pvt.Write_Conc_log('l_table_char '||to_char(i)||' = '||l_table_char(i));
996: Ams_Utility_Pvt.Write_Conc_log('Final l_string = '||substrb(l_string,(2000*i) - 1999,2000));
997: end loop;
998: for i in 1 .. l_table_char.count
999: loop
1000: Ams_Utility_Pvt.Write_Conc_log('l_table_char '||to_char(i)||' = '||l_table_char(i));
1001: end loop;
1002: execute immediate l_string
1003: using l_table_char(1), l_table_char(2), l_table_char(3), l_table_char(4),
1004: l_table_char(5), l_table_char(6), l_table_char(7), l_table_char(8),
1028: l_table_char(79), l_table_char(100);
1029: commit;
1030: end if;
1031:
1032: Ams_Utility_Pvt.Write_Conc_log('QT Table Created ');
1033: /*
1034: if l_policy_exists = 'Y' then
1035: hz_common_pub.enable_cont_source_security;
1036: Ams_Utility_Pvt.Write_Conc_log('Enable Contant Source Security ');
1032: Ams_Utility_Pvt.Write_Conc_log('QT Table Created ');
1033: /*
1034: if l_policy_exists = 'Y' then
1035: hz_common_pub.enable_cont_source_security;
1036: Ams_Utility_Pvt.Write_Conc_log('Enable Contant Source Security ');
1037: end if;
1038: */
1039: -- Update the ams_query_template_all table
1040: update ams_query_template_all
1045: MASTER_DS_REC_NUMBERS = l_master_total_records,
1046: RECALC_TABLE_STATUS = 'AVAILABLE'
1047: where TEMPLATE_ID = p_TEMPLATE_ID;
1048:
1049: Ams_Utility_Pvt.Write_Conc_log('QT Table creation Finish. ');
1050:
1051: commit;
1052:
1053: Ams_Utility_Pvt.Write_Conc_log('Start Table Analyze .');
1049: Ams_Utility_Pvt.Write_Conc_log('QT Table creation Finish. ');
1050:
1051: commit;
1052:
1053: Ams_Utility_Pvt.Write_Conc_log('Start Table Analyze .');
1054: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ');
1055: execute immediate ' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ';
1056: Ams_Utility_Pvt.Write_Conc_log('Finish Table Analyze .');
1057:
1050:
1051: commit;
1052:
1053: Ams_Utility_Pvt.Write_Conc_log('Start Table Analyze .');
1054: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ');
1055: execute immediate ' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ';
1056: Ams_Utility_Pvt.Write_Conc_log('Finish Table Analyze .');
1057:
1058: -- Bitmap Index Creation
1052:
1053: Ams_Utility_Pvt.Write_Conc_log('Start Table Analyze .');
1054: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ');
1055: execute immediate ' ANALYZE TABLE '||l_ams_schema||'.'||l_view_name||' COMPUTE STATISTICS ';
1056: Ams_Utility_Pvt.Write_Conc_log('Finish Table Analyze .');
1057:
1058: -- Bitmap Index Creation
1059: -- ---------------------
1060: Ams_Utility_Pvt.Write_Conc_log('Start: Bitmap Index Creation ');
1056: Ams_Utility_Pvt.Write_Conc_log('Finish Table Analyze .');
1057:
1058: -- Bitmap Index Creation
1059: -- ---------------------
1060: Ams_Utility_Pvt.Write_Conc_log('Start: Bitmap Index Creation ');
1061:
1062: open c_index_columns;
1063: loop
1064: fetch c_index_columns into l_index_column;
1064: fetch c_index_columns into l_index_column;
1065: exit when c_index_columns%notfound;
1066: l_counter := l_counter + 1;
1067: l_index_string := 'CREATE BITMAP INDEX '||l_view_name||'_N_'||to_char(l_counter)||' ON '||l_view_name||'('||l_index_column||')';
1068: Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1069: execute immediate l_index_string;
1070: Ams_Utility_Pvt.Write_Conc_log('Index Created ');
1071: Ams_Utility_Pvt.Write_Conc_log('Start Index Analyze .');
1072: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ');
1066: l_counter := l_counter + 1;
1067: l_index_string := 'CREATE BITMAP INDEX '||l_view_name||'_N_'||to_char(l_counter)||' ON '||l_view_name||'('||l_index_column||')';
1068: Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1069: execute immediate l_index_string;
1070: Ams_Utility_Pvt.Write_Conc_log('Index Created ');
1071: Ams_Utility_Pvt.Write_Conc_log('Start Index Analyze .');
1072: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ');
1073: execute immediate ' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ';
1074: Ams_Utility_Pvt.Write_Conc_log('Finish Index Analyze .');
1067: l_index_string := 'CREATE BITMAP INDEX '||l_view_name||'_N_'||to_char(l_counter)||' ON '||l_view_name||'('||l_index_column||')';
1068: Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1069: execute immediate l_index_string;
1070: Ams_Utility_Pvt.Write_Conc_log('Index Created ');
1071: Ams_Utility_Pvt.Write_Conc_log('Start Index Analyze .');
1072: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ');
1073: execute immediate ' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ';
1074: Ams_Utility_Pvt.Write_Conc_log('Finish Index Analyze .');
1075: end loop;
1068: Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1069: execute immediate l_index_string;
1070: Ams_Utility_Pvt.Write_Conc_log('Index Created ');
1071: Ams_Utility_Pvt.Write_Conc_log('Start Index Analyze .');
1072: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ');
1073: execute immediate ' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ';
1074: Ams_Utility_Pvt.Write_Conc_log('Finish Index Analyze .');
1075: end loop;
1076: close c_index_columns;
1070: Ams_Utility_Pvt.Write_Conc_log('Index Created ');
1071: Ams_Utility_Pvt.Write_Conc_log('Start Index Analyze .');
1072: Ams_Utility_Pvt.Write_Conc_log(' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ');
1073: execute immediate ' ANALYZE INDEX '||l_view_name||'_N_'||to_char(l_counter)||' COMPUTE STATISTICS ';
1074: Ams_Utility_Pvt.Write_Conc_log('Finish Index Analyze .');
1075: end loop;
1076: close c_index_columns;
1077:
1078: Ams_Utility_Pvt.Write_Conc_log('End: Bitmap Index Creation ');
1074: Ams_Utility_Pvt.Write_Conc_log('Finish Index Analyze .');
1075: end loop;
1076: close c_index_columns;
1077:
1078: Ams_Utility_Pvt.Write_Conc_log('End: Bitmap Index Creation ');
1079:
1080: commit;
1081: retcode:= 0;
1082: -- SOLIN, Bug 3696553
1082: -- SOLIN, Bug 3696553
1083: -- IF part is very up above
1084: ELSE
1085: l_check_table_string := 'begin select 1 into :l_exist from sys.all_tables'||'@'||l_dblink||' where table_name = '''||l_view_name||''' ;end;';
1086: Ams_Utility_Pvt.Write_Conc_log('l_check_table_string = '||l_check_table_string);
1087:
1088: BEGIN
1089: execute immediate l_check_table_string using OUT l_exist;
1090: EXCEPTION
1093: END;
1094:
1095: IF l_exist = 1
1096: THEN
1097: Ams_Utility_Pvt.Write_Conc_log('remote l_remote_truncate_string = '||l_remote_truncate_string);
1098: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_remote_truncate_string||'''); END;';
1099: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1100: execute immediate l_create_remote;
1101:
1095: IF l_exist = 1
1096: THEN
1097: Ams_Utility_Pvt.Write_Conc_log('remote l_remote_truncate_string = '||l_remote_truncate_string);
1098: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_remote_truncate_string||'''); END;';
1099: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1100: execute immediate l_create_remote;
1101:
1102: l_drop_string := 'DROP TABLE '||l_view_name;
1103: Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1099: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1100: execute immediate l_create_remote;
1101:
1102: l_drop_string := 'DROP TABLE '||l_view_name;
1103: Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1104: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_drop_string ||'''); END;';
1105: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1106: execute immediate l_create_remote;
1107: END IF;
1101:
1102: l_drop_string := 'DROP TABLE '||l_view_name;
1103: Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1104: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_drop_string ||'''); END;';
1105: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1106: execute immediate l_create_remote;
1107: END IF;
1108:
1109: Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1105: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1106: execute immediate l_create_remote;
1107: END IF;
1108:
1109: Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1110: Ams_Utility_Pvt.Write_Conc_log('remote l_create1_string = '||l_create1_string);
1111: Ams_Utility_Pvt.Write_Conc_log('remote l_insert_string = '||l_insert_string);
1112: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_create1_string ||'''); END;';
1113: Ams_Utility_Pvt.Write_Conc_log('create command = '||l_create_remote);
1106: execute immediate l_create_remote;
1107: END IF;
1108:
1109: Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1110: Ams_Utility_Pvt.Write_Conc_log('remote l_create1_string = '||l_create1_string);
1111: Ams_Utility_Pvt.Write_Conc_log('remote l_insert_string = '||l_insert_string);
1112: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_create1_string ||'''); END;';
1113: Ams_Utility_Pvt.Write_Conc_log('create command = '||l_create_remote);
1114: execute immediate l_create_remote;
1107: END IF;
1108:
1109: Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1110: Ams_Utility_Pvt.Write_Conc_log('remote l_create1_string = '||l_create1_string);
1111: Ams_Utility_Pvt.Write_Conc_log('remote l_insert_string = '||l_insert_string);
1112: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_create1_string ||'''); END;';
1113: Ams_Utility_Pvt.Write_Conc_log('create command = '||l_create_remote);
1114: execute immediate l_create_remote;
1115:
1109: Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1110: Ams_Utility_Pvt.Write_Conc_log('remote l_create1_string = '||l_create1_string);
1111: Ams_Utility_Pvt.Write_Conc_log('remote l_insert_string = '||l_insert_string);
1112: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_create1_string ||'''); END;';
1113: Ams_Utility_Pvt.Write_Conc_log('create command = '||l_create_remote);
1114: execute immediate l_create_remote;
1115:
1116: execute immediate
1117: 'BEGIN
1130: l_null,
1131: OUT l_total_recs,
1132: 'EXECUTE_STRING';
1133:
1134: Ams_Utility_Pvt.Write_Conc_log('x_return_status='||x_return_status || ' x_msg_data=' || x_msg_data);
1135:
1136: -- Update the ams_query_template_all table
1137: update ams_query_template_all
1138: set mv_name = l_view_name,
1148: open c_synonym_exists;
1149: fetch c_synonym_exists into l_synonym_exists;
1150: close c_synonym_exists;
1151: if l_synonym_exists = 'Y' then
1152: Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
1153: execute immediate l_drop_synonym;
1154: Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
1155: end if;
1156: execute immediate l_remote_create_synonym;
1150: close c_synonym_exists;
1151: if l_synonym_exists = 'Y' then
1152: Ams_Utility_Pvt.Write_Conc_log('l_drop_synonym = '||l_drop_synonym);
1153: execute immediate l_drop_synonym;
1154: Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
1155: end if;
1156: execute immediate l_remote_create_synonym;
1157: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
1158:
1153: execute immediate l_drop_synonym;
1154: Ams_Utility_Pvt.Write_Conc_log('Synonym Droped ');
1155: end if;
1156: execute immediate l_remote_create_synonym;
1157: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
1158:
1159: -- create index for remote table?
1160: l_remote_index_string := 'begin select max(COLUMN_ID) into :l_number_of_index from sys.all_tab_columns'||'@'||l_dblink||' where table_name = '''||l_view_name||''' and column_name like ''A%'';end;';
1161: Ams_Utility_Pvt.Write_Conc_log('l_remote_index_string = '||l_remote_index_string);
1157: Ams_Utility_Pvt.Write_Conc_log('Synonym created. ');
1158:
1159: -- create index for remote table?
1160: l_remote_index_string := 'begin select max(COLUMN_ID) into :l_number_of_index from sys.all_tab_columns'||'@'||l_dblink||' where table_name = '''||l_view_name||''' and column_name like ''A%'';end;';
1161: Ams_Utility_Pvt.Write_Conc_log('l_remote_index_string = '||l_remote_index_string);
1162: BEGIN
1163: execute immediate l_remote_index_string using OUT l_number_of_index;
1164: Ams_Utility_Pvt.Write_Conc_log('number of index = '||l_number_of_index);
1165: EXCEPTION
1160: l_remote_index_string := 'begin select max(COLUMN_ID) into :l_number_of_index from sys.all_tab_columns'||'@'||l_dblink||' where table_name = '''||l_view_name||''' and column_name like ''A%'';end;';
1161: Ams_Utility_Pvt.Write_Conc_log('l_remote_index_string = '||l_remote_index_string);
1162: BEGIN
1163: execute immediate l_remote_index_string using OUT l_number_of_index;
1164: Ams_Utility_Pvt.Write_Conc_log('number of index = '||l_number_of_index);
1165: EXCEPTION
1166: WHEN OTHERS THEN
1167: l_number_of_index := 0;
1168: END;
1170: WHILE l_counter <= l_number_of_index
1171: LOOP
1172:
1173: l_remote_index_string := 'BEGIN select column_name into :l_index_column from sys.all_tab_columns'||'@'||l_dblink||' where table_name = '''||l_view_name||''' and column_name like ''A%'' and column_id = '||l_counter||'; END;';
1174: Ams_Utility_Pvt.Write_Conc_log('get column name string = '||l_remote_index_string);
1175: BEGIN
1176: execute immediate l_remote_index_string using OUT l_index_column;
1177: Ams_Utility_Pvt.Write_Conc_log('index column = '||l_index_column);
1178: EXCEPTION
1173: l_remote_index_string := 'BEGIN select column_name into :l_index_column from sys.all_tab_columns'||'@'||l_dblink||' where table_name = '''||l_view_name||''' and column_name like ''A%'' and column_id = '||l_counter||'; END;';
1174: Ams_Utility_Pvt.Write_Conc_log('get column name string = '||l_remote_index_string);
1175: BEGIN
1176: execute immediate l_remote_index_string using OUT l_index_column;
1177: Ams_Utility_Pvt.Write_Conc_log('index column = '||l_index_column);
1178: EXCEPTION
1179: WHEN OTHERS THEN
1180: NULL;
1181: END;
1182: l_index_string := 'BEGIN dbms_utility.exec_ddl_statement'||'@'
1183: ||l_dblink||'(''CREATE BITMAP INDEX '||l_view_name||'_N_'
1184: ||to_char(l_counter)||' ON '||l_view_name||'('||l_index_column
1185: ||')'||'''); END;';
1186: Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1187:
1188: execute immediate l_index_string;
1189:
1190: Ams_Utility_Pvt.Write_Conc_log('remote Index Created ');
1186: Ams_Utility_Pvt.Write_Conc_log('l_index_string = '||l_index_string);
1187:
1188: execute immediate l_index_string;
1189:
1190: Ams_Utility_Pvt.Write_Conc_log('remote Index Created ');
1191: l_counter := l_counter + 1;
1192: end loop;
1193: END IF;
1194: -- SOLIN, END
1191: l_counter := l_counter + 1;
1192: end loop;
1193: END IF;
1194: -- SOLIN, END
1195: Ams_Utility_Pvt.Write_Conc_log('+++++++++++++++++++++End CM program : AMSGMVTP.+++++++++++++++++++++');
1196:
1197: EXCEPTION
1198: WHEN OTHERS THEN
1199: -- hz_common_pub.enable_cont_source_security;
1196:
1197: EXCEPTION
1198: WHEN OTHERS THEN
1199: -- hz_common_pub.enable_cont_source_security;
1200: Ams_Utility_Pvt.Write_Conc_log('Exception in generate_mv_for_template : '||SQLERRM);
1201: errbuf:= substr(SQLERRM,1,254);
1202: retcode:= 2;
1203:
1204: update ams_query_template_all
1211: l_view_exists := NULL;
1212: open c_view_exists;
1213: fetch c_view_exists into l_view_exists;
1214: close c_view_exists;
1215: Ams_Utility_Pvt.Write_Conc_log('l_view_exists = '||l_view_exists);
1216: if l_view_exists = 'Y' then
1217: IF l_remote_flag = 'N'
1218: THEN
1219: l_truncate_string := 'TRUNCATE TABLE '||l_ams_schema||'.'||l_view_name;
1217: IF l_remote_flag = 'N'
1218: THEN
1219: l_truncate_string := 'TRUNCATE TABLE '||l_ams_schema||'.'||l_view_name;
1220: l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
1221: Ams_Utility_Pvt.Write_Conc_log('local l_truncate_string = '||l_truncate_string);
1222: execute immediate l_truncate_string;
1223: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
1224: execute immediate l_drop_string;
1225: ELSE
1219: l_truncate_string := 'TRUNCATE TABLE '||l_ams_schema||'.'||l_view_name;
1220: l_drop_string := 'DROP TABLE '||l_ams_schema||'.'||l_view_name;
1221: Ams_Utility_Pvt.Write_Conc_log('local l_truncate_string = '||l_truncate_string);
1222: execute immediate l_truncate_string;
1223: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
1224: execute immediate l_drop_string;
1225: ELSE
1226: Ams_Utility_Pvt.Write_Conc_log('remote l_remote_truncate_string = '||l_remote_truncate_string);
1227: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_remote_truncate_string||'''); END;';
1222: execute immediate l_truncate_string;
1223: Ams_Utility_Pvt.Write_Conc_log('local l_drop_string = '||l_drop_string);
1224: execute immediate l_drop_string;
1225: ELSE
1226: Ams_Utility_Pvt.Write_Conc_log('remote l_remote_truncate_string = '||l_remote_truncate_string);
1227: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_remote_truncate_string||'''); END;';
1228: Ams_Utility_Pvt.Write_Conc_log('truncate command = '||l_create_remote);
1229: execute immediate l_create_remote;
1230:
1224: execute immediate l_drop_string;
1225: ELSE
1226: Ams_Utility_Pvt.Write_Conc_log('remote l_remote_truncate_string = '||l_remote_truncate_string);
1227: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_remote_truncate_string||'''); END;';
1228: Ams_Utility_Pvt.Write_Conc_log('truncate command = '||l_create_remote);
1229: execute immediate l_create_remote;
1230:
1231: l_drop_string := 'DROP TABLE '||l_view_name;
1232: Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1228: Ams_Utility_Pvt.Write_Conc_log('truncate command = '||l_create_remote);
1229: execute immediate l_create_remote;
1230:
1231: l_drop_string := 'DROP TABLE '||l_view_name;
1232: Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1233: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_drop_string ||'''); END;';
1234: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1235: execute immediate l_create_remote;
1236: END IF;
1230:
1231: l_drop_string := 'DROP TABLE '||l_view_name;
1232: Ams_Utility_Pvt.Write_Conc_log('remote l_drop_string = '||l_drop_string);
1233: l_create_remote := 'BEGIN dbms_utility.exec_ddl_statement'||'@'||l_dblink||'('''|| l_drop_string ||'''); END;';
1234: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1235: execute immediate l_create_remote;
1236: END IF;
1237:
1238: Ams_Utility_Pvt.Write_Conc_log('Table Droped in exception ');
1234: Ams_Utility_Pvt.Write_Conc_log('drop command = '||l_create_remote);
1235: execute immediate l_create_remote;
1236: END IF;
1237:
1238: Ams_Utility_Pvt.Write_Conc_log('Table Droped in exception ');
1239: Ams_Utility_Pvt.Write_Conc_log('This query template is returning lot of data , please modify the query. ');
1240: Ams_Utility_Pvt.Write_Conc_log('To restruct the size use LOV filter conditions.');
1241: end if;
1242: -- SOLIN, end
1235: execute immediate l_create_remote;
1236: END IF;
1237:
1238: Ams_Utility_Pvt.Write_Conc_log('Table Droped in exception ');
1239: Ams_Utility_Pvt.Write_Conc_log('This query template is returning lot of data , please modify the query. ');
1240: Ams_Utility_Pvt.Write_Conc_log('To restruct the size use LOV filter conditions.');
1241: end if;
1242: -- SOLIN, end
1243: raise;
1236: END IF;
1237:
1238: Ams_Utility_Pvt.Write_Conc_log('Table Droped in exception ');
1239: Ams_Utility_Pvt.Write_Conc_log('This query template is returning lot of data , please modify the query. ');
1240: Ams_Utility_Pvt.Write_Conc_log('To restruct the size use LOV filter conditions.');
1241: end if;
1242: -- SOLIN, end
1243: raise;
1244: End generate_mv_for_template;
1341: begin
1342: delete from ams_act_logs where ARC_ACT_LOG_USED_BY =
1343: 'RECL' and ACT_LOG_USED_BY_ID = p_template_id;
1344: -- dbms_output.put_line('IN AMS_List_running_total_pvt----------------------- ');
1345: AMS_Utility_PVT.Create_Log (
1346: x_return_status => l_return_status,
1347: p_arc_log_used_by => 'RECL',
1348: p_log_used_by_id => p_template_id,
1349: p_msg_data => 'Start : Recalculate Process at '||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'),
1354:
1355: -- Get the total # of sqls
1356: l_sql_count := p_sql_string.count;
1357: -- dbms_output.put_line('l_sql_count = '||to_char(l_sql_count));
1358: AMS_Utility_PVT.Create_Log (
1359: x_return_status => l_return_status,
1360: p_arc_log_used_by => 'RECL',
1361: p_log_used_by_id => p_template_id,
1362: p_msg_data => 'l_sql_count := '||l_sql_count,
1369: l_count := 0;
1370: -- Get the total # of attributes for each sql
1371: l_total_param := p_total_parameters(l_i);
1372: -- dbms_output.put_line('l_total_param = '||to_char(l_total_param));
1373: AMS_Utility_PVT.Create_Log (
1374: x_return_status => l_return_status,
1375: p_arc_log_used_by => 'RECL',
1376: p_log_used_by_id => p_template_id,
1377: p_msg_data => 'l_total_param := '||l_total_param||' l_i:'||l_i,
1378: p_msg_type => 'DEBUG');
1379:
1380: cur := DBMS_SQL.OPEN_CURSOR;
1381:
1382: AMS_Utility_PVT.Create_Log (
1383: x_return_status => l_return_status,
1384: p_arc_log_used_by => 'RECL',
1385: p_log_used_by_id => p_template_id,
1386: p_msg_data => substr('SQL_QUERY:= '||p_sql_string(l_i),1,255),
1406:
1407: DBMS_SQL.CLOSE_CURSOR (cur);
1408:
1409: -- dbms_output.put_line('Total from query -- l_total = '|| to_char(l_total));
1410: AMS_Utility_PVT.Create_Log (
1411: x_return_status => l_return_status,
1412: p_arc_log_used_by => 'RECL',
1413: p_log_used_by_id => p_template_id,
1414: p_msg_data => 'l_total := '||l_count,
1415: p_msg_type => 'DEBUG');
1416: if l_sample_pct = 0 then
1417: p_sql_results(l_i) := l_count;
1418: -- dbms_output.put_line('p_sql_results(l_i) = '|| p_sql_results(l_i));
1419: AMS_Utility_PVT.Create_Log (
1420: x_return_status => l_return_status,
1421: p_arc_log_used_by => 'RECL',
1422: p_log_used_by_id => p_template_id,
1423: p_msg_data => 'Result going bcak -- p_sql_results(l_i) := '||p_sql_results(l_i),
1424: p_msg_type => 'DEBUG');
1425: end if;
1426: if l_sample_pct > 0 then
1427: p_sql_results(l_i) := round((l_count / l_sample_pct_recrods) * l_master_ds_rec_numbers) ;
1428: AMS_Utility_PVT.Create_Log (
1429: x_return_status => l_return_status,
1430: p_arc_log_used_by => 'RECL',
1431: p_log_used_by_id => p_template_id,
1432: p_msg_data => 'Results with sampling p_sql_results(l_i) := '||p_sql_results(l_i)
1442: -- l_param_start := l_param_start + 1;
1443: l_total := null;
1444: l_create_string := null;
1445: /*
1446: AMS_Utility_PVT.Create_Log (
1447: x_return_status => l_return_status,
1448: p_arc_log_used_by => 'RECL',
1449: p_log_used_by_id => p_template_id,
1450: p_msg_data => 'Last line l_param_start := '||l_param_start,
1451: p_msg_type => 'DEBUG');
1452: */
1453: -- dbms_output.put_line('l_param_start = '||to_char(l_param_start));
1454: END LOOP;
1455: AMS_Utility_PVT.Create_Log (
1456: x_return_status => l_return_status,
1457: p_arc_log_used_by => 'RECL',
1458: p_log_used_by_id => p_template_id,
1459: p_msg_data => 'End : Recalculate process ',
1461: -- dbms_output.put_line('IN AMS_List_running_total_pvt--------------------------------------- ');
1462:
1463: EXCEPTION
1464: WHEN others THEN
1465: AMS_Utility_PVT.Create_Log (
1466: x_return_status => l_return_status,
1467: p_arc_log_used_by => 'RECL',
1468: p_log_used_by_id => p_template_id,
1469: p_msg_data => 'Error in process_query for recalculate '|| SQLERRM||' '||SQLCODE,
1599: l_tot_loop number := 0;
1600:
1601: begin
1602:
1603: Ams_Utility_Pvt.Write_Conc_log('start gen_lov_filter_for_templmv');
1604: for l_qualify_cond_rec IN c_qualify_conds
1605: loop
1606: begin
1607: Ams_Utility_Pvt.Write_Conc_log('l_cond_count = '||l_cond_count);
1603: Ams_Utility_Pvt.Write_Conc_log('start gen_lov_filter_for_templmv');
1604: for l_qualify_cond_rec IN c_qualify_conds
1605: loop
1606: begin
1607: Ams_Utility_Pvt.Write_Conc_log('l_cond_count = '||l_cond_count);
1608: if l_cond_count > 0 then
1609: -- x_filter_sql := x_filter_sql || 'and (';
1610: x_filter_sql := x_filter_sql || 'OR (';
1611: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1607: Ams_Utility_Pvt.Write_Conc_log('l_cond_count = '||l_cond_count);
1608: if l_cond_count > 0 then
1609: -- x_filter_sql := x_filter_sql || 'and (';
1610: x_filter_sql := x_filter_sql || 'OR (';
1611: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1612: else
1613: x_filter_sql := x_filter_sql || '(';
1614: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1615: end if;
1610: x_filter_sql := x_filter_sql || 'OR (';
1611: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1612: else
1613: x_filter_sql := x_filter_sql || '(';
1614: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1615: end if;
1616: l_cond_count := l_cond_count + 1;
1617:
1618: l_value_count := 0;
1619:
1620: open c_left_oprand (l_qualify_cond_rec.cond_id);
1621: fetch c_left_oprand into l_left_operand_rec;
1622: close c_left_oprand;
1623: Ams_Utility_Pvt.Write_Conc_log('After c_left_oprand list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1624:
1625: l_qa_id := null;
1626: l_object_name := null;
1627: l_object_alias := null;
1636: End loop;
1637: close c_alias;
1638:
1639: l_left_operand := ' UPPER('||l_object_alias || '.' || l_left_operand_rec.attr_name||')';
1640: Ams_Utility_Pvt.Write_Conc_log('l_left_operand = '||l_left_operand);
1641: -- l_left_operand := l_left_operand_rec.table_name || '.' || l_left_operand_rec.attr_name;
1642: open c_operator (l_qualify_cond_rec.cond_id);
1643: loop
1644: fetch c_operator into l_operator;
1642: open c_operator (l_qualify_cond_rec.cond_id);
1643: loop
1644: fetch c_operator into l_operator;
1645: exit when c_operator%notfound;
1646: Ams_Utility_Pvt.Write_Conc_log('After c_operator-- l_operator = '||l_operator);
1647:
1648: if l_operator in ('IS', 'IN', 'LIKE','=') then
1649: l_operator := ' LIKE ';
1650: l_cond_conn := ' or ';
1656: l_operator := ' LIKE ';
1657: l_cond_conn := ' or ';
1658: end if;
1659: close c_operator;
1660: Ams_Utility_Pvt.Write_Conc_log('After c_operator LOOP -- l_operator = '||l_operator);
1661: Ams_Utility_Pvt.Write_Conc_log('After c_operator LOOP -- l_cond_conn= '||l_cond_conn);
1662:
1663:
1664: open c_lov_values (l_qualify_cond_rec.cond_id);
1657: l_cond_conn := ' or ';
1658: end if;
1659: close c_operator;
1660: Ams_Utility_Pvt.Write_Conc_log('After c_operator LOOP -- l_operator = '||l_operator);
1661: Ams_Utility_Pvt.Write_Conc_log('After c_operator LOOP -- l_cond_conn= '||l_cond_conn);
1662:
1663:
1664: open c_lov_values (l_qualify_cond_rec.cond_id);
1665: loop
1668: -- l_count := l_count + 1;
1669: l_tot_count := l_tot_count + 1;
1670: end loop;
1671: close c_lov_values;
1672: Ams_Utility_Pvt.Write_Conc_log('***** After c_lov_values LOOP -- l_count = '||l_count);
1673: Ams_Utility_Pvt.Write_Conc_log('***** After c_lov_values LOOP -- l_tot_count = '||l_tot_count);
1674:
1675: -- if l_count = 0 then
1676: if l_tot_count = 0 then
1669: l_tot_count := l_tot_count + 1;
1670: end loop;
1671: close c_lov_values;
1672: Ams_Utility_Pvt.Write_Conc_log('***** After c_lov_values LOOP -- l_count = '||l_count);
1673: Ams_Utility_Pvt.Write_Conc_log('***** After c_lov_values LOOP -- l_tot_count = '||l_tot_count);
1674:
1675: -- if l_count = 0 then
1676: if l_tot_count = 0 then
1677: Ams_Utility_Pvt.Write_Conc_log('After c_lov_values --SELECT ALL is used for lov values ');
1673: Ams_Utility_Pvt.Write_Conc_log('***** After c_lov_values LOOP -- l_tot_count = '||l_tot_count);
1674:
1675: -- if l_count = 0 then
1676: if l_tot_count = 0 then
1677: Ams_Utility_Pvt.Write_Conc_log('After c_lov_values --SELECT ALL is used for lov values ');
1678: open c_lov_type(l_left_operand_rec.list_source_type_id,l_left_operand_rec.attr_name);
1679: fetch c_lov_type into l_lov_type;
1680: close c_lov_type;
1681: Ams_Utility_Pvt.Write_Conc_log('LOV type -- l_lov_type = '||l_lov_type);
1677: Ams_Utility_Pvt.Write_Conc_log('After c_lov_values --SELECT ALL is used for lov values ');
1678: open c_lov_type(l_left_operand_rec.list_source_type_id,l_left_operand_rec.attr_name);
1679: fetch c_lov_type into l_lov_type;
1680: close c_lov_type;
1681: Ams_Utility_Pvt.Write_Conc_log('LOV type -- l_lov_type = '||l_lov_type);
1682: if l_lov_type = 'SQL' then
1683: open c_lov_sql(l_left_operand_rec.list_source_type_id,l_left_operand_rec.attr_name);
1684: fetch c_lov_sql into l_lov_sql;
1685: close c_lov_sql;
1682: if l_lov_type = 'SQL' then
1683: open c_lov_sql(l_left_operand_rec.list_source_type_id,l_left_operand_rec.attr_name);
1684: fetch c_lov_sql into l_lov_sql;
1685: close c_lov_sql;
1686: Ams_Utility_Pvt.Write_Conc_log(' l_lov_sql = '||l_lov_sql);
1687: l_position := instrb(upper(l_lov_sql),'FROM');
1688: Ams_Utility_Pvt.Write_Conc_log(' l_position = '||l_position);
1689: l_final_lov_sql := substr(l_lov_sql,1,l_position - 1)||' BULK COLLECT INTO :1 ,:2 '||substr(l_lov_sql,l_position);
1690: Ams_Utility_Pvt.Write_Conc_log(' l_final_lov_sql = '||l_final_lov_sql);
1684: fetch c_lov_sql into l_lov_sql;
1685: close c_lov_sql;
1686: Ams_Utility_Pvt.Write_Conc_log(' l_lov_sql = '||l_lov_sql);
1687: l_position := instrb(upper(l_lov_sql),'FROM');
1688: Ams_Utility_Pvt.Write_Conc_log(' l_position = '||l_position);
1689: l_final_lov_sql := substr(l_lov_sql,1,l_position - 1)||' BULK COLLECT INTO :1 ,:2 '||substr(l_lov_sql,l_position);
1690: Ams_Utility_Pvt.Write_Conc_log(' l_final_lov_sql = '||l_final_lov_sql);
1691: EXECUTE IMMEDIATE 'BEGIN '||l_final_lov_sql||' ; END; ' USING OUT l_code_tbl ,OUT l_meaning_tbl;
1692: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1686: Ams_Utility_Pvt.Write_Conc_log(' l_lov_sql = '||l_lov_sql);
1687: l_position := instrb(upper(l_lov_sql),'FROM');
1688: Ams_Utility_Pvt.Write_Conc_log(' l_position = '||l_position);
1689: l_final_lov_sql := substr(l_lov_sql,1,l_position - 1)||' BULK COLLECT INTO :1 ,:2 '||substr(l_lov_sql,l_position);
1690: Ams_Utility_Pvt.Write_Conc_log(' l_final_lov_sql = '||l_final_lov_sql);
1691: EXECUTE IMMEDIATE 'BEGIN '||l_final_lov_sql||' ; END; ' USING OUT l_code_tbl ,OUT l_meaning_tbl;
1692: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1693: end if;
1694: if l_lov_type = 'USER' then
1688: Ams_Utility_Pvt.Write_Conc_log(' l_position = '||l_position);
1689: l_final_lov_sql := substr(l_lov_sql,1,l_position - 1)||' BULK COLLECT INTO :1 ,:2 '||substr(l_lov_sql,l_position);
1690: Ams_Utility_Pvt.Write_Conc_log(' l_final_lov_sql = '||l_final_lov_sql);
1691: EXECUTE IMMEDIATE 'BEGIN '||l_final_lov_sql||' ; END; ' USING OUT l_code_tbl ,OUT l_meaning_tbl;
1692: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1693: end if;
1694: if l_lov_type = 'USER' then
1695: Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1696: Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.attr_name = '||l_left_operand_rec.attr_name);
1691: EXECUTE IMMEDIATE 'BEGIN '||l_final_lov_sql||' ; END; ' USING OUT l_code_tbl ,OUT l_meaning_tbl;
1692: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1693: end if;
1694: if l_lov_type = 'USER' then
1695: Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1696: Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.attr_name = '||l_left_operand_rec.attr_name);
1697:
1698: EXECUTE IMMEDIATE ' BEGIN select valb.value_code '||' BULK COLLECT INTO :1 '||
1699: ' from ams_list_src_fields flds ,ams_attb_lov_b lovb, ams_attb_lov_values_b valb'||
1692: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1693: end if;
1694: if l_lov_type = 'USER' then
1695: Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1696: Ams_Utility_Pvt.Write_Conc_log(' (l_left_operand_rec.attr_name = '||l_left_operand_rec.attr_name);
1697:
1698: EXECUTE IMMEDIATE ' BEGIN select valb.value_code '||' BULK COLLECT INTO :1 '||
1699: ' from ams_list_src_fields flds ,ams_attb_lov_b lovb, ams_attb_lov_values_b valb'||
1700: ' where flds.LIST_SOURCE_TYPE_ID = :2'||
1701: ' and flds.SOURCE_COLUMN_NAME = :3 '||
1702: ' and flds.attb_lov_id = lovb.attb_lov_id and lovb.CREATION_TYPE = '||''''|| 'USER'||''''||
1703: ' and lovb.attb_lov_id = valb.attb_lov_id ; END; ' USING OUT l_code_tbl, IN l_left_operand_rec.list_source_type_id, l_left_operand_rec.attr_name;
1704: end if;
1705: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1706: for i in 1 .. l_code_tbl.count
1707: loop
1708: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl(i) = '||l_code_tbl(i));
1709: end loop;
1704: end if;
1705: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl.count = '||l_code_tbl.count);
1706: for i in 1 .. l_code_tbl.count
1707: loop
1708: Ams_Utility_Pvt.Write_Conc_log(' l_code_tbl(i) = '||l_code_tbl(i));
1709: end loop;
1710: -- [[[[[[[[[[[[[[[[[[
1711: l_tot_loop := 0;
1712: for i in 1 .. l_code_tbl.count
1710: -- [[[[[[[[[[[[[[[[[[
1711: l_tot_loop := 0;
1712: for i in 1 .. l_code_tbl.count
1713: loop
1714: -- Ams_Utility_Pvt.Write_Conc_log(' coming in l_code_tbl.count LOOP ');
1715: l_tot_loop := l_tot_loop + 1;
1716: begin
1717: x_string_params (l_param_count + 1) := l_code_tbl(i);
1718: -- dbms_output.put_line('l_param_count = '|| l_param_count);
1717: x_string_params (l_param_count + 1) := l_code_tbl(i);
1718: -- dbms_output.put_line('l_param_count = '|| l_param_count);
1719: -- if l_param_count = l_count then
1720: -- if l_param_count+1 = l_code_tbl.count then
1721: Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1722: Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop);
1723: if (l_param_count+1 = l_code_tbl.count + l_p_count) or (l_tot_count = l_tot_loop) then
1724: Ams_Utility_Pvt.Write_Conc_log(' l_param_count+1 '||to_char(l_param_count+1));
1725: l_or := ' ';
1718: -- dbms_output.put_line('l_param_count = '|| l_param_count);
1719: -- if l_param_count = l_count then
1720: -- if l_param_count+1 = l_code_tbl.count then
1721: Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1722: Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop);
1723: if (l_param_count+1 = l_code_tbl.count + l_p_count) or (l_tot_count = l_tot_loop) then
1724: Ams_Utility_Pvt.Write_Conc_log(' l_param_count+1 '||to_char(l_param_count+1));
1725: l_or := ' ';
1726: else
1720: -- if l_param_count+1 = l_code_tbl.count then
1721: Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1722: Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop);
1723: if (l_param_count+1 = l_code_tbl.count + l_p_count) or (l_tot_count = l_tot_loop) then
1724: Ams_Utility_Pvt.Write_Conc_log(' l_param_count+1 '||to_char(l_param_count+1));
1725: l_or := ' ';
1726: else
1727: l_or := ' OR ';
1728: end if;
1740: -- dbms_output.put_line('l_param_count = '|| l_param_count);
1741: end;
1742: end loop;
1743: l_p_count := l_param_count;
1744: Ams_Utility_Pvt.Write_Conc_log('x_num_params = '||x_num_params);
1745: -- [[[[[[[[[[[[[[[[
1746: -- l_param_remove_count := l_param_remove_count + l_param_count;
1747: -- l_param_remove_count := l_param_remove_count + l_p_count ;
1748: l_param_remove_count := l_p_count ;
1745: -- [[[[[[[[[[[[[[[[
1746: -- l_param_remove_count := l_param_remove_count + l_param_count;
1747: -- l_param_remove_count := l_param_remove_count + l_p_count ;
1748: l_param_remove_count := l_p_count ;
1749: Ams_Utility_Pvt.Write_Conc_log('in 000 l_param_remove_count = '||l_param_remove_count);
1750: end if; -- l_count = 0
1751:
1752: -- if l_count > 0 then
1753: if l_tot_count > 0 then
1758: l_tot_loop := l_tot_loop +1;
1759: x_string_params (l_param_count + 1) := l_lov_values_rec.lov_value;
1760: -- if l_param_count = l_count then
1761: -- if l_param_count+1 - l_param_remove_count = l_count then
1762: Ams_Utility_Pvt.Write_Conc_log(' l_param_remove_count = '||l_param_remove_count);
1763: Ams_Utility_Pvt.Write_Conc_log(' l_param_count = '||l_param_count);
1764: Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1765: Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop );
1766: -- if l_param_count+1 - l_param_remove_count = l_count then
1759: x_string_params (l_param_count + 1) := l_lov_values_rec.lov_value;
1760: -- if l_param_count = l_count then
1761: -- if l_param_count+1 - l_param_remove_count = l_count then
1762: Ams_Utility_Pvt.Write_Conc_log(' l_param_remove_count = '||l_param_remove_count);
1763: Ams_Utility_Pvt.Write_Conc_log(' l_param_count = '||l_param_count);
1764: Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1765: Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop );
1766: -- if l_param_count+1 - l_param_remove_count = l_count then
1767: if (l_param_count+1 - l_param_remove_count = l_tot_count) or (l_tot_count = l_tot_loop) then
1760: -- if l_param_count = l_count then
1761: -- if l_param_count+1 - l_param_remove_count = l_count then
1762: Ams_Utility_Pvt.Write_Conc_log(' l_param_remove_count = '||l_param_remove_count);
1763: Ams_Utility_Pvt.Write_Conc_log(' l_param_count = '||l_param_count);
1764: Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1765: Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop );
1766: -- if l_param_count+1 - l_param_remove_count = l_count then
1767: if (l_param_count+1 - l_param_remove_count = l_tot_count) or (l_tot_count = l_tot_loop) then
1768: l_or := ' ';
1761: -- if l_param_count+1 - l_param_remove_count = l_count then
1762: Ams_Utility_Pvt.Write_Conc_log(' l_param_remove_count = '||l_param_remove_count);
1763: Ams_Utility_Pvt.Write_Conc_log(' l_param_count = '||l_param_count);
1764: Ams_Utility_Pvt.Write_Conc_log(' l_p_count = '||l_p_count);
1765: Ams_Utility_Pvt.Write_Conc_log(' l_tot_loop = '||l_tot_loop );
1766: -- if l_param_count+1 - l_param_remove_count = l_count then
1767: if (l_param_count+1 - l_param_remove_count = l_tot_count) or (l_tot_count = l_tot_loop) then
1768: l_or := ' ';
1769: else
1791: end;
1792: end loop; -- c_qualify_conds
1793: x_filter_sql := '('||x_filter_sql || ')';
1794: x_num_params := l_param_count ;
1795: Ams_Utility_Pvt.Write_Conc_log('Final x_num_params = '||x_num_params);
1796: Ams_Utility_Pvt.Write_Conc_log('end gen_lov_filter_for_templmv');
1797:
1798: EXCEPTION
1799: WHEN OTHERS THEN
1792: end loop; -- c_qualify_conds
1793: x_filter_sql := '('||x_filter_sql || ')';
1794: x_num_params := l_param_count ;
1795: Ams_Utility_Pvt.Write_Conc_log('Final x_num_params = '||x_num_params);
1796: Ams_Utility_Pvt.Write_Conc_log('end gen_lov_filter_for_templmv');
1797:
1798: EXCEPTION
1799: WHEN OTHERS THEN
1800: Ams_Utility_Pvt.Write_Conc_log('Exception in gen_lov_filter_for_templmv : '||SQLERRM);
1796: Ams_Utility_Pvt.Write_Conc_log('end gen_lov_filter_for_templmv');
1797:
1798: EXCEPTION
1799: WHEN OTHERS THEN
1800: Ams_Utility_Pvt.Write_Conc_log('Exception in gen_lov_filter_for_templmv : '||SQLERRM);
1801: -- errbuf:= substr(SQLERRM,1,254);
1802: --retcode:= 2;
1803: raise;
1804: end;
1887: l_value1 varchar2(2000);
1888: l_value2 varchar2(2000);
1889:
1890: BEGIN
1891: Ams_Utility_Pvt.Write_Conc_log('Start gen_constant_filter');
1892:
1893: l_num_params := x_num_params;
1894: l_cond_count := x_num_params;
1895:
1900: --Loop thru each mandatory condition having one operator
1901: --and a value associated with it
1902: FOR cond_rec IN C_query_cond_main LOOP
1903:
1904: Ams_Utility_Pvt.Write_Conc_log('Processing cond_id: '||cond_rec.cond_id);
1905:
1906: --resetting variables
1907: l_value1 := null;
1908: l_value2 := null;
1912: -------
1913: --start building the filter_sql
1914: if l_cond_count > 0 then
1915: x_filter_sql := x_filter_sql || ' AND (';
1916: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1917: else
1918: x_filter_sql := x_filter_sql || '(';
1919: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1920: end if;
1915: x_filter_sql := x_filter_sql || ' AND (';
1916: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1917: else
1918: x_filter_sql := x_filter_sql || '(';
1919: Ams_Utility_Pvt.Write_Conc_log('x_filter_sql = '||x_filter_sql);
1920: end if;
1921:
1922: l_cond_count := l_cond_count + 1;
1923: -------
1924: --Get the letf operand value
1925: open C_left_oprand (cond_rec.cond_id);
1926: fetch C_left_oprand into l_left_operand_rec;
1927: close C_left_oprand;
1928: Ams_Utility_Pvt.Write_Conc_log('Left Operand: '||l_left_operand_rec.table_name||'.'||l_left_operand_rec.attr_name
1929: ||' list_source_type_id = '||l_left_operand_rec.list_source_type_id);
1930:
1931: l_qa_id := null;
1932: l_object_name := null;
1943: END LOOP;
1944: CLOSE C_alias;
1945:
1946: l_left_operand := 'UPPER('||l_object_alias || '.' || l_left_operand_rec.attr_name||') ';
1947: Ams_Utility_Pvt.Write_Conc_log('l_left_operand = '||l_left_operand);
1948:
1949: -------
1950: --Get Operator
1951: OPEN C_operator(cond_rec.cond_id);
1951: OPEN C_operator(cond_rec.cond_id);
1952: FETCH C_operator INTO l_operator, l_dummy;
1953: CLOSE C_operator;
1954:
1955: Ams_Utility_Pvt.Write_Conc_log('Operator = '||l_operator);
1956:
1957: IF l_operator is not null THEN
1958:
1959: IF l_operator <> 'IN' THEN
1959: IF l_operator <> 'IN' THEN
1960: OPEN C_display_values(cond_rec.cond_id);
1961: FETCH C_display_values INTO l_value1;
1962: CLOSE C_display_values;
1963: Ams_Utility_Pvt.Write_Conc_log('Value1 = '||l_value1);
1964: END IF;
1965:
1966: IF l_operator = 'BETWEEN' THEN
1967:
1967:
1968: OPEN C_display_value2(cond_rec.cond_id);
1969: FETCH C_display_value2 INTO l_value2;
1970: CLOSE C_display_value2;
1971: Ams_Utility_Pvt.Write_Conc_log('Value2 = '||l_value2);
1972:
1973: END IF;
1974: ELSE --if l_operator is not null
1975: Ams_Utility_Pvt.Write_Conc_log('** Operator is null.. **');
1971: Ams_Utility_Pvt.Write_Conc_log('Value2 = '||l_value2);
1972:
1973: END IF;
1974: ELSE --if l_operator is not null
1975: Ams_Utility_Pvt.Write_Conc_log('** Operator is null.. **');
1976: END IF;
1977:
1978: --Build filter clause
1979: -------
1994: end if;
1995:
1996: END LOOP;
1997: x_num_params := l_num_params;
1998: Ams_Utility_Pvt.Write_Conc_log('Final param count: '||to_char(x_num_params));
1999: Ams_Utility_Pvt.Write_Conc_log('Final filter sql : '||x_filter_sql);
2000: Ams_Utility_Pvt.Write_Conc_log('End gen_constant_filter.');
2001:
2002: EXCEPTION
1995:
1996: END LOOP;
1997: x_num_params := l_num_params;
1998: Ams_Utility_Pvt.Write_Conc_log('Final param count: '||to_char(x_num_params));
1999: Ams_Utility_Pvt.Write_Conc_log('Final filter sql : '||x_filter_sql);
2000: Ams_Utility_Pvt.Write_Conc_log('End gen_constant_filter.');
2001:
2002: EXCEPTION
2003: WHEN OTHERS THEN
1996: END LOOP;
1997: x_num_params := l_num_params;
1998: Ams_Utility_Pvt.Write_Conc_log('Final param count: '||to_char(x_num_params));
1999: Ams_Utility_Pvt.Write_Conc_log('Final filter sql : '||x_filter_sql);
2000: Ams_Utility_Pvt.Write_Conc_log('End gen_constant_filter.');
2001:
2002: EXCEPTION
2003: WHEN OTHERS THEN
2004: Ams_Utility_Pvt.Write_Conc_log('Exception in gen_constant_filter : '||SQLERRM);
2000: Ams_Utility_Pvt.Write_Conc_log('End gen_constant_filter.');
2001:
2002: EXCEPTION
2003: WHEN OTHERS THEN
2004: Ams_Utility_Pvt.Write_Conc_log('Exception in gen_constant_filter : '||SQLERRM);
2005: Raise;
2006:
2007: END gen_constant_filter;
2008: ------------------------------------