[Home] [Help]
695: ELSE
696: h_many_methods := TRUE;
697: END IF;
698:
699: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
700: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
701: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
702: END IF;
703: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
697: END IF;
698:
699: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
700: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
701: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
702: END IF;
703: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
704: IF x_parallel_jobs = 'Y' THEN
705: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
699: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
700: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
701: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
702: END IF;
703: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
704: IF x_parallel_jobs = 'Y' THEN
705: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
706: END IF;
707: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
701: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
702: END IF;
703: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
704: IF x_parallel_jobs = 'Y' THEN
705: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
706: END IF;
707: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
708: FOR h_i IN 1..x_num_key_columns LOOP
709: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
703: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
704: IF x_parallel_jobs = 'Y' THEN
705: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
706: END IF;
707: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
708: FOR h_i IN 1..x_num_key_columns LOOP
709: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
710: END LOOP;
711: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
705: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
706: END IF;
707: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
708: FOR h_i IN 1..x_num_key_columns LOOP
709: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
710: END LOOP;
711: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
712: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
713: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
707: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
708: FOR h_i IN 1..x_num_key_columns LOOP
709: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
710: END LOOP;
711: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
712: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
713: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
714: END IF;
715: FOR h_i IN 1..x_num_data_columns LOOP
709: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
710: END LOOP;
711: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
712: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
713: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
714: END IF;
715: FOR h_i IN 1..x_num_data_columns LOOP
716: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
717: END LOOP;
712: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
713: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
714: END IF;
715: FOR h_i IN 1..x_num_data_columns LOOP
716: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
717: END LOOP;
718: IF x_aw_flag THEN
719: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
720: END IF;
715: FOR h_i IN 1..x_num_data_columns LOOP
716: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
717: END LOOP;
718: IF x_aw_flag THEN
719: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
720: END IF;
721: bsc_dbgen_utils.add_string(h_sql, ')');
722: IF h_many_methods THEN
723: bsc_dbgen_utils.add_string(h_sql, ' select');
717: END LOOP;
718: IF x_aw_flag THEN
719: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
720: END IF;
721: bsc_dbgen_utils.add_string(h_sql, ')');
722: IF h_many_methods THEN
723: bsc_dbgen_utils.add_string(h_sql, ' select');
724: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
725: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
719: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
720: END IF;
721: bsc_dbgen_utils.add_string(h_sql, ')');
722: IF h_many_methods THEN
723: bsc_dbgen_utils.add_string(h_sql, ' select');
724: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
725: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
726: END IF;
727: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
721: bsc_dbgen_utils.add_string(h_sql, ')');
722: IF h_many_methods THEN
723: bsc_dbgen_utils.add_string(h_sql, ' select');
724: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
725: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
726: END IF;
727: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
728: FOR h_i IN 1..x_num_key_columns LOOP
729: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
723: bsc_dbgen_utils.add_string(h_sql, ' select');
724: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
725: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
726: END IF;
727: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
728: FOR h_i IN 1..x_num_key_columns LOOP
729: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
730: END LOOP;
731: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
725: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
726: END IF;
727: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
728: FOR h_i IN 1..x_num_key_columns LOOP
729: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
730: END LOOP;
731: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
732: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
733: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
727: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
728: FOR h_i IN 1..x_num_key_columns LOOP
729: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
730: END LOOP;
731: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
732: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
733: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
734: END IF;
735: FOR h_i IN 1..x_num_data_columns LOOP
729: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
730: END LOOP;
731: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
732: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
733: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
734: END IF;
735: FOR h_i IN 1..x_num_data_columns LOOP
736: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||')');
737: END LOOP;
732: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
733: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
734: END IF;
735: FOR h_i IN 1..x_num_data_columns LOOP
736: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||')');
737: END LOOP;
738: IF x_aw_flag THEN
739: bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
740: END IF;
735: FOR h_i IN 1..x_num_data_columns LOOP
736: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||')');
737: END LOOP;
738: IF x_aw_flag THEN
739: bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
740: END IF;
741: bsc_dbgen_utils.add_string(h_sql, ' from (');
742: END IF;
743: IF h_num_perf_cols > 0 THEN
737: END LOOP;
738: IF x_aw_flag THEN
739: bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
740: END IF;
741: bsc_dbgen_utils.add_string(h_sql, ' from (');
742: END IF;
743: IF h_num_perf_cols > 0 THEN
744: -- all measures with this projection method goes here other goes with null
745: h_num_per_back := 3;
750: IF h_per_ini <= 0 THEN
751: h_per_ini := 1;
752: END IF;
753: END IF;
754: bsc_dbgen_utils.add_string(h_sql, ' select');
755: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
756: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(tp) parallel(tr) parallel(pp) */');
757: END IF;
758: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
752: END IF;
753: END IF;
754: bsc_dbgen_utils.add_string(h_sql, ' select');
755: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
756: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(tp) parallel(tr) parallel(pp) */');
757: END IF;
758: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
759: FOR h_i IN 1..x_num_key_columns LOOP
760: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
754: bsc_dbgen_utils.add_string(h_sql, ' select');
755: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
756: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(tp) parallel(tr) parallel(pp) */');
757: END IF;
758: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
759: FOR h_i IN 1..x_num_key_columns LOOP
760: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
761: END LOOP;
762: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
756: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(tp) parallel(tr) parallel(pp) */');
757: END IF;
758: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
759: FOR h_i IN 1..x_num_key_columns LOOP
760: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
761: END LOOP;
762: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
763: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
764: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
758: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
759: FOR h_i IN 1..x_num_key_columns LOOP
760: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
761: END LOOP;
762: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
763: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
764: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
765: END IF;
766: FOR h_i IN 1..x_num_data_columns LOOP
760: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
761: END LOOP;
762: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
763: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
764: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
765: END IF;
766: FOR h_i IN 1..x_num_data_columns LOOP
767: IF x_data_proj_methods(h_i) = 3 THEN
768: bsc_dbgen_utils.add_string(h_sql,
764: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
765: END IF;
766: FOR h_i IN 1..x_num_data_columns LOOP
767: IF x_data_proj_methods(h_i) = 3 THEN
768: bsc_dbgen_utils.add_string(h_sql,
769: ', case when (tr.tr'||h_i||'>0 and tp.tp'||h_i||'>0) and ((decode(tp.tp'||h_i||',0,'||
770: '0,tr.tr'||h_i||'/tp.tp'||h_i||')*pp.p'||h_i||')>(2*pp.p'||h_i||'))'||
771: ' then 2*pp.p'||h_i||
772: ' when tr.tr'||h_i||'>0 and tp.tp'||h_i||'>0'||
786: ' when ((tr.tr'||h_i||'=0 or (tr.tr'||h_i||' is null)) and (tp.tp'||h_i||'=0 or'||
787: ' (tp.tp'||h_i||' is null)))'||
788: ' then pp.p'||h_i||' end '||x_data_columns(h_i));
789: ELSE
790: bsc_dbgen_utils.add_string(h_sql, ', null '||x_data_columns(h_i));
791: END IF;
792: END LOOP;
793: IF x_aw_flag THEN
794: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
790: bsc_dbgen_utils.add_string(h_sql, ', null '||x_data_columns(h_i));
791: END IF;
792: END LOOP;
793: IF x_aw_flag THEN
794: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
795: END IF;
796: bsc_dbgen_utils.add_string(h_sql, ' from (select');
797: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
798: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
792: END LOOP;
793: IF x_aw_flag THEN
794: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
795: END IF;
796: bsc_dbgen_utils.add_string(h_sql, ' from (select');
797: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
798: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
799: END IF;
800: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
794: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
795: END IF;
796: bsc_dbgen_utils.add_string(h_sql, ' from (select');
797: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
798: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
799: END IF;
800: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
801: FOR h_i IN 1..x_num_key_columns LOOP
802: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
796: bsc_dbgen_utils.add_string(h_sql, ' from (select');
797: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
798: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
799: END IF;
800: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
801: FOR h_i IN 1..x_num_key_columns LOOP
802: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
803: END LOOP;
804: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year, 0 type, bsc_tmp_all_periods.period');
798: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
799: END IF;
800: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
801: FOR h_i IN 1..x_num_key_columns LOOP
802: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
803: END LOOP;
804: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year, 0 type, bsc_tmp_all_periods.period');
805: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
806: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
800: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
801: FOR h_i IN 1..x_num_key_columns LOOP
802: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
803: END LOOP;
804: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year, 0 type, bsc_tmp_all_periods.period');
805: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
806: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
807: END IF;
808: bsc_dbgen_utils.add_string(h_sql, ' from (select');
802: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
803: END LOOP;
804: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year, 0 type, bsc_tmp_all_periods.period');
805: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
806: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
807: END IF;
808: bsc_dbgen_utils.add_string(h_sql, ' from (select');
809: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
810: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
804: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year, 0 type, bsc_tmp_all_periods.period');
805: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
806: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
807: END IF;
808: bsc_dbgen_utils.add_string(h_sql, ' from (select');
809: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
810: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
811: END IF;
812: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
806: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
807: END IF;
808: bsc_dbgen_utils.add_string(h_sql, ' from (select');
809: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
810: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
811: END IF;
812: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
813: FOR h_i IN 1..x_num_key_columns LOOP
814: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
808: bsc_dbgen_utils.add_string(h_sql, ' from (select');
809: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
810: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
811: END IF;
812: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
813: FOR h_i IN 1..x_num_key_columns LOOP
814: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
815: END LOOP;
816: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
810: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
811: END IF;
812: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
813: FOR h_i IN 1..x_num_key_columns LOOP
814: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
815: END LOOP;
816: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
817: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
818: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
812: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
813: FOR h_i IN 1..x_num_key_columns LOOP
814: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
815: END LOOP;
816: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
817: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
818: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
819: IF x_parallel_jobs = 'Y' THEN
820: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
814: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
815: END LOOP;
816: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
817: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
818: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
819: IF x_parallel_jobs = 'Y' THEN
820: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
821: END IF;
822: ELSE
816: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
817: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
818: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
819: IF x_parallel_jobs = 'Y' THEN
820: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
821: END IF;
822: ELSE
823: IF x_parallel_jobs = 'Y' THEN
824: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
820: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
821: END IF;
822: ELSE
823: IF x_parallel_jobs = 'Y' THEN
824: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
825: END IF;
826: END IF;
827: bsc_dbgen_utils.add_string(h_sql, ') a,');
828: IF h_yearly_flag <> 1 THEN
823: IF x_parallel_jobs = 'Y' THEN
824: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
825: END IF;
826: END IF;
827: bsc_dbgen_utils.add_string(h_sql, ') a,');
828: IF h_yearly_flag <> 1 THEN
829: bsc_dbgen_utils.add_string(h_sql, ' (select');
830: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
831: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
825: END IF;
826: END IF;
827: bsc_dbgen_utils.add_string(h_sql, ') a,');
828: IF h_yearly_flag <> 1 THEN
829: bsc_dbgen_utils.add_string(h_sql, ' (select');
830: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
831: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
832: END IF;
833: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
827: bsc_dbgen_utils.add_string(h_sql, ') a,');
828: IF h_yearly_flag <> 1 THEN
829: bsc_dbgen_utils.add_string(h_sql, ' (select');
830: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
831: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
832: END IF;
833: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
834: ' from bsc_db_calendar'||
835: ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
829: bsc_dbgen_utils.add_string(h_sql, ' (select');
830: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
831: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
832: END IF;
833: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
834: ' from bsc_db_calendar'||
835: ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
836: ' and '||h_per_column||' > '||x_current_period||
837: ' ) bsc_tmp_all_periods');
835: ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
836: ' and '||h_per_column||' > '||x_current_period||
837: ' ) bsc_tmp_all_periods');
838: ELSE
839: bsc_dbgen_utils.add_string(h_sql, ' (select');
840: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
841: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
842: END IF;
843: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
837: ' ) bsc_tmp_all_periods');
838: ELSE
839: bsc_dbgen_utils.add_string(h_sql, ' (select');
840: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
841: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
842: END IF;
843: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
844: ' from bsc_db_calendar'||
845: ' where year > '||x_current_fy||' and year <= '||h_max_year||
839: bsc_dbgen_utils.add_string(h_sql, ' (select');
840: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
841: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
842: END IF;
843: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
844: ' from bsc_db_calendar'||
845: ' where year > '||x_current_fy||' and year <= '||h_max_year||
846: ' and calendar_id = '||x_calendar_id||
847: ' ) bsc_tmp_all_periods');
845: ' where year > '||x_current_fy||' and year <= '||h_max_year||
846: ' and calendar_id = '||x_calendar_id||
847: ' ) bsc_tmp_all_periods');
848: END IF;
849: bsc_dbgen_utils.add_string(h_sql, ' ) p,'||
850: ' (select ');
851: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
852: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
853: END IF;
848: END IF;
849: bsc_dbgen_utils.add_string(h_sql, ' ) p,'||
850: ' (select ');
851: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
852: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
853: END IF;
854: FOR h_i IN 1..x_num_key_columns LOOP
855: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
856: END LOOP;
851: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
852: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
853: END IF;
854: FOR h_i IN 1..x_num_key_columns LOOP
855: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
856: END LOOP;
857: bsc_dbgen_utils.add_string(h_sql, '0 type');
858: FOR h_i IN 1..x_num_data_columns LOOP
859: IF x_data_proj_methods(h_i) = 3 THEN
853: END IF;
854: FOR h_i IN 1..x_num_key_columns LOOP
855: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
856: END LOOP;
857: bsc_dbgen_utils.add_string(h_sql, '0 type');
858: FOR h_i IN 1..x_num_data_columns LOOP
859: IF x_data_proj_methods(h_i) = 3 THEN
860: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tp'||h_i);
861: END IF;
856: END LOOP;
857: bsc_dbgen_utils.add_string(h_sql, '0 type');
858: FOR h_i IN 1..x_num_data_columns LOOP
859: IF x_data_proj_methods(h_i) = 3 THEN
860: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tp'||h_i);
861: END IF;
862: END LOOP;
863: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
864: IF h_yearly_flag <> 1 THEN
859: IF x_data_proj_methods(h_i) = 3 THEN
860: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tp'||h_i);
861: END IF;
862: END LOOP;
863: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
864: IF h_yearly_flag <> 1 THEN
865: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and'||
866: ' period >= '||h_per_ini||' and period <= '||x_current_period);
867: ELSE
861: END IF;
862: END LOOP;
863: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
864: IF h_yearly_flag <> 1 THEN
865: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and'||
866: ' period >= '||h_per_ini||' and period <= '||x_current_period);
867: ELSE
868: bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 1');
869: END IF;
864: IF h_yearly_flag <> 1 THEN
865: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and'||
866: ' period >= '||h_per_ini||' and period <= '||x_current_period);
867: ELSE
868: bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 1');
869: END IF;
870: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
871: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
872: END IF;
867: ELSE
868: bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 1');
869: END IF;
870: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
871: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
872: END IF;
873: IF x_parallel_jobs = 'Y' THEN
874: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
875: END IF;
870: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
871: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
872: END IF;
873: IF x_parallel_jobs = 'Y' THEN
874: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
875: END IF;
876: IF x_num_key_columns > 0 THEN
877: bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
878: FOR h_i IN 2..x_num_key_columns LOOP
873: IF x_parallel_jobs = 'Y' THEN
874: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
875: END IF;
876: IF x_num_key_columns > 0 THEN
877: bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
878: FOR h_i IN 2..x_num_key_columns LOOP
879: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
880: END LOOP;
881: END IF;
875: END IF;
876: IF x_num_key_columns > 0 THEN
877: bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
878: FOR h_i IN 2..x_num_key_columns LOOP
879: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
880: END LOOP;
881: END IF;
882: bsc_dbgen_utils.add_string(h_sql, ') tp,'||
883: ' (select ');
878: FOR h_i IN 2..x_num_key_columns LOOP
879: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
880: END LOOP;
881: END IF;
882: bsc_dbgen_utils.add_string(h_sql, ') tp,'||
883: ' (select ');
884: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
885: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
886: END IF;
881: END IF;
882: bsc_dbgen_utils.add_string(h_sql, ') tp,'||
883: ' (select ');
884: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
885: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
886: END IF;
887: FOR h_i IN 1..x_num_key_columns LOOP
888: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
889: END LOOP;
884: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
885: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
886: END IF;
887: FOR h_i IN 1..x_num_key_columns LOOP
888: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
889: END LOOP;
890: bsc_dbgen_utils.add_string(h_sql, '0 type');
891: FOR h_i IN 1..x_num_data_columns LOOP
892: IF x_data_proj_methods(h_i) = 3 THEN
886: END IF;
887: FOR h_i IN 1..x_num_key_columns LOOP
888: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
889: END LOOP;
890: bsc_dbgen_utils.add_string(h_sql, '0 type');
891: FOR h_i IN 1..x_num_data_columns LOOP
892: IF x_data_proj_methods(h_i) = 3 THEN
893: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tr'||h_i);
894: END IF;
889: END LOOP;
890: bsc_dbgen_utils.add_string(h_sql, '0 type');
891: FOR h_i IN 1..x_num_data_columns LOOP
892: IF x_data_proj_methods(h_i) = 3 THEN
893: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tr'||h_i);
894: END IF;
895: END LOOP;
896: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
897: IF h_yearly_flag <> 1 THEN
892: IF x_data_proj_methods(h_i) = 3 THEN
893: bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tr'||h_i);
894: END IF;
895: END LOOP;
896: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
897: IF h_yearly_flag <> 1 THEN
898: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 0 and'||
899: ' period >= '||h_per_ini||' and period <= '||x_current_period);
900: ELSE
894: END IF;
895: END LOOP;
896: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
897: IF h_yearly_flag <> 1 THEN
898: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 0 and'||
899: ' period >= '||h_per_ini||' and period <= '||x_current_period);
900: ELSE
901: bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 0');
902: END IF;
897: IF h_yearly_flag <> 1 THEN
898: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 0 and'||
899: ' period >= '||h_per_ini||' and period <= '||x_current_period);
900: ELSE
901: bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 0');
902: END IF;
903: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
904: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
905: END IF;
900: ELSE
901: bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 0');
902: END IF;
903: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
904: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
905: END IF;
906: IF x_parallel_jobs = 'Y' THEN
907: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
908: END IF;
903: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
904: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
905: END IF;
906: IF x_parallel_jobs = 'Y' THEN
907: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
908: END IF;
909: IF x_num_key_columns > 0 THEN
910: bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
911: FOR h_i IN 2..x_num_key_columns LOOP
906: IF x_parallel_jobs = 'Y' THEN
907: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
908: END IF;
909: IF x_num_key_columns > 0 THEN
910: bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
911: FOR h_i IN 2..x_num_key_columns LOOP
912: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
913: END LOOP;
914: END IF;
908: END IF;
909: IF x_num_key_columns > 0 THEN
910: bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
911: FOR h_i IN 2..x_num_key_columns LOOP
912: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
913: END LOOP;
914: END IF;
915: bsc_dbgen_utils.add_string(h_sql, ') tr,'||
916: ' (select ');
911: FOR h_i IN 2..x_num_key_columns LOOP
912: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
913: END LOOP;
914: END IF;
915: bsc_dbgen_utils.add_string(h_sql, ') tr,'||
916: ' (select ');
917: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
918: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
919: END IF;
914: END IF;
915: bsc_dbgen_utils.add_string(h_sql, ') tr,'||
916: ' (select ');
917: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
918: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
919: END IF;
920: FOR h_i IN 1..x_num_key_columns LOOP
921: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
922: END LOOP;
917: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
918: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
919: END IF;
920: FOR h_i IN 1..x_num_key_columns LOOP
921: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
922: END LOOP;
923: bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
924: FOR h_i IN 1..x_num_data_columns LOOP
925: IF x_data_proj_methods(h_i) = 3 THEN
919: END IF;
920: FOR h_i IN 1..x_num_key_columns LOOP
921: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
922: END LOOP;
923: bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
924: FOR h_i IN 1..x_num_data_columns LOOP
925: IF x_data_proj_methods(h_i) = 3 THEN
926: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' p'||h_i);
927: END IF;
922: END LOOP;
923: bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
924: FOR h_i IN 1..x_num_data_columns LOOP
925: IF x_data_proj_methods(h_i) = 3 THEN
926: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' p'||h_i);
927: END IF;
928: END LOOP;
929: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
930: IF h_yearly_flag <> 1 THEN
925: IF x_data_proj_methods(h_i) = 3 THEN
926: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' p'||h_i);
927: END IF;
928: END LOOP;
929: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
930: IF h_yearly_flag <> 1 THEN
931: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and period > '||x_current_period);
932: ELSE
933: bsc_dbgen_utils.add_string(h_sql, ' year > '||x_current_fy||' and year <= '||h_max_year||' and type = 1');
927: END IF;
928: END LOOP;
929: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
930: IF h_yearly_flag <> 1 THEN
931: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and period > '||x_current_period);
932: ELSE
933: bsc_dbgen_utils.add_string(h_sql, ' year > '||x_current_fy||' and year <= '||h_max_year||' and type = 1');
934: END IF;
935: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
929: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
930: IF h_yearly_flag <> 1 THEN
931: bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and period > '||x_current_period);
932: ELSE
933: bsc_dbgen_utils.add_string(h_sql, ' year > '||x_current_fy||' and year <= '||h_max_year||' and type = 1');
934: END IF;
935: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
936: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
937: END IF;
932: ELSE
933: bsc_dbgen_utils.add_string(h_sql, ' year > '||x_current_fy||' and year <= '||h_max_year||' and type = 1');
934: END IF;
935: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
936: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
937: END IF;
938: IF x_parallel_jobs = 'Y' THEN
939: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
940: END IF;
935: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
936: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
937: END IF;
938: IF x_parallel_jobs = 'Y' THEN
939: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
940: END IF;
941: bsc_dbgen_utils.add_string(h_sql, ') pp'||
942: ' where');
943: FOR h_i IN 1..x_num_key_columns LOOP
937: END IF;
938: IF x_parallel_jobs = 'Y' THEN
939: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
940: END IF;
941: bsc_dbgen_utils.add_string(h_sql, ') pp'||
942: ' where');
943: FOR h_i IN 1..x_num_key_columns LOOP
944: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tp.key'||h_i||'(+) and');
945: END LOOP;
940: END IF;
941: bsc_dbgen_utils.add_string(h_sql, ') pp'||
942: ' where');
943: FOR h_i IN 1..x_num_key_columns LOOP
944: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tp.key'||h_i||'(+) and');
945: END LOOP;
946: bsc_dbgen_utils.add_string(h_sql, ' p.type = tp.type(+) and');
947: FOR h_i IN 1..x_num_key_columns LOOP
948: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tr.key'||h_i||'(+) and');
942: ' where');
943: FOR h_i IN 1..x_num_key_columns LOOP
944: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tp.key'||h_i||'(+) and');
945: END LOOP;
946: bsc_dbgen_utils.add_string(h_sql, ' p.type = tp.type(+) and');
947: FOR h_i IN 1..x_num_key_columns LOOP
948: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tr.key'||h_i||'(+) and');
949: END LOOP;
950: bsc_dbgen_utils.add_string(h_sql, ' p.type = tr.type (+) and');
944: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tp.key'||h_i||'(+) and');
945: END LOOP;
946: bsc_dbgen_utils.add_string(h_sql, ' p.type = tp.type(+) and');
947: FOR h_i IN 1..x_num_key_columns LOOP
948: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tr.key'||h_i||'(+) and');
949: END LOOP;
950: bsc_dbgen_utils.add_string(h_sql, ' p.type = tr.type (+) and');
951: FOR h_i IN 1..x_num_key_columns LOOP
952: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = pp.key'||h_i||'(+) and');
946: bsc_dbgen_utils.add_string(h_sql, ' p.type = tp.type(+) and');
947: FOR h_i IN 1..x_num_key_columns LOOP
948: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tr.key'||h_i||'(+) and');
949: END LOOP;
950: bsc_dbgen_utils.add_string(h_sql, ' p.type = tr.type (+) and');
951: FOR h_i IN 1..x_num_key_columns LOOP
952: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = pp.key'||h_i||'(+) and');
953: END LOOP;
954: bsc_dbgen_utils.add_string(h_sql, ' p.year = pp.year (+) and'||
948: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tr.key'||h_i||'(+) and');
949: END LOOP;
950: bsc_dbgen_utils.add_string(h_sql, ' p.type = tr.type (+) and');
951: FOR h_i IN 1..x_num_key_columns LOOP
952: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = pp.key'||h_i||'(+) and');
953: END LOOP;
954: bsc_dbgen_utils.add_string(h_sql, ' p.year = pp.year (+) and'||
955: ' p.type = pp.type (+) and p.period = pp.period (+)');
956: END IF;
950: bsc_dbgen_utils.add_string(h_sql, ' p.type = tr.type (+) and');
951: FOR h_i IN 1..x_num_key_columns LOOP
952: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = pp.key'||h_i||'(+) and');
953: END LOOP;
954: bsc_dbgen_utils.add_string(h_sql, ' p.year = pp.year (+) and'||
955: ' p.type = pp.type (+) and p.period = pp.period (+)');
956: END IF;
957: IF h_num_custom_cols > 0 THEN
958: IF h_num_perf_cols > 0 THEN
955: ' p.type = pp.type (+) and p.period = pp.period (+)');
956: END IF;
957: IF h_num_custom_cols > 0 THEN
958: IF h_num_perf_cols > 0 THEN
959: bsc_dbgen_utils.add_string(h_sql, ' UNION ALL');
960: END IF;
961:
962: -- all measures with this projection method goes here other goes with null
963: bsc_dbgen_utils.add_string(h_sql, ' select');
959: bsc_dbgen_utils.add_string(h_sql, ' UNION ALL');
960: END IF;
961:
962: -- all measures with this projection method goes here other goes with null
963: bsc_dbgen_utils.add_string(h_sql, ' select');
964: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
965: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(b) */');
966: END IF;
967: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
961:
962: -- all measures with this projection method goes here other goes with null
963: bsc_dbgen_utils.add_string(h_sql, ' select');
964: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
965: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(b) */');
966: END IF;
967: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
968: FOR h_i IN 1..x_num_key_columns LOOP
969: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
963: bsc_dbgen_utils.add_string(h_sql, ' select');
964: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
965: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(b) */');
966: END IF;
967: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
968: FOR h_i IN 1..x_num_key_columns LOOP
969: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
970: END LOOP;
971: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
965: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(b) */');
966: END IF;
967: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
968: FOR h_i IN 1..x_num_key_columns LOOP
969: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
970: END LOOP;
971: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
972: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
973: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
967: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
968: FOR h_i IN 1..x_num_key_columns LOOP
969: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
970: END LOOP;
971: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
972: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
973: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
974: END IF;
975: FOR h_i IN 1..x_num_data_columns LOOP
969: bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
970: END LOOP;
971: bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
972: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
973: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
974: END IF;
975: FOR h_i IN 1..x_num_data_columns LOOP
976: IF x_data_proj_methods(h_i) = 4 THEN
977: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
973: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
974: END IF;
975: FOR h_i IN 1..x_num_data_columns LOOP
976: IF x_data_proj_methods(h_i) = 4 THEN
977: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
978: ELSE
979: bsc_dbgen_utils.add_string(h_sql, ', null '||x_data_columns(h_i));
980: END IF;
981: END LOOP;
975: FOR h_i IN 1..x_num_data_columns LOOP
976: IF x_data_proj_methods(h_i) = 4 THEN
977: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
978: ELSE
979: bsc_dbgen_utils.add_string(h_sql, ', null '||x_data_columns(h_i));
980: END IF;
981: END LOOP;
982: IF x_aw_flag THEN
983: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
979: bsc_dbgen_utils.add_string(h_sql, ', null '||x_data_columns(h_i));
980: END IF;
981: END LOOP;
982: IF x_aw_flag THEN
983: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
984: END IF;
985: bsc_dbgen_utils.add_string(h_sql, ' from (select');
986: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
987: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
981: END LOOP;
982: IF x_aw_flag THEN
983: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
984: END IF;
985: bsc_dbgen_utils.add_string(h_sql, ' from (select');
986: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
987: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
988: END IF;
989: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
983: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
984: END IF;
985: bsc_dbgen_utils.add_string(h_sql, ' from (select');
986: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
987: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
988: END IF;
989: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
990: FOR h_i IN 1..x_num_key_columns LOOP
991: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
985: bsc_dbgen_utils.add_string(h_sql, ' from (select');
986: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
987: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
988: END IF;
989: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
990: FOR h_i IN 1..x_num_key_columns LOOP
991: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
992: END LOOP;
993: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year year, 0 type, bsc_tmp_all_periods.period period');
987: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
988: END IF;
989: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
990: FOR h_i IN 1..x_num_key_columns LOOP
991: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
992: END LOOP;
993: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year year, 0 type, bsc_tmp_all_periods.period period');
994: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
995: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
989: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
990: FOR h_i IN 1..x_num_key_columns LOOP
991: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
992: END LOOP;
993: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year year, 0 type, bsc_tmp_all_periods.period period');
994: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
995: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
996: END IF;
997: bsc_dbgen_utils.add_string(h_sql, ' from (select');
991: bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
992: END LOOP;
993: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year year, 0 type, bsc_tmp_all_periods.period period');
994: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
995: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
996: END IF;
997: bsc_dbgen_utils.add_string(h_sql, ' from (select');
998: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
999: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
993: bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year year, 0 type, bsc_tmp_all_periods.period period');
994: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
995: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
996: END IF;
997: bsc_dbgen_utils.add_string(h_sql, ' from (select');
998: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
999: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1000: END IF;
1001: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
995: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
996: END IF;
997: bsc_dbgen_utils.add_string(h_sql, ' from (select');
998: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
999: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1000: END IF;
1001: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1002: FOR h_i IN 1..x_num_key_columns LOOP
1003: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
997: bsc_dbgen_utils.add_string(h_sql, ' from (select');
998: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
999: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1000: END IF;
1001: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1002: FOR h_i IN 1..x_num_key_columns LOOP
1003: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1004: END LOOP;
1005: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
999: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1000: END IF;
1001: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1002: FOR h_i IN 1..x_num_key_columns LOOP
1003: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1004: END LOOP;
1005: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
1006: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1007: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
1001: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1002: FOR h_i IN 1..x_num_key_columns LOOP
1003: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1004: END LOOP;
1005: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
1006: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1007: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
1008: IF x_parallel_jobs = 'Y' THEN
1009: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1003: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1004: END LOOP;
1005: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
1006: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1007: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
1008: IF x_parallel_jobs = 'Y' THEN
1009: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1010: END IF;
1011: ELSE
1005: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
1006: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1007: bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
1008: IF x_parallel_jobs = 'Y' THEN
1009: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1010: END IF;
1011: ELSE
1012: IF x_parallel_jobs = 'Y' THEN
1013: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1009: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1010: END IF;
1011: ELSE
1012: IF x_parallel_jobs = 'Y' THEN
1013: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1014: END IF;
1015: END IF;
1016: bsc_dbgen_utils.add_string(h_sql, ') a,');
1017: IF h_yearly_flag <> 1 THEN
1012: IF x_parallel_jobs = 'Y' THEN
1013: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1014: END IF;
1015: END IF;
1016: bsc_dbgen_utils.add_string(h_sql, ') a,');
1017: IF h_yearly_flag <> 1 THEN
1018: bsc_dbgen_utils.add_string(h_sql, ' (select');
1019: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1020: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1014: END IF;
1015: END IF;
1016: bsc_dbgen_utils.add_string(h_sql, ') a,');
1017: IF h_yearly_flag <> 1 THEN
1018: bsc_dbgen_utils.add_string(h_sql, ' (select');
1019: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1020: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1021: END IF;
1022: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
1016: bsc_dbgen_utils.add_string(h_sql, ') a,');
1017: IF h_yearly_flag <> 1 THEN
1018: bsc_dbgen_utils.add_string(h_sql, ' (select');
1019: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1020: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1021: END IF;
1022: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
1023: ' from bsc_db_calendar'||
1024: ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
1018: bsc_dbgen_utils.add_string(h_sql, ' (select');
1019: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1020: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1021: END IF;
1022: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
1023: ' from bsc_db_calendar'||
1024: ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
1025: ' and '||h_per_column||' > '||x_current_period||
1026: ' ) bsc_tmp_all_periods');
1024: ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
1025: ' and '||h_per_column||' > '||x_current_period||
1026: ' ) bsc_tmp_all_periods');
1027: ELSE
1028: bsc_dbgen_utils.add_string(h_sql, ' (select');
1029: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1030: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1031: END IF;
1032: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
1026: ' ) bsc_tmp_all_periods');
1027: ELSE
1028: bsc_dbgen_utils.add_string(h_sql, ' (select');
1029: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1030: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1031: END IF;
1032: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
1033: ' from bsc_db_calendar'||
1034: ' where year > '||x_current_fy||' and year <= '||h_max_year||
1028: bsc_dbgen_utils.add_string(h_sql, ' (select');
1029: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1030: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1031: END IF;
1032: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
1033: ' from bsc_db_calendar'||
1034: ' where year > '||x_current_fy||' and year <= '||h_max_year||
1035: ' and calendar_id = '||x_calendar_id||
1036: ' ) bsc_tmp_all_periods');
1034: ' where year > '||x_current_fy||' and year <= '||h_max_year||
1035: ' and calendar_id = '||x_calendar_id||
1036: ' ) bsc_tmp_all_periods');
1037: END IF;
1038: bsc_dbgen_utils.add_string(h_sql, ') p,'||
1039: ' (select ');
1040: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1041: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
1042: END IF;
1037: END IF;
1038: bsc_dbgen_utils.add_string(h_sql, ') p,'||
1039: ' (select ');
1040: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1041: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
1042: END IF;
1043: FOR h_i IN 1..x_num_key_columns LOOP
1044: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1045: END LOOP;
1040: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1041: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
1042: END IF;
1043: FOR h_i IN 1..x_num_key_columns LOOP
1044: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1045: END LOOP;
1046: bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
1047: FOR h_i IN 1..h_num_custom_cols LOOP
1048: bsc_dbgen_utils.add_string(h_sql, ', '||h_custom_cols(h_i));
1042: END IF;
1043: FOR h_i IN 1..x_num_key_columns LOOP
1044: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1045: END LOOP;
1046: bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
1047: FOR h_i IN 1..h_num_custom_cols LOOP
1048: bsc_dbgen_utils.add_string(h_sql, ', '||h_custom_cols(h_i));
1049: END LOOP;
1050: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1044: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1045: END LOOP;
1046: bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
1047: FOR h_i IN 1..h_num_custom_cols LOOP
1048: bsc_dbgen_utils.add_string(h_sql, ', '||h_custom_cols(h_i));
1049: END LOOP;
1050: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1051: ' where type = 90');
1052: IF h_yearly_flag <> 1 THEN
1046: bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
1047: FOR h_i IN 1..h_num_custom_cols LOOP
1048: bsc_dbgen_utils.add_string(h_sql, ', '||h_custom_cols(h_i));
1049: END LOOP;
1050: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1051: ' where type = 90');
1052: IF h_yearly_flag <> 1 THEN
1053: bsc_dbgen_utils.add_string(h_sql, ' and year = '||x_current_fy||' and period > '||x_current_period);
1054: ELSE
1049: END LOOP;
1050: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1051: ' where type = 90');
1052: IF h_yearly_flag <> 1 THEN
1053: bsc_dbgen_utils.add_string(h_sql, ' and year = '||x_current_fy||' and period > '||x_current_period);
1054: ELSE
1055: bsc_dbgen_utils.add_string(h_sql, ' and year > '||x_current_fy||' and year <= '||h_max_year);
1056: END IF;
1057: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1051: ' where type = 90');
1052: IF h_yearly_flag <> 1 THEN
1053: bsc_dbgen_utils.add_string(h_sql, ' and year = '||x_current_fy||' and period > '||x_current_period);
1054: ELSE
1055: bsc_dbgen_utils.add_string(h_sql, ' and year > '||x_current_fy||' and year <= '||h_max_year);
1056: END IF;
1057: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1058: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
1059: END IF;
1054: ELSE
1055: bsc_dbgen_utils.add_string(h_sql, ' and year > '||x_current_fy||' and year <= '||h_max_year);
1056: END IF;
1057: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1058: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
1059: END IF;
1060: IF x_parallel_jobs = 'Y' THEN
1061: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1062: END IF;
1057: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1058: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
1059: END IF;
1060: IF x_parallel_jobs = 'Y' THEN
1061: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1062: END IF;
1063: bsc_dbgen_utils.add_string(h_sql, ') b'||
1064: ' where');
1065: FOR h_i IN 1..x_num_key_columns LOOP
1059: END IF;
1060: IF x_parallel_jobs = 'Y' THEN
1061: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1062: END IF;
1063: bsc_dbgen_utils.add_string(h_sql, ') b'||
1064: ' where');
1065: FOR h_i IN 1..x_num_key_columns LOOP
1066: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = b.'||x_key_columns(h_i)||'(+) and');
1067: END LOOP;
1062: END IF;
1063: bsc_dbgen_utils.add_string(h_sql, ') b'||
1064: ' where');
1065: FOR h_i IN 1..x_num_key_columns LOOP
1066: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = b.'||x_key_columns(h_i)||'(+) and');
1067: END LOOP;
1068: bsc_dbgen_utils.add_string(h_sql, ' p.year = b.year(+) and p.type = b.type(+) and'||
1069: ' p.period = b.period(+)');
1070: END IF;
1064: ' where');
1065: FOR h_i IN 1..x_num_key_columns LOOP
1066: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = b.'||x_key_columns(h_i)||'(+) and');
1067: END LOOP;
1068: bsc_dbgen_utils.add_string(h_sql, ' p.year = b.year(+) and p.type = b.type(+) and'||
1069: ' p.period = b.period(+)');
1070: END IF;
1071: IF h_many_methods THEN
1072: bsc_dbgen_utils.add_string(h_sql, ') p'||
1068: bsc_dbgen_utils.add_string(h_sql, ' p.year = b.year(+) and p.type = b.type(+) and'||
1069: ' p.period = b.period(+)');
1070: END IF;
1071: IF h_many_methods THEN
1072: bsc_dbgen_utils.add_string(h_sql, ') p'||
1073: ' group by p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||',');
1074: FOR h_i IN 1..x_num_key_columns LOOP
1075: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||',');
1076: END LOOP;
1071: IF h_many_methods THEN
1072: bsc_dbgen_utils.add_string(h_sql, ') p'||
1073: ' group by p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||',');
1074: FOR h_i IN 1..x_num_key_columns LOOP
1075: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||',');
1076: END LOOP;
1077: bsc_dbgen_utils.add_string(h_sql, ' p.year, p.type, p.period');
1078: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1079: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
1073: ' group by p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||',');
1074: FOR h_i IN 1..x_num_key_columns LOOP
1075: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||',');
1076: END LOOP;
1077: bsc_dbgen_utils.add_string(h_sql, ' p.year, p.type, p.period');
1078: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1079: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
1080: END IF;
1081: --Fix bug#5155388
1075: bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||',');
1076: END LOOP;
1077: bsc_dbgen_utils.add_string(h_sql, ' p.year, p.type, p.period');
1078: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1079: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
1080: END IF;
1081: --Fix bug#5155388
1082: IF x_aw_flag THEN
1083: bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
1079: bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
1080: END IF;
1081: --Fix bug#5155388
1082: IF x_aw_flag THEN
1083: bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
1084: END IF;
1085: END IF;
1086: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1087: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
1083: bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
1084: END IF;
1085: END IF;
1086: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1087: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
1088: commit;
1089: END IF;
1090:
1091: -- Now calculate projection for data columns with projection method 1 (moving average)
1101: h_end_per := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);
1102: END IF;
1103: IF (h_num_perf_cols > 0) OR (h_num_custom_cols > 0) THEN
1104: -- There are rows in the projection table already. So we need to update.
1105: bsc_dbgen_utils.add_string(h_sql, 'update '||x_proj_table||' p'||
1106: ' set ('||h_avg_cols(1));
1107: FOR h_i IN 2..h_num_avg_cols LOOP
1108: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1109: END LOOP;
1104: -- There are rows in the projection table already. So we need to update.
1105: bsc_dbgen_utils.add_string(h_sql, 'update '||x_proj_table||' p'||
1106: ' set ('||h_avg_cols(1));
1107: FOR h_i IN 2..h_num_avg_cols LOOP
1108: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1109: END LOOP;
1110: bsc_dbgen_utils.add_string(h_sql, ') = ('||
1111: ' select ');
1112: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1106: ' set ('||h_avg_cols(1));
1107: FOR h_i IN 2..h_num_avg_cols LOOP
1108: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1109: END LOOP;
1110: bsc_dbgen_utils.add_string(h_sql, ') = ('||
1111: ' select ');
1112: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1113: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */ ');
1114: END IF;
1109: END LOOP;
1110: bsc_dbgen_utils.add_string(h_sql, ') = ('||
1111: ' select ');
1112: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1113: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */ ');
1114: END IF;
1115: bsc_dbgen_utils.add_string(h_sql, 'avg('||h_avg_cols(1)||')');
1116: FOR h_i IN 2..h_num_avg_cols LOOP
1117: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1111: ' select ');
1112: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1113: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */ ');
1114: END IF;
1115: bsc_dbgen_utils.add_string(h_sql, 'avg('||h_avg_cols(1)||')');
1116: FOR h_i IN 2..h_num_avg_cols LOOP
1117: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1118: END LOOP;
1119: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1113: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */ ');
1114: END IF;
1115: bsc_dbgen_utils.add_string(h_sql, 'avg('||h_avg_cols(1)||')');
1116: FOR h_i IN 2..h_num_avg_cols LOOP
1117: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1118: END LOOP;
1119: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1120: ' select ');
1121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1115: bsc_dbgen_utils.add_string(h_sql, 'avg('||h_avg_cols(1)||')');
1116: FOR h_i IN 2..h_num_avg_cols LOOP
1117: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1118: END LOOP;
1119: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1120: ' select ');
1121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1122: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */ ');
1123: END IF;
1118: END LOOP;
1119: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1120: ' select ');
1121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1122: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */ ');
1123: END IF;
1124: FOR h_i IN 1..x_num_key_columns LOOP
1125: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1126: END LOOP;
1121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1122: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */ ');
1123: END IF;
1124: FOR h_i IN 1..x_num_key_columns LOOP
1125: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1126: END LOOP;
1127: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1128: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1129: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1123: END IF;
1124: FOR h_i IN 1..x_num_key_columns LOOP
1125: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1126: END LOOP;
1127: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1128: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1129: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1130: END IF;
1131: FOR h_i IN 1..h_num_avg_cols LOOP
1125: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1126: END LOOP;
1127: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1128: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1129: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1130: END IF;
1131: FOR h_i IN 1..h_num_avg_cols LOOP
1132: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1133: END LOOP;
1128: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1129: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1130: END IF;
1131: FOR h_i IN 1..h_num_avg_cols LOOP
1132: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1133: END LOOP;
1134: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1135: ' where type = :1');
1136: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1130: END IF;
1131: FOR h_i IN 1..h_num_avg_cols LOOP
1132: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1133: END LOOP;
1134: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1135: ' where type = :1');
1136: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1137: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :2');
1138: END IF;
1133: END LOOP;
1134: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1135: ' where type = :1');
1136: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1137: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :2');
1138: END IF;
1139: IF x_parallel_jobs = 'Y' THEN
1140: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :3');
1141: END IF;
1136: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1137: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :2');
1138: END IF;
1139: IF x_parallel_jobs = 'Y' THEN
1140: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :3');
1141: END IF;
1142: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1143: ' select ');
1144: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1138: END IF;
1139: IF x_parallel_jobs = 'Y' THEN
1140: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :3');
1141: END IF;
1142: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1143: ' select ');
1144: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1145: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1146: END IF;
1141: END IF;
1142: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1143: ' select ');
1144: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1145: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1146: END IF;
1147: FOR h_i IN 1..x_num_key_columns LOOP
1148: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1149: END LOOP;
1144: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1145: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1146: END IF;
1147: FOR h_i IN 1..x_num_key_columns LOOP
1148: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1149: END LOOP;
1150: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1151: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1152: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1146: END IF;
1147: FOR h_i IN 1..x_num_key_columns LOOP
1148: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1149: END LOOP;
1150: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1151: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1152: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1153: END IF;
1154: FOR h_i IN 1..h_num_avg_cols LOOP
1148: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1149: END LOOP;
1150: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1151: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1152: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1153: END IF;
1154: FOR h_i IN 1..h_num_avg_cols LOOP
1155: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1156: END LOOP;
1151: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1152: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1153: END IF;
1154: FOR h_i IN 1..h_num_avg_cols LOOP
1155: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1156: END LOOP;
1157: bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1158: ' WHERE type = :4');
1159: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1153: END IF;
1154: FOR h_i IN 1..h_num_avg_cols LOOP
1155: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1156: END LOOP;
1157: bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1158: ' WHERE type = :4');
1159: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1160: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :5');
1161: END IF;
1156: END LOOP;
1157: bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1158: ' WHERE type = :4');
1159: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1160: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :5');
1161: END IF;
1162: IF x_parallel_jobs = 'Y' THEN
1163: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :6');
1164: END IF;
1159: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1160: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :5');
1161: END IF;
1162: IF x_parallel_jobs = 'Y' THEN
1163: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :6');
1164: END IF;
1165: bsc_dbgen_utils.add_string(h_sql, ') u'||
1166: ' where');
1167: IF h_yearly_flag = 1 THEN
1161: END IF;
1162: IF x_parallel_jobs = 'Y' THEN
1163: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :6');
1164: END IF;
1165: bsc_dbgen_utils.add_string(h_sql, ') u'||
1166: ' where');
1167: IF h_yearly_flag = 1 THEN
1168: bsc_dbgen_utils.add_string(h_sql, ' (u.year between :7 AND :8)');
1169: ELSE
1164: END IF;
1165: bsc_dbgen_utils.add_string(h_sql, ') u'||
1166: ' where');
1167: IF h_yearly_flag = 1 THEN
1168: bsc_dbgen_utils.add_string(h_sql, ' (u.year between :7 AND :8)');
1169: ELSE
1170: bsc_dbgen_utils.add_string(h_sql, ' (u.year * 1000 + u.period) between (:7 * 1000 + :8) and (:9 * 1000 + :10)');
1171: END IF;
1172: FOR h_i IN 1..x_num_key_columns LOOP
1166: ' where');
1167: IF h_yearly_flag = 1 THEN
1168: bsc_dbgen_utils.add_string(h_sql, ' (u.year between :7 AND :8)');
1169: ELSE
1170: bsc_dbgen_utils.add_string(h_sql, ' (u.year * 1000 + u.period) between (:7 * 1000 + :8) and (:9 * 1000 + :10)');
1171: END IF;
1172: FOR h_i IN 1..x_num_key_columns LOOP
1173: bsc_dbgen_utils.add_string(h_sql, ' and p.'||x_key_columns(h_i)||' = u.'||x_key_columns(h_i));
1174: END LOOP;
1169: ELSE
1170: bsc_dbgen_utils.add_string(h_sql, ' (u.year * 1000 + u.period) between (:7 * 1000 + :8) and (:9 * 1000 + :10)');
1171: END IF;
1172: FOR h_i IN 1..x_num_key_columns LOOP
1173: bsc_dbgen_utils.add_string(h_sql, ' and p.'||x_key_columns(h_i)||' = u.'||x_key_columns(h_i));
1174: END LOOP;
1175: bsc_dbgen_utils.add_string(h_sql, ' and p.type = u.type');
1176: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1177: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = u.periodicity_id');
1171: END IF;
1172: FOR h_i IN 1..x_num_key_columns LOOP
1173: bsc_dbgen_utils.add_string(h_sql, ' and p.'||x_key_columns(h_i)||' = u.'||x_key_columns(h_i));
1174: END LOOP;
1175: bsc_dbgen_utils.add_string(h_sql, ' and p.type = u.type');
1176: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1177: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = u.periodicity_id');
1178: END IF;
1179: bsc_dbgen_utils.add_string(h_sql, ')');
1173: bsc_dbgen_utils.add_string(h_sql, ' and p.'||x_key_columns(h_i)||' = u.'||x_key_columns(h_i));
1174: END LOOP;
1175: bsc_dbgen_utils.add_string(h_sql, ' and p.type = u.type');
1176: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1177: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = u.periodicity_id');
1178: END IF;
1179: bsc_dbgen_utils.add_string(h_sql, ')');
1180: IF h_yearly_flag = 1 THEN
1181: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11');
1175: bsc_dbgen_utils.add_string(h_sql, ' and p.type = u.type');
1176: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1177: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = u.periodicity_id');
1178: END IF;
1179: bsc_dbgen_utils.add_string(h_sql, ')');
1180: IF h_yearly_flag = 1 THEN
1181: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11');
1182: ELSE
1183: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11 and p.period = :12');
1177: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = u.periodicity_id');
1178: END IF;
1179: bsc_dbgen_utils.add_string(h_sql, ')');
1180: IF h_yearly_flag = 1 THEN
1181: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11');
1182: ELSE
1183: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11 and p.period = :12');
1184: END IF;
1185: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1179: bsc_dbgen_utils.add_string(h_sql, ')');
1180: IF h_yearly_flag = 1 THEN
1181: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11');
1182: ELSE
1183: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11 and p.period = :12');
1184: END IF;
1185: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1186: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = :13');
1187: END IF;
1182: ELSE
1183: bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11 and p.period = :12');
1184: END IF;
1185: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1186: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = :13');
1187: END IF;
1188: IF x_parallel_jobs = 'Y' THEN
1189: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :14');
1190: END IF;
1185: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1186: bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = :13');
1187: END IF;
1188: IF x_parallel_jobs = 'Y' THEN
1189: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :14');
1190: END IF;
1191:
1192: FOR h_j IN h_init_per..h_end_per LOOP
1193: h_bind_vars_values.delete;
1242: h_num_bind_vars := h_num_bind_vars + 1;
1243: h_bind_vars_values(h_num_bind_vars) := x_batch_value;
1244: END IF;
1245: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1246: BSC_DBGEN_UTILS.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1247: commit;
1248: END LOOP;
1249: ELSE
1250: -- There is no rows in the projection table. So we need to insert.
1247: commit;
1248: END LOOP;
1249: ELSE
1250: -- There is no rows in the projection table. So we need to insert.
1251: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
1252: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1253: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
1254: END IF;
1255: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
1249: ELSE
1250: -- There is no rows in the projection table. So we need to insert.
1251: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
1252: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1253: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
1254: END IF;
1255: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
1256: IF x_parallel_jobs = 'Y' THEN
1257: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
1251: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
1252: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1253: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
1254: END IF;
1255: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
1256: IF x_parallel_jobs = 'Y' THEN
1257: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
1258: END IF;
1259: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1253: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
1254: END IF;
1255: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
1256: IF x_parallel_jobs = 'Y' THEN
1257: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
1258: END IF;
1259: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1260: FOR h_i IN 1..x_num_key_columns LOOP
1261: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1255: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
1256: IF x_parallel_jobs = 'Y' THEN
1257: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
1258: END IF;
1259: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1260: FOR h_i IN 1..x_num_key_columns LOOP
1261: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1262: END LOOP;
1263: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1257: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
1258: END IF;
1259: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1260: FOR h_i IN 1..x_num_key_columns LOOP
1261: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1262: END LOOP;
1263: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1264: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1265: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1259: bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1260: FOR h_i IN 1..x_num_key_columns LOOP
1261: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1262: END LOOP;
1263: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1264: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1265: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1266: END IF;
1267: FOR h_i IN 1..h_num_avg_cols LOOP
1261: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1262: END LOOP;
1263: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1264: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1265: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1266: END IF;
1267: FOR h_i IN 1..h_num_avg_cols LOOP
1268: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1269: END LOOP;
1264: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1265: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1266: END IF;
1267: FOR h_i IN 1..h_num_avg_cols LOOP
1268: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1269: END LOOP;
1270: IF x_aw_flag THEN
1271: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
1272: END IF;
1267: FOR h_i IN 1..h_num_avg_cols LOOP
1268: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1269: END LOOP;
1270: IF x_aw_flag THEN
1271: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
1272: END IF;
1273: bsc_dbgen_utils.add_string(h_sql, ')'||
1274: ' select');
1275: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1269: END LOOP;
1270: IF x_aw_flag THEN
1271: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
1272: END IF;
1273: bsc_dbgen_utils.add_string(h_sql, ')'||
1274: ' select');
1275: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1276: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */');
1277: END IF;
1272: END IF;
1273: bsc_dbgen_utils.add_string(h_sql, ')'||
1274: ' select');
1275: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1276: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */');
1277: END IF;
1278: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1279: FOR h_i IN 1..x_num_key_columns LOOP
1280: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1274: ' select');
1275: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1276: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */');
1277: END IF;
1278: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1279: FOR h_i IN 1..x_num_key_columns LOOP
1280: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1281: END LOOP;
1282: IF h_yearly_flag = 1 THEN
1276: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */');
1277: END IF;
1278: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1279: FOR h_i IN 1..x_num_key_columns LOOP
1280: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1281: END LOOP;
1282: IF h_yearly_flag = 1 THEN
1283: bsc_dbgen_utils.add_string(h_sql, ':1, 0, 0');
1284: ELSE
1279: FOR h_i IN 1..x_num_key_columns LOOP
1280: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1281: END LOOP;
1282: IF h_yearly_flag = 1 THEN
1283: bsc_dbgen_utils.add_string(h_sql, ':1, 0, 0');
1284: ELSE
1285: bsc_dbgen_utils.add_string(h_sql, ':1, 0, :2');
1286: END IF;
1287: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1281: END LOOP;
1282: IF h_yearly_flag = 1 THEN
1283: bsc_dbgen_utils.add_string(h_sql, ':1, 0, 0');
1284: ELSE
1285: bsc_dbgen_utils.add_string(h_sql, ':1, 0, :2');
1286: END IF;
1287: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1288: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1289: END IF;
1284: ELSE
1285: bsc_dbgen_utils.add_string(h_sql, ':1, 0, :2');
1286: END IF;
1287: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1288: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1289: END IF;
1290: FOR h_i IN 1..h_num_avg_cols LOOP
1291: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1292: END LOOP;
1287: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1288: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1289: END IF;
1290: FOR h_i IN 1..h_num_avg_cols LOOP
1291: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1292: END LOOP;
1293: IF x_aw_flag THEN
1294: bsc_dbgen_utils.add_string(h_sql, ', :3, :4');
1295: END IF;
1290: FOR h_i IN 1..h_num_avg_cols LOOP
1291: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1292: END LOOP;
1293: IF x_aw_flag THEN
1294: bsc_dbgen_utils.add_string(h_sql, ', :3, :4');
1295: END IF;
1296: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1297: ' select');
1298: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1292: END LOOP;
1293: IF x_aw_flag THEN
1294: bsc_dbgen_utils.add_string(h_sql, ', :3, :4');
1295: END IF;
1296: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1297: ' select');
1298: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1299: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1300: END IF;
1295: END IF;
1296: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1297: ' select');
1298: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1299: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1300: END IF;
1301: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1302: FOR h_i IN 1..x_num_key_columns LOOP
1303: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1297: ' select');
1298: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1299: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1300: END IF;
1301: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1302: FOR h_i IN 1..x_num_key_columns LOOP
1303: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1304: END LOOP;
1305: bsc_dbgen_utils.add_string(h_sql, ' year, type, period');
1299: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1300: END IF;
1301: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1302: FOR h_i IN 1..x_num_key_columns LOOP
1303: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1304: END LOOP;
1305: bsc_dbgen_utils.add_string(h_sql, ' year, type, period');
1306: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1307: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1301: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1302: FOR h_i IN 1..x_num_key_columns LOOP
1303: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1304: END LOOP;
1305: bsc_dbgen_utils.add_string(h_sql, ' year, type, period');
1306: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1307: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1308: END IF;
1309: FOR h_i IN 1..h_num_avg_cols LOOP
1303: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1304: END LOOP;
1305: bsc_dbgen_utils.add_string(h_sql, ' year, type, period');
1306: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1307: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1308: END IF;
1309: FOR h_i IN 1..h_num_avg_cols LOOP
1310: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1311: END LOOP;
1306: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1307: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1308: END IF;
1309: FOR h_i IN 1..h_num_avg_cols LOOP
1310: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1311: END LOOP;
1312: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1313: ' WHERE type = :5');
1314: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1308: END IF;
1309: FOR h_i IN 1..h_num_avg_cols LOOP
1310: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1311: END LOOP;
1312: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1313: ' WHERE type = :5');
1314: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1315: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :6');
1316: END IF;
1311: END LOOP;
1312: bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1313: ' WHERE type = :5');
1314: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1315: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :6');
1316: END IF;
1317: IF x_parallel_jobs = 'Y' THEN
1318: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :7');
1319: END IF;
1314: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1315: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :6');
1316: END IF;
1317: IF x_parallel_jobs = 'Y' THEN
1318: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :7');
1319: END IF;
1320: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1321: ' select ');
1322: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1316: END IF;
1317: IF x_parallel_jobs = 'Y' THEN
1318: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :7');
1319: END IF;
1320: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1321: ' select ');
1322: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1323: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1324: END IF;
1319: END IF;
1320: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1321: ' select ');
1322: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1323: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1324: END IF;
1325: bsc_dbgen_utils.add_string(h_sql, BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1326: FOR h_i IN 1..x_num_key_columns LOOP
1327: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1321: ' select ');
1322: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1323: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1324: END IF;
1325: bsc_dbgen_utils.add_string(h_sql, BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1326: FOR h_i IN 1..x_num_key_columns LOOP
1327: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1328: END LOOP;
1329: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1323: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1324: END IF;
1325: bsc_dbgen_utils.add_string(h_sql, BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1326: FOR h_i IN 1..x_num_key_columns LOOP
1327: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1328: END LOOP;
1329: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1330: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1331: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1325: bsc_dbgen_utils.add_string(h_sql, BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1326: FOR h_i IN 1..x_num_key_columns LOOP
1327: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1328: END LOOP;
1329: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1330: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1331: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1332: END IF;
1333: FOR h_i IN 1..h_num_avg_cols LOOP
1327: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1328: END LOOP;
1329: bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1330: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1331: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1332: END IF;
1333: FOR h_i IN 1..h_num_avg_cols LOOP
1334: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1335: END LOOP;
1330: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1331: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1332: END IF;
1333: FOR h_i IN 1..h_num_avg_cols LOOP
1334: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1335: END LOOP;
1336: bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1337: ' WHERE type = :8');
1338: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1332: END IF;
1333: FOR h_i IN 1..h_num_avg_cols LOOP
1334: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1335: END LOOP;
1336: bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1337: ' WHERE type = :8');
1338: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1339: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :9');
1340: END IF;
1335: END LOOP;
1336: bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1337: ' WHERE type = :8');
1338: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1339: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :9');
1340: END IF;
1341: IF x_parallel_jobs = 'Y' THEN
1342: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :10');
1343: END IF;
1338: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1339: bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :9');
1340: END IF;
1341: IF x_parallel_jobs = 'Y' THEN
1342: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :10');
1343: END IF;
1344: bsc_dbgen_utils.add_string(h_sql, ') u'||
1345: ' where');
1346: IF h_yearly_flag = 1 THEN
1340: END IF;
1341: IF x_parallel_jobs = 'Y' THEN
1342: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :10');
1343: END IF;
1344: bsc_dbgen_utils.add_string(h_sql, ') u'||
1345: ' where');
1346: IF h_yearly_flag = 1 THEN
1347: bsc_dbgen_utils.add_string(h_sql, ' (year between :11 AND :12)');
1348: ELSE
1343: END IF;
1344: bsc_dbgen_utils.add_string(h_sql, ') u'||
1345: ' where');
1346: IF h_yearly_flag = 1 THEN
1347: bsc_dbgen_utils.add_string(h_sql, ' (year between :11 AND :12)');
1348: ELSE
1349: bsc_dbgen_utils.add_string(h_sql, ' (year * 1000 + period) between (:11 * 1000 + :12) and (:13 * 1000 + :14)');
1350: END IF;
1351: bsc_dbgen_utils.add_string(h_sql, ' group by '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1345: ' where');
1346: IF h_yearly_flag = 1 THEN
1347: bsc_dbgen_utils.add_string(h_sql, ' (year between :11 AND :12)');
1348: ELSE
1349: bsc_dbgen_utils.add_string(h_sql, ' (year * 1000 + period) between (:11 * 1000 + :12) and (:13 * 1000 + :14)');
1350: END IF;
1351: bsc_dbgen_utils.add_string(h_sql, ' group by '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1352: FOR h_i IN 1..x_num_key_columns LOOP
1353: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1347: bsc_dbgen_utils.add_string(h_sql, ' (year between :11 AND :12)');
1348: ELSE
1349: bsc_dbgen_utils.add_string(h_sql, ' (year * 1000 + period) between (:11 * 1000 + :12) and (:13 * 1000 + :14)');
1350: END IF;
1351: bsc_dbgen_utils.add_string(h_sql, ' group by '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1352: FOR h_i IN 1..x_num_key_columns LOOP
1353: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1354: END LOOP;
1355: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1349: bsc_dbgen_utils.add_string(h_sql, ' (year * 1000 + period) between (:11 * 1000 + :12) and (:13 * 1000 + :14)');
1350: END IF;
1351: bsc_dbgen_utils.add_string(h_sql, ' group by '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1352: FOR h_i IN 1..x_num_key_columns LOOP
1353: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1354: END LOOP;
1355: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1356: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1357: END IF;
1352: FOR h_i IN 1..x_num_key_columns LOOP
1353: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1354: END LOOP;
1355: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1356: bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1357: END IF;
1358:
1359: FOR h_j IN h_init_per..h_end_per LOOP
1360: h_bind_vars_values.delete;
1410: h_num_bind_vars := h_num_bind_vars + 1;
1411: h_bind_vars_values(h_num_bind_vars) := (h_j - 1);
1412: END IF;
1413: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1414: BSC_DBGEN_UTILS.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1415: commit;
1416: END LOOP;
1417: END IF;
1418: END IF;
1553: h_bal_columns(h_num_bal_columns) := x_data_columns(h_i);
1554: END IF;
1555: END LOOP;
1556:
1557: bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||l_newline||
1558: ' type bsc_b_r is record('||l_newline);
1559: FOR h_i IN 1..x_num_key_columns LOOP
1560: --Fix bug#4880895 use varchar2 to support bis dimensions
1561: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), '||l_newline);
1557: bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||l_newline||
1558: ' type bsc_b_r is record('||l_newline);
1559: FOR h_i IN 1..x_num_key_columns LOOP
1560: --Fix bug#4880895 use varchar2 to support bis dimensions
1561: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), '||l_newline);
1562: END LOOP;
1563: bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5), periodicity_id number'||l_newline);
1564: FOR h_i IN 1..x_num_data_columns LOOP
1565: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||l_newline||
1559: FOR h_i IN 1..x_num_key_columns LOOP
1560: --Fix bug#4880895 use varchar2 to support bis dimensions
1561: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), '||l_newline);
1562: END LOOP;
1563: bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5), periodicity_id number'||l_newline);
1564: FOR h_i IN 1..x_num_data_columns LOOP
1565: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||l_newline||
1566: ', B_DATA'||h_i||' number');
1567: END LOOP;
1561: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), '||l_newline);
1562: END LOOP;
1563: bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5), periodicity_id number'||l_newline);
1564: FOR h_i IN 1..x_num_data_columns LOOP
1565: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||l_newline||
1566: ', B_DATA'||h_i||' number');
1567: END LOOP;
1568: bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' number, row_id rowid);'||l_newline||
1569: ' type bsc_b_t is table of bsc_b_r index by pls_integer;'||l_newline||
1564: FOR h_i IN 1..x_num_data_columns LOOP
1565: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||l_newline||
1566: ', B_DATA'||h_i||' number');
1567: END LOOP;
1568: bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' number, row_id rowid);'||l_newline||
1569: ' type bsc_b_t is table of bsc_b_r index by pls_integer;'||l_newline||
1570: ' type t_rowid_table is table of rowid index by pls_integer;'||l_newline||
1571: ' h_load_batch number;'||l_newline);
1572:
1570: ' type t_rowid_table is table of rowid index by pls_integer;'||l_newline||
1571: ' h_load_batch number;'||l_newline);
1572:
1573: IF x_parallel_jobs = 'Y' THEN
1574: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := '||x_batch_value||';'||l_newline);
1575: ELSE
1576: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := 0;'||l_newline);
1577: END IF;
1578: bsc_dbgen_utils.add_string(h_sql,
1572:
1573: IF x_parallel_jobs = 'Y' THEN
1574: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := '||x_batch_value||';'||l_newline);
1575: ELSE
1576: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := 0;'||l_newline);
1577: END IF;
1578: bsc_dbgen_utils.add_string(h_sql,
1579: ' h_num_partitions number := '||x_num_partitions||';'||l_newline||
1580: ' h_periodicity number := '||x_periodicity||';'||l_newline||
1574: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := '||x_batch_value||';'||l_newline);
1575: ELSE
1576: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := 0;'||l_newline);
1577: END IF;
1578: bsc_dbgen_utils.add_string(h_sql,
1579: ' h_num_partitions number := '||x_num_partitions||';'||l_newline||
1580: ' h_periodicity number := '||x_periodicity||';'||l_newline||
1581: ' h_current_fy number := '||x_current_fy||';'||l_newline||
1582: ' cursor c1 is'||l_newline||
1582: ' cursor c1 is'||l_newline||
1583: ' with bsc_i_data as ('||l_newline||
1584: ' SELECT ');
1585: FOR h_i IN 1..x_num_key_columns LOOP
1586: bsc_dbgen_utils.add_string(h_sql, x_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', '||l_newline);
1587: END LOOP;
1588: IF h_calendar_source = 'BSC' THEN
1589: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
1590: ELSE
1585: FOR h_i IN 1..x_num_key_columns LOOP
1586: bsc_dbgen_utils.add_string(h_sql, x_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', '||l_newline);
1587: END LOOP;
1588: IF h_calendar_source = 'BSC' THEN
1589: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
1590: ELSE
1591: -- BIS calendar
1592: IF h_yearly_flag = 1 THEN
1593: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
1589: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
1590: ELSE
1591: -- BIS calendar
1592: IF h_yearly_flag = 1 THEN
1593: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
1594: ELSE
1595: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
1596: END IF;
1597: END IF;
1591: -- BIS calendar
1592: IF h_yearly_flag = 1 THEN
1593: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
1594: ELSE
1595: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
1596: END IF;
1597: END IF;
1598: bsc_dbgen_utils.add_string(h_sql, ', h_periodicity periodicity_id'||l_newline);
1599: FOR h_i IN 1..x_num_data_columns LOOP
1594: ELSE
1595: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
1596: END IF;
1597: END IF;
1598: bsc_dbgen_utils.add_string(h_sql, ', h_periodicity periodicity_id'||l_newline);
1599: FOR h_i IN 1..x_num_data_columns LOOP
1600: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline);
1601: END LOOP;
1602: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1596: END IF;
1597: END IF;
1598: bsc_dbgen_utils.add_string(h_sql, ', h_periodicity periodicity_id'||l_newline);
1599: FOR h_i IN 1..x_num_data_columns LOOP
1600: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline);
1601: END LOOP;
1602: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1603: ' FROM (select /*+ordered*/ ');
1604: IF x_parallel_jobs = 'Y' THEN
1598: bsc_dbgen_utils.add_string(h_sql, ', h_periodicity periodicity_id'||l_newline);
1599: FOR h_i IN 1..x_num_data_columns LOOP
1600: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline);
1601: END LOOP;
1602: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1603: ' FROM (select /*+ordered*/ ');
1604: IF x_parallel_jobs = 'Y' THEN
1605: bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1606: ELSE
1601: END LOOP;
1602: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1603: ' FROM (select /*+ordered*/ ');
1604: IF x_parallel_jobs = 'Y' THEN
1605: bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1606: ELSE
1607: IF x_num_partitions > 0 THEN
1608: bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
1609: FOR h_i IN 1..x_num_key_columns LOOP
1604: IF x_parallel_jobs = 'Y' THEN
1605: bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1606: ELSE
1607: IF x_num_partitions > 0 THEN
1608: bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
1609: FOR h_i IN 1..x_num_key_columns LOOP
1610: IF h_i = 1 THEN
1611: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1612: ELSE
1607: IF x_num_partitions > 0 THEN
1608: bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
1609: FOR h_i IN 1..x_num_key_columns LOOP
1610: IF h_i = 1 THEN
1611: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1612: ELSE
1613: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1614: END IF;
1615: END LOOP;
1609: FOR h_i IN 1..x_num_key_columns LOOP
1610: IF h_i = 1 THEN
1611: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1612: ELSE
1613: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1614: END IF;
1615: END LOOP;
1616: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1617: ELSE
1612: ELSE
1613: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1614: END IF;
1615: END LOOP;
1616: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1617: ELSE
1618: bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1619: END IF;
1620: END IF;
1614: END IF;
1615: END LOOP;
1616: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1617: ELSE
1618: bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1619: END IF;
1620: END IF;
1621: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.*'||
1622: ' FROM '||x_rowid_table||', '||x_input_table||
1617: ELSE
1618: bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1619: END IF;
1620: END IF;
1621: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.*'||
1622: ' FROM '||x_rowid_table||', '||x_input_table||
1623: ' WHERE '||x_rowid_table||'.row_id = '||x_input_table||'.rowid and'||
1624: ' '||x_rowid_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_load_batch');
1625: IF x_parallel_jobs = 'Y' THEN
1622: ' FROM '||x_rowid_table||', '||x_input_table||
1623: ' WHERE '||x_rowid_table||'.row_id = '||x_input_table||'.rowid and'||
1624: ' '||x_rowid_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_load_batch');
1625: IF x_parallel_jobs = 'Y' THEN
1626: bsc_dbgen_utils.add_string(h_sql, ' and dbms_utility.get_hash_value(');
1627: FOR h_i IN 1..x_num_key_columns LOOP
1628: IF h_i = 1 THEN
1629: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1630: ELSE
1625: IF x_parallel_jobs = 'Y' THEN
1626: bsc_dbgen_utils.add_string(h_sql, ' and dbms_utility.get_hash_value(');
1627: FOR h_i IN 1..x_num_key_columns LOOP
1628: IF h_i = 1 THEN
1629: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1630: ELSE
1631: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1632: END IF;
1633: END LOOP;
1627: FOR h_i IN 1..x_num_key_columns LOOP
1628: IF h_i = 1 THEN
1629: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1630: ELSE
1631: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1632: END IF;
1633: END LOOP;
1634: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
1635: END IF;
1630: ELSE
1631: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1632: END IF;
1633: END LOOP;
1634: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
1635: END IF;
1636: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
1637: FOR h_i IN 1..x_num_key_columns LOOP
1638: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i)||l_newline);
1632: END IF;
1633: END LOOP;
1634: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
1635: END IF;
1636: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
1637: FOR h_i IN 1..x_num_key_columns LOOP
1638: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i)||l_newline);
1639: END LOOP;
1640: IF h_calendar_source <> 'BSC' THEN
1634: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
1635: END IF;
1636: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
1637: FOR h_i IN 1..x_num_key_columns LOOP
1638: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i)||l_newline);
1639: END LOOP;
1640: IF h_calendar_source <> 'BSC' THEN
1641: -- BIS calendar
1642: bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
1638: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i)||l_newline);
1639: END LOOP;
1640: IF h_calendar_source <> 'BSC' THEN
1641: -- BIS calendar
1642: bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
1643: END IF;
1644: IF x_num_key_columns > 0 THEN
1645: bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
1646: x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
1641: -- BIS calendar
1642: bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
1643: END IF;
1644: IF x_num_key_columns > 0 THEN
1645: bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
1646: x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
1647: FOR h_i IN 2..x_num_key_columns LOOP
1648: bsc_dbgen_utils.add_string(h_sql, ' AND '||l_newline||
1649: x_input_table||'.'||x_key_columns(h_i)||' = '||x_key_dim_tables(h_i)||'.USER_CODE');
1644: IF x_num_key_columns > 0 THEN
1645: bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
1646: x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
1647: FOR h_i IN 2..x_num_key_columns LOOP
1648: bsc_dbgen_utils.add_string(h_sql, ' AND '||l_newline||
1649: x_input_table||'.'||x_key_columns(h_i)||' = '||x_key_dim_tables(h_i)||'.USER_CODE');
1650: END LOOP;
1651: END IF;
1652: IF h_calendar_source <> 'BSC' THEN
1651: END IF;
1652: IF h_calendar_source <> 'BSC' THEN
1653: -- BIS calendar
1654: IF x_num_key_columns > 0 THEN
1655: bsc_dbgen_utils.add_string(h_sql, ' AND');
1656: ELSE
1657: bsc_dbgen_utils.add_string(h_sql, ' WHERE');
1658: END IF;
1659: IF h_periodicity_type = 9 THEN
1653: -- BIS calendar
1654: IF x_num_key_columns > 0 THEN
1655: bsc_dbgen_utils.add_string(h_sql, ' AND');
1656: ELSE
1657: bsc_dbgen_utils.add_string(h_sql, ' WHERE');
1658: END IF;
1659: IF h_periodicity_type = 9 THEN
1660: -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
1661: bsc_dbgen_utils.add_string(h_sql,
1657: bsc_dbgen_utils.add_string(h_sql, ' WHERE');
1658: END IF;
1659: IF h_periodicity_type = 9 THEN
1660: -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
1661: bsc_dbgen_utils.add_string(h_sql,
1662: ' TRUNC('||x_input_table||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND'||
1663: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
1664: ELSE
1665: -- Other periodicity. TIME_FK is VARCHAR2
1663: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
1664: ELSE
1665: -- Other periodicity. TIME_FK is VARCHAR2
1666: -- Fix bug#5175277 missing space ANDBSC_SYS_PERIODS....
1667: bsc_dbgen_utils.add_string(h_sql,
1668: ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
1669: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
1670: END IF;
1671: END IF;
1668: ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
1669: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
1670: END IF;
1671: END IF;
1672: bsc_dbgen_utils.add_string(h_sql, ') select ');
1673: FOR h_i IN 1..x_num_key_columns LOOP
1674: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', '||l_newline);
1675: END LOOP;
1676: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD'||
1670: END IF;
1671: END IF;
1672: bsc_dbgen_utils.add_string(h_sql, ') select ');
1673: FOR h_i IN 1..x_num_key_columns LOOP
1674: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', '||l_newline);
1675: END LOOP;
1676: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD'||
1677: ', '||x_input_table||'.PERIODICITY_ID');
1678: FOR h_i IN 1..x_num_data_columns LOOP
1672: bsc_dbgen_utils.add_string(h_sql, ') select ');
1673: FOR h_i IN 1..x_num_key_columns LOOP
1674: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', '||l_newline);
1675: END LOOP;
1676: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD'||
1677: ', '||x_input_table||'.PERIODICITY_ID');
1678: FOR h_i IN 1..x_num_data_columns LOOP
1679: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline||
1680: ', '||x_base_table||'.'||x_data_columns(h_i));
1675: END LOOP;
1676: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD'||
1677: ', '||x_input_table||'.PERIODICITY_ID');
1678: FOR h_i IN 1..x_num_data_columns LOOP
1679: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline||
1680: ', '||x_base_table||'.'||x_data_columns(h_i));
1681: END LOOP;
1682: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', '||x_base_table||'.rowid row_id'||
1683: ' FROM (SELECT ');
1678: FOR h_i IN 1..x_num_data_columns LOOP
1679: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline||
1680: ', '||x_base_table||'.'||x_data_columns(h_i));
1681: END LOOP;
1682: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', '||x_base_table||'.rowid row_id'||
1683: ' FROM (SELECT ');
1684: FOR h_i IN 1..x_num_key_columns LOOP
1685: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1686: END LOOP;
1681: END LOOP;
1682: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', '||x_base_table||'.rowid row_id'||
1683: ' FROM (SELECT ');
1684: FOR h_i IN 1..x_num_key_columns LOOP
1685: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1686: END LOOP;
1687: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_i_data.PERIOD, bsc_i_data.periodicity_id');
1688: FOR h_i IN 1..x_num_data_columns LOOP
1689: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||x_data_columns(h_i)||l_newline);
1683: ' FROM (SELECT ');
1684: FOR h_i IN 1..x_num_key_columns LOOP
1685: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1686: END LOOP;
1687: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_i_data.PERIOD, bsc_i_data.periodicity_id');
1688: FOR h_i IN 1..x_num_data_columns LOOP
1689: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||x_data_columns(h_i)||l_newline);
1690: END LOOP;
1691: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
1685: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1686: END LOOP;
1687: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_i_data.PERIOD, bsc_i_data.periodicity_id');
1688: FOR h_i IN 1..x_num_data_columns LOOP
1689: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||x_data_columns(h_i)||l_newline);
1690: END LOOP;
1691: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
1692: ' from bsc_i_data'||l_newline||
1693: ' union all'||l_newline||
1687: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_i_data.PERIOD, bsc_i_data.periodicity_id');
1688: FOR h_i IN 1..x_num_data_columns LOOP
1689: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||x_data_columns(h_i)||l_newline);
1690: END LOOP;
1691: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
1692: ' from bsc_i_data'||l_newline||
1693: ' union all'||l_newline||
1694: ' select '||l_newline);
1695: FOR h_i IN 1..x_num_key_columns LOOP
1692: ' from bsc_i_data'||l_newline||
1693: ' union all'||l_newline||
1694: ' select '||l_newline);
1695: FOR h_i IN 1..x_num_key_columns LOOP
1696: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1697: END LOOP;
1698: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_db_calendar_temp.upper_PERIOD PERIOD'||
1699: ', bsc_db_calendar_temp.periodicity_id');
1700: FOR h_i IN 1..x_num_data_columns LOOP
1694: ' select '||l_newline);
1695: FOR h_i IN 1..x_num_key_columns LOOP
1696: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1697: END LOOP;
1698: bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_db_calendar_temp.upper_PERIOD PERIOD'||
1699: ', bsc_db_calendar_temp.periodicity_id');
1700: FOR h_i IN 1..x_num_data_columns LOOP
1701: IF x_data_measure_types(h_i) = 1 THEN
1702: -- Activity measure
1699: ', bsc_db_calendar_temp.periodicity_id');
1700: FOR h_i IN 1..x_num_data_columns LOOP
1701: IF x_data_measure_types(h_i) = 1 THEN
1702: -- Activity measure
1703: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_formulas(h_i)||' '||x_data_columns(h_i));
1704: ELSE
1705: -- Balance measure
1706: bsc_dbgen_utils.add_string(h_sql, ', SUM(DECODE(BSC_DB_CALENDAR_TEMP.LAST_PERIOD,''Y'','||x_data_columns(h_i)||', NULL))'||
1707: ' '||x_data_columns(h_i));
1702: -- Activity measure
1703: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_formulas(h_i)||' '||x_data_columns(h_i));
1704: ELSE
1705: -- Balance measure
1706: bsc_dbgen_utils.add_string(h_sql, ', SUM(DECODE(BSC_DB_CALENDAR_TEMP.LAST_PERIOD,''Y'','||x_data_columns(h_i)||', NULL))'||
1707: ' '||x_data_columns(h_i));
1708: END IF;
1709: END LOOP;
1710: --Fix bug#5155523 do not use max(bsc_i_data.batch_column_name) instead add it to the group by
1707: ' '||x_data_columns(h_i));
1708: END IF;
1709: END LOOP;
1710: --Fix bug#5155523 do not use max(bsc_i_data.batch_column_name) instead add it to the group by
1711: bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' '||
1712: BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1713: ' from bsc_i_data, bsc_db_calendar_temp'||l_newline||
1714: ' where bsc_i_data.period = bsc_db_calendar_temp.lower_period and'||l_newline||
1715: ' bsc_i_data.year = bsc_db_calendar_temp.year'||l_newline||
1714: ' where bsc_i_data.period = bsc_db_calendar_temp.lower_period and'||l_newline||
1715: ' bsc_i_data.year = bsc_db_calendar_temp.year'||l_newline||
1716: ' group by ');
1717: FOR h_i IN 1..x_num_key_columns LOOP
1718: bsc_dbgen_utils.add_string(h_sql, 'BSC_I_DATA.'||x_key_columns(h_i)||', ');
1719: END LOOP;
1720: bsc_dbgen_utils.add_string(h_sql, 'BSC_I_DATA.YEAR, BSC_I_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD,'||l_newline||
1721: ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID, BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1722: ') '||x_input_table||l_newline||
1716: ' group by ');
1717: FOR h_i IN 1..x_num_key_columns LOOP
1718: bsc_dbgen_utils.add_string(h_sql, 'BSC_I_DATA.'||x_key_columns(h_i)||', ');
1719: END LOOP;
1720: bsc_dbgen_utils.add_string(h_sql, 'BSC_I_DATA.YEAR, BSC_I_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD,'||l_newline||
1721: ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID, BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1722: ') '||x_input_table||l_newline||
1723: ', (select * from '||x_base_table||l_newline);
1724: IF x_parallel_jobs = 'Y' THEN
1721: ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID, BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1722: ') '||x_input_table||l_newline||
1723: ', (select * from '||x_base_table||l_newline);
1724: IF x_parallel_jobs = 'Y' THEN
1725: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value');
1726: END IF;
1727: bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||l_newline||
1728: ' where ');
1729: FOR h_i IN 1..x_num_key_columns LOOP
1723: ', (select * from '||x_base_table||l_newline);
1724: IF x_parallel_jobs = 'Y' THEN
1725: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value');
1726: END IF;
1727: bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||l_newline||
1728: ' where ');
1729: FOR h_i IN 1..x_num_key_columns LOOP
1730: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and '||l_newline);
1731: END LOOP;
1726: END IF;
1727: bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||l_newline||
1728: ' where ');
1729: FOR h_i IN 1..x_num_key_columns LOOP
1730: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and '||l_newline);
1731: END LOOP;
1732: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR = '||x_base_table||'.YEAR(+) and '||l_newline||
1733: x_input_table||'.TYPE = '||x_base_table||'.TYPE(+) and '||l_newline||
1734: x_input_table||'.PERIOD = '||x_base_table||'.PERIOD(+) and '||l_newline||
1728: ' where ');
1729: FOR h_i IN 1..x_num_key_columns LOOP
1730: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and '||l_newline);
1731: END LOOP;
1732: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR = '||x_base_table||'.YEAR(+) and '||l_newline||
1733: x_input_table||'.TYPE = '||x_base_table||'.TYPE(+) and '||l_newline||
1734: x_input_table||'.PERIOD = '||x_base_table||'.PERIOD(+) and '||l_newline||
1735: x_input_table||'.PERIODICITY_ID = '||x_base_table||'.PERIODICITY_ID(+);'||l_newline||
1736: ' v1 bsc_b_t;'||l_newline||
1742: ' after_period number := '||x_current_period||';'||l_newline||
1743: ' cursor c2 is'||l_newline||
1744: ' select '||l_newline);
1745: FOR h_i IN 1..x_num_key_columns LOOP
1746: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', '||l_newline);
1747: END LOOP;
1748: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE, '||
1749: 'bsc_db_calendar_temp.upper_PERIOD period, bsc_db_calendar_temp.periodicity_id'||l_newline);
1750: FOR h_i IN 1..x_num_data_columns LOOP
1744: ' select '||l_newline);
1745: FOR h_i IN 1..x_num_key_columns LOOP
1746: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', '||l_newline);
1747: END LOOP;
1748: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE, '||
1749: 'bsc_db_calendar_temp.upper_PERIOD period, bsc_db_calendar_temp.periodicity_id'||l_newline);
1750: FOR h_i IN 1..x_num_data_columns LOOP
1751: IF x_data_measure_types(h_i) = 1 THEN
1752: -- Activity measure
1749: 'bsc_db_calendar_temp.upper_PERIOD period, bsc_db_calendar_temp.periodicity_id'||l_newline);
1750: FOR h_i IN 1..x_num_data_columns LOOP
1751: IF x_data_measure_types(h_i) = 1 THEN
1752: -- Activity measure
1753: bsc_dbgen_utils.add_string(h_sql, ', '||
1754: replace(x_data_formulas(h_i),
1755: '('||x_data_columns(h_i)||')',
1756: '('||x_base_table||'.'||x_data_columns(h_i)||')')||
1757: ' '||x_data_columns(h_i)||
1757: ' '||x_data_columns(h_i)||
1758: ', null B_DATA'||h_i);
1759: ELSE
1760: -- Balance measure
1761: bsc_dbgen_utils.add_string(h_sql, ', '||
1762: 'SUM(DECODE(BSC_DB_CALENDAR_TEMP.LAST_PERIOD,''Y'','||x_base_table||'.'||x_data_columns(h_i)||', NULL))'||
1763: ' '||x_data_columns(h_i)||
1764: ', null B_DATA'||h_i);
1765:
1764: ', null B_DATA'||h_i);
1765:
1766: END IF;
1767: END LOOP;
1768: bsc_dbgen_utils.add_string(h_sql, ', max('||x_base_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||') '||
1769: BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
1770: ', chartorowid(tt.row_id) row_id'||
1771: ' from table(cast(v1_join_rollup as '||x_base_table||'_tt)) tt,'||
1772: ' bsc_db_calendar_temp, '||x_base_table||
1771: ' from table(cast(v1_join_rollup as '||x_base_table||'_tt)) tt,'||
1772: ' bsc_db_calendar_temp, '||x_base_table||
1773: ' where ');
1774: FOR h_i IN 1..x_num_key_columns LOOP
1775: bsc_dbgen_utils.add_string(h_sql, 'tt.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||' and ');
1776: END LOOP;
1777: bsc_dbgen_utils.add_string(h_sql, 'tt.year = bsc_db_calendar_temp.year and'||l_newline||
1778: ' tt.type = '||x_base_table||'.type and'||l_newline||
1779: ' tt.period = bsc_db_calendar_temp.upper_period and'||l_newline||
1773: ' where ');
1774: FOR h_i IN 1..x_num_key_columns LOOP
1775: bsc_dbgen_utils.add_string(h_sql, 'tt.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||' and ');
1776: END LOOP;
1777: bsc_dbgen_utils.add_string(h_sql, 'tt.year = bsc_db_calendar_temp.year and'||l_newline||
1778: ' tt.type = '||x_base_table||'.type and'||l_newline||
1779: ' tt.period = bsc_db_calendar_temp.upper_period and'||l_newline||
1780: ' tt.periodicity_id = bsc_db_calendar_temp.periodicity_id and'||l_newline||
1781: ' bsc_db_calendar_temp.year = '||x_base_table||'.year and'||l_newline||
1781: ' bsc_db_calendar_temp.year = '||x_base_table||'.year and'||l_newline||
1782: ' bsc_db_calendar_temp.lower_period = '||x_base_table||'.period and'||l_newline||
1783: ' '||x_base_table||'.periodicity_id = h_periodicity'||l_newline);
1784: IF x_parallel_jobs = 'Y' THEN
1785: bsc_dbgen_utils.add_string(h_sql, ' and '||x_base_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value'||l_newline);
1786: END IF;
1787: bsc_dbgen_utils.add_string(h_sql, ' group by '||l_newline);
1788: FOR h_i IN 1..x_num_key_columns LOOP
1789: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', ');
1783: ' '||x_base_table||'.periodicity_id = h_periodicity'||l_newline);
1784: IF x_parallel_jobs = 'Y' THEN
1785: bsc_dbgen_utils.add_string(h_sql, ' and '||x_base_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value'||l_newline);
1786: END IF;
1787: bsc_dbgen_utils.add_string(h_sql, ' group by '||l_newline);
1788: FOR h_i IN 1..x_num_key_columns LOOP
1789: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', ');
1790: END LOOP;
1791: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE,'||
1785: bsc_dbgen_utils.add_string(h_sql, ' and '||x_base_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value'||l_newline);
1786: END IF;
1787: bsc_dbgen_utils.add_string(h_sql, ' group by '||l_newline);
1788: FOR h_i IN 1..x_num_key_columns LOOP
1789: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', ');
1790: END LOOP;
1791: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE,'||
1792: ' bsc_db_calendar_temp.upper_PERIOD, bsc_db_calendar_temp.periodicity_id, tt.row_id;');
1793: FOR h_i IN 1..x_num_data_columns LOOP
1787: bsc_dbgen_utils.add_string(h_sql, ' group by '||l_newline);
1788: FOR h_i IN 1..x_num_key_columns LOOP
1789: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', ');
1790: END LOOP;
1791: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE,'||
1792: ' bsc_db_calendar_temp.upper_PERIOD, bsc_db_calendar_temp.periodicity_id, tt.row_id;');
1793: FOR h_i IN 1..x_num_data_columns LOOP
1794: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||' dbms_sql.number_table;'||l_newline);
1795: END LOOP;
1790: END LOOP;
1791: bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE,'||
1792: ' bsc_db_calendar_temp.upper_PERIOD, bsc_db_calendar_temp.periodicity_id, tt.row_id;');
1793: FOR h_i IN 1..x_num_data_columns LOOP
1794: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||' dbms_sql.number_table;'||l_newline);
1795: END LOOP;
1796: bsc_dbgen_utils.add_string(h_sql, ' u_rowid t_rowid_table;');
1797: FOR h_i IN 1..x_num_key_columns LOOP
1798: -- Fix bug#4880895 user varchar2 to support bis dimensions
1792: ' bsc_db_calendar_temp.upper_PERIOD, bsc_db_calendar_temp.periodicity_id, tt.row_id;');
1793: FOR h_i IN 1..x_num_data_columns LOOP
1794: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||' dbms_sql.number_table;'||l_newline);
1795: END LOOP;
1796: bsc_dbgen_utils.add_string(h_sql, ' u_rowid t_rowid_table;');
1797: FOR h_i IN 1..x_num_key_columns LOOP
1798: -- Fix bug#4880895 user varchar2 to support bis dimensions
1799: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;'||l_newline);
1800: END LOOP;
1795: END LOOP;
1796: bsc_dbgen_utils.add_string(h_sql, ' u_rowid t_rowid_table;');
1797: FOR h_i IN 1..x_num_key_columns LOOP
1798: -- Fix bug#4880895 user varchar2 to support bis dimensions
1799: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;'||l_newline);
1800: END LOOP;
1801: bsc_dbgen_utils.add_string(h_sql,
1802: ' i_year dbms_sql.number_table;'||l_newline||
1803: ' i_type dbms_sql.number_table;'||l_newline||
1797: FOR h_i IN 1..x_num_key_columns LOOP
1798: -- Fix bug#4880895 user varchar2 to support bis dimensions
1799: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;'||l_newline);
1800: END LOOP;
1801: bsc_dbgen_utils.add_string(h_sql,
1802: ' i_year dbms_sql.number_table;'||l_newline||
1803: ' i_type dbms_sql.number_table;'||l_newline||
1804: ' i_period dbms_sql.number_table;'||l_newline||
1805: ' i_periodicity_id dbms_sql.number_table;'||l_newline);
1803: ' i_type dbms_sql.number_table;'||l_newline||
1804: ' i_period dbms_sql.number_table;'||l_newline||
1805: ' i_periodicity_id dbms_sql.number_table;'||l_newline);
1806: FOR h_i IN 1..x_num_data_columns LOOP
1807: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||' dbms_sql.number_table;'||l_newline);
1808: END LOOP;
1809: bsc_dbgen_utils.add_string(h_sql,
1810: ' i_batch dbms_sql.number_table;'||l_newline||
1811: ' u_count integer := 0;'||l_newline||
1805: ' i_periodicity_id dbms_sql.number_table;'||l_newline);
1806: FOR h_i IN 1..x_num_data_columns LOOP
1807: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||' dbms_sql.number_table;'||l_newline);
1808: END LOOP;
1809: bsc_dbgen_utils.add_string(h_sql,
1810: ' i_batch dbms_sql.number_table;'||l_newline||
1811: ' u_count integer := 0;'||l_newline||
1812: ' i_count integer := 0;'||l_newline||
1813: ' begin'||l_newline);
1815: -- NOTE: Do this only if there are balance columns and the periodicity of the table is not yearly
1816: -- Also no need to do it if before_period = after_period
1817: -- Also this has to be done outside the p_load_batch loop
1818: IF (h_num_bal_columns > 0) AND (h_yearly_flag <> 1) AND (x_current_period > x_old_current_period) THEN
1819: bsc_dbgen_utils.add_string(h_sql, ' declare'||l_newline||
1820: ' cursor c_t is'||l_newline||
1821: ' select * from bsc_db_calendar_temp'||l_newline||
1822: ' order by periodicity_id,year,lower_period;'||l_newline||
1823: ' cursor c_cb(p_lower_periodicity number,p_lower_year number,p_lower_period number,'||l_newline||
1823: ' cursor c_cb(p_lower_periodicity number,p_lower_year number,p_lower_period number,'||l_newline||
1824: ' p_upper_periodicity number,p_upper_year number,p_upper_period number) is'||l_newline||
1825: ' select ');
1826: FOR h_i IN 1..h_num_bal_columns LOOP
1827: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||h_bal_columns(h_i)||' '||h_bal_columns(h_i)||', '||l_newline);
1828: END LOOP;
1829: bsc_dbgen_utils.add_string(h_sql, 'b_upper.rowid row_id'||
1830: ' from '||x_base_table||' b_lower, '||x_base_table||' b_upper'||l_newline||
1831: ' where '||l_newline);
1825: ' select ');
1826: FOR h_i IN 1..h_num_bal_columns LOOP
1827: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||h_bal_columns(h_i)||' '||h_bal_columns(h_i)||', '||l_newline);
1828: END LOOP;
1829: bsc_dbgen_utils.add_string(h_sql, 'b_upper.rowid row_id'||
1830: ' from '||x_base_table||' b_lower, '||x_base_table||' b_upper'||l_newline||
1831: ' where '||l_newline);
1832: IF x_parallel_jobs = 'Y' THEN
1833: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1829: bsc_dbgen_utils.add_string(h_sql, 'b_upper.rowid row_id'||
1830: ' from '||x_base_table||' b_lower, '||x_base_table||' b_upper'||l_newline||
1831: ' where '||l_newline);
1832: IF x_parallel_jobs = 'Y' THEN
1833: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1834: END IF;
1835: bsc_dbgen_utils.add_string(h_sql, 'b_lower.periodicity_id(+) = p_lower_periodicity and'||l_newline||
1836: ' b_lower.year(+) = p_lower_year and'||l_newline||
1837: ' b_lower.period(+) = p_lower_period and '||l_newline);
1831: ' where '||l_newline);
1832: IF x_parallel_jobs = 'Y' THEN
1833: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1834: END IF;
1835: bsc_dbgen_utils.add_string(h_sql, 'b_lower.periodicity_id(+) = p_lower_periodicity and'||l_newline||
1836: ' b_lower.year(+) = p_lower_year and'||l_newline||
1837: ' b_lower.period(+) = p_lower_period and '||l_newline);
1838: IF x_parallel_jobs = 'Y' THEN
1839: bsc_dbgen_utils.add_string(h_sql, 'b_upper.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1835: bsc_dbgen_utils.add_string(h_sql, 'b_lower.periodicity_id(+) = p_lower_periodicity and'||l_newline||
1836: ' b_lower.year(+) = p_lower_year and'||l_newline||
1837: ' b_lower.period(+) = p_lower_period and '||l_newline);
1838: IF x_parallel_jobs = 'Y' THEN
1839: bsc_dbgen_utils.add_string(h_sql, 'b_upper.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1840: END IF;
1841: bsc_dbgen_utils.add_string(h_sql, 'b_upper.periodicity_id = p_upper_periodicity and'||l_newline||
1842: ' b_upper.year = p_upper_year and'||l_newline||
1843: ' b_upper.period = p_upper_period and '||l_newline);
1837: ' b_lower.period(+) = p_lower_period and '||l_newline);
1838: IF x_parallel_jobs = 'Y' THEN
1839: bsc_dbgen_utils.add_string(h_sql, 'b_upper.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1840: END IF;
1841: bsc_dbgen_utils.add_string(h_sql, 'b_upper.periodicity_id = p_upper_periodicity and'||l_newline||
1842: ' b_upper.year = p_upper_year and'||l_newline||
1843: ' b_upper.period = p_upper_period and '||l_newline);
1844: FOR h_i IN 1..x_num_key_columns LOOP
1845: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||x_key_columns(h_i)||'(+) = b_upper.'||x_key_columns(h_i)||' and '||l_newline);
1841: bsc_dbgen_utils.add_string(h_sql, 'b_upper.periodicity_id = p_upper_periodicity and'||l_newline||
1842: ' b_upper.year = p_upper_year and'||l_newline||
1843: ' b_upper.period = p_upper_period and '||l_newline);
1844: FOR h_i IN 1..x_num_key_columns LOOP
1845: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||x_key_columns(h_i)||'(+) = b_upper.'||x_key_columns(h_i)||' and '||l_newline);
1846: END LOOP;
1847: bsc_dbgen_utils.add_string(h_sql, ' b_lower.type(+) = b_upper.type;'||l_newline);
1848: FOR h_i IN 1..h_num_bal_columns LOOP
1849: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||' dbms_sql.number_table;'||l_newline);
1843: ' b_upper.period = p_upper_period and '||l_newline);
1844: FOR h_i IN 1..x_num_key_columns LOOP
1845: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||x_key_columns(h_i)||'(+) = b_upper.'||x_key_columns(h_i)||' and '||l_newline);
1846: END LOOP;
1847: bsc_dbgen_utils.add_string(h_sql, ' b_lower.type(+) = b_upper.type;'||l_newline);
1848: FOR h_i IN 1..h_num_bal_columns LOOP
1849: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||' dbms_sql.number_table;'||l_newline);
1850: END LOOP;
1851: bsc_dbgen_utils.add_string(h_sql, l_newline||
1845: bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||x_key_columns(h_i)||'(+) = b_upper.'||x_key_columns(h_i)||' and '||l_newline);
1846: END LOOP;
1847: bsc_dbgen_utils.add_string(h_sql, ' b_lower.type(+) = b_upper.type;'||l_newline);
1848: FOR h_i IN 1..h_num_bal_columns LOOP
1849: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||' dbms_sql.number_table;'||l_newline);
1850: END LOOP;
1851: bsc_dbgen_utils.add_string(h_sql, l_newline||
1852: ' l_cb_rowid t_rowid_table;'||l_newline||
1853: ' begin'||l_newline||
1847: bsc_dbgen_utils.add_string(h_sql, ' b_lower.type(+) = b_upper.type;'||l_newline);
1848: FOR h_i IN 1..h_num_bal_columns LOOP
1849: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||' dbms_sql.number_table;'||l_newline);
1850: END LOOP;
1851: bsc_dbgen_utils.add_string(h_sql, l_newline||
1852: ' l_cb_rowid t_rowid_table;'||l_newline||
1853: ' begin'||l_newline||
1854: ' open c_t;'||l_newline||
1855: ' loop'||l_newline||
1864: ' if c1_correct(i).periodicity_id = c1_correct(j).periodicity_id and'||l_newline||
1865: ' (c1_correct(j).last_period=''Y'' or'||l_newline||
1866: ' (c1_correct(j).lower_period = after_period and c1_correct(j).year = h_current_fy)) then'||l_newline);
1867: FOR h_i IN 1..h_num_bal_columns LOOP
1868: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||'.delete;'||l_newline);
1869: END LOOP;
1870: bsc_dbgen_utils.add_string(h_sql,
1871: ' l_cb_rowid.delete;'||l_newline||
1872: ' open c_cb(h_periodicity, c1_correct(j).year, c1_correct(j).lower_period,'||l_newline||
1866: ' (c1_correct(j).lower_period = after_period and c1_correct(j).year = h_current_fy)) then'||l_newline);
1867: FOR h_i IN 1..h_num_bal_columns LOOP
1868: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||'.delete;'||l_newline);
1869: END LOOP;
1870: bsc_dbgen_utils.add_string(h_sql,
1871: ' l_cb_rowid.delete;'||l_newline||
1872: ' open c_cb(h_periodicity, c1_correct(j).year, c1_correct(j).lower_period,'||l_newline||
1873: ' c1_correct(j).periodicity_id, c1_correct(j).year, c1_correct(j).upper_period);'||l_newline||
1874: ' loop'||l_newline||
1873: ' c1_correct(j).periodicity_id, c1_correct(j).year, c1_correct(j).upper_period);'||l_newline||
1874: ' loop'||l_newline||
1875: ' fetch c_cb bulk collect into ');
1876: FOR h_i IN 1..h_num_bal_columns LOOP
1877: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||', ');
1878: END LOOP;
1879: bsc_dbgen_utils.add_string(h_sql, 'l_cb_rowid;'||l_newline||
1880: ' exit when c_cb%notfound;'||l_newline||
1881: ' end loop;'||l_newline||
1875: ' fetch c_cb bulk collect into ');
1876: FOR h_i IN 1..h_num_bal_columns LOOP
1877: bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||', ');
1878: END LOOP;
1879: bsc_dbgen_utils.add_string(h_sql, 'l_cb_rowid;'||l_newline||
1880: ' exit when c_cb%notfound;'||l_newline||
1881: ' end loop;'||l_newline||
1882: ' close c_cb;'||l_newline||
1883: ' forall k in 1..l_cb_rowid.count'||l_newline||
1883: ' forall k in 1..l_cb_rowid.count'||l_newline||
1884: ' update '||x_base_table||l_newline||
1885: ' set '||h_bal_columns(1)||' = l_cb_data1(k)');
1886: FOR h_i IN 2..h_num_bal_columns LOOP
1887: bsc_dbgen_utils.add_string(h_sql,
1888: ', '||h_bal_columns(h_i)||' = l_cb_data'||h_i||'(k)'||l_newline);
1889: END LOOP;
1890: bsc_dbgen_utils.add_string(h_sql, ' where rowid = l_cb_rowid(k);'||l_newline||
1891: ' commit;'||l_newline||
1886: FOR h_i IN 2..h_num_bal_columns LOOP
1887: bsc_dbgen_utils.add_string(h_sql,
1888: ', '||h_bal_columns(h_i)||' = l_cb_data'||h_i||'(k)'||l_newline);
1889: END LOOP;
1890: bsc_dbgen_utils.add_string(h_sql, ' where rowid = l_cb_rowid(k);'||l_newline||
1891: ' commit;'||l_newline||
1892: ' exit;'||l_newline||
1893: ' end if;'||l_newline||
1894: ' end loop;'||l_newline||
1896: ' end loop;'||l_newline||
1897: ' end;');
1898: END IF;
1899: -- Start key translation
1900: bsc_dbgen_utils.add_string(h_sql,
1901: ' for k in 0..'||x_num_loads||' loop'||l_newline||
1902: ' h_load_batch := k;'||l_newline||
1903: ' v1.delete;'||l_newline||
1904: ' v1_join_rollup.delete;'||l_newline||
1903: ' v1.delete;'||l_newline||
1904: ' v1_join_rollup.delete;'||l_newline||
1905: ' v1_rollup.delete;'||l_newline);
1906: FOR h_i IN 1..x_num_data_columns LOOP
1907: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'.delete;'||l_newline);
1908: END LOOP;
1909: bsc_dbgen_utils.add_string(h_sql, ' u_rowid.delete;'||l_newline);
1910: FOR h_i IN 1..x_num_key_columns LOOP
1911: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;'||l_newline);
1905: ' v1_rollup.delete;'||l_newline);
1906: FOR h_i IN 1..x_num_data_columns LOOP
1907: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'.delete;'||l_newline);
1908: END LOOP;
1909: bsc_dbgen_utils.add_string(h_sql, ' u_rowid.delete;'||l_newline);
1910: FOR h_i IN 1..x_num_key_columns LOOP
1911: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;'||l_newline);
1912: END LOOP;
1913:
1907: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'.delete;'||l_newline);
1908: END LOOP;
1909: bsc_dbgen_utils.add_string(h_sql, ' u_rowid.delete;'||l_newline);
1910: FOR h_i IN 1..x_num_key_columns LOOP
1911: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;'||l_newline);
1912: END LOOP;
1913:
1914: bsc_dbgen_utils.add_string(h_sql,
1915: ' i_year.delete;'||l_newline||
1910: FOR h_i IN 1..x_num_key_columns LOOP
1911: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;'||l_newline);
1912: END LOOP;
1913:
1914: bsc_dbgen_utils.add_string(h_sql,
1915: ' i_year.delete;'||l_newline||
1916: ' i_type.delete;'||l_newline||
1917: ' i_period.delete;'||l_newline||
1918: ' i_periodicity_id.delete;'||l_newline);
1916: ' i_type.delete;'||l_newline||
1917: ' i_period.delete;'||l_newline||
1918: ' i_periodicity_id.delete;'||l_newline);
1919: FOR h_i IN 1..x_num_data_columns LOOP
1920: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;'||l_newline);
1921: END LOOP;
1922:
1923: bsc_dbgen_utils.add_string(h_sql,
1924: ' i_batch.delete;'||l_newline||
1919: FOR h_i IN 1..x_num_data_columns LOOP
1920: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;'||l_newline);
1921: END LOOP;
1922:
1923: bsc_dbgen_utils.add_string(h_sql,
1924: ' i_batch.delete;'||l_newline||
1925: ' u_count := 0;'||l_newline||
1926: ' i_count := 0;'||l_newline||
1927: ' open c1;'||l_newline||
1934: ' if v1(i).row_id is not null and v1(i).periodicity_id <> h_periodicity then'||l_newline||
1935: ' v1_join_rollup.extend;'||l_newline||
1936: ' v1_join_rollup(v1_join_rollup.count) := '||x_base_table||'_t('||l_newline);
1937: FOR h_i IN 1..x_num_key_columns LOOP
1938: bsc_dbgen_utils.add_string(h_sql, 'v1(i).'||x_key_columns(h_i)||', '||l_newline);
1939: END LOOP;
1940:
1941: bsc_dbgen_utils.add_string(h_sql, 'v1(i).year, v1(i).type, v1(i).period, v1(i).periodicity_id');
1942: FOR h_i IN 1..x_num_data_columns LOOP
1937: FOR h_i IN 1..x_num_key_columns LOOP
1938: bsc_dbgen_utils.add_string(h_sql, 'v1(i).'||x_key_columns(h_i)||', '||l_newline);
1939: END LOOP;
1940:
1941: bsc_dbgen_utils.add_string(h_sql, 'v1(i).year, v1(i).type, v1(i).period, v1(i).periodicity_id');
1942: FOR h_i IN 1..x_num_data_columns LOOP
1943: bsc_dbgen_utils.add_string(h_sql, ', null');
1944: END LOOP;
1945: --Fix bug#4915276: use nvl() since 10<>null is false
1939: END LOOP;
1940:
1941: bsc_dbgen_utils.add_string(h_sql, 'v1(i).year, v1(i).type, v1(i).period, v1(i).periodicity_id');
1942: FOR h_i IN 1..x_num_data_columns LOOP
1943: bsc_dbgen_utils.add_string(h_sql, ', null');
1944: END LOOP;
1945: --Fix bug#4915276: use nvl() since 10<>null is false
1946: bsc_dbgen_utils.add_string(h_sql, ', rowidtochar(v1(i).row_id), null);'||l_newline||
1947: ' end if;'||l_newline||
1942: FOR h_i IN 1..x_num_data_columns LOOP
1943: bsc_dbgen_utils.add_string(h_sql, ', null');
1944: END LOOP;
1945: --Fix bug#4915276: use nvl() since 10<>null is false
1946: bsc_dbgen_utils.add_string(h_sql, ', rowidtochar(v1(i).row_id), null);'||l_newline||
1947: ' end if;'||l_newline||
1948: ' end loop;'||l_newline||
1949: ' u_count := 0;'||l_newline||
1950: ' for i in 1..v1.count loop'||l_newline||
1950: ' for i in 1..v1.count loop'||l_newline||
1951: ' if v1(i).row_id is not null then'||l_newline||
1952: ' if (nvl(v1(i).b_data1,-9999999999)<>nvl(v1(i).'||x_data_columns(1)||',-9999999999))'||l_newline);
1953: FOR h_i IN 2..x_num_data_columns LOOP
1954: bsc_dbgen_utils.add_string(h_sql, ' or (nvl(v1(i).b_data'||h_i||',-9999999999)<>nvl(v1(i).'||x_data_columns(h_i)||',-9999999999))'||l_newline);
1955: END LOOP;
1956:
1957: bsc_dbgen_utils.add_string(h_sql, ' then'||l_newline||
1958: ' if v1(i).periodicity_id = h_periodicity then'||l_newline||
1953: FOR h_i IN 2..x_num_data_columns LOOP
1954: bsc_dbgen_utils.add_string(h_sql, ' or (nvl(v1(i).b_data'||h_i||',-9999999999)<>nvl(v1(i).'||x_data_columns(h_i)||',-9999999999))'||l_newline);
1955: END LOOP;
1956:
1957: bsc_dbgen_utils.add_string(h_sql, ' then'||l_newline||
1958: ' if v1(i).periodicity_id = h_periodicity then'||l_newline||
1959: ' u_count:=u_count+1;'||l_newline||
1960: ' u_rowid(u_count):=v1(i).row_id;'||l_newline);
1961: FOR h_i IN 1..x_num_data_columns LOOP
1958: ' if v1(i).periodicity_id = h_periodicity then'||l_newline||
1959: ' u_count:=u_count+1;'||l_newline||
1960: ' u_rowid(u_count):=v1(i).row_id;'||l_newline);
1961: FOR h_i IN 1..x_num_data_columns LOOP
1962: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'(u_count):=v1(i).'||x_data_columns(h_i)||';'||l_newline);
1963: END LOOP;
1964: bsc_dbgen_utils.add_string(h_sql,
1965: ' end if;'||l_newline||
1966: ' end if;'||l_newline||
1960: ' u_rowid(u_count):=v1(i).row_id;'||l_newline);
1961: FOR h_i IN 1..x_num_data_columns LOOP
1962: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'(u_count):=v1(i).'||x_data_columns(h_i)||';'||l_newline);
1963: END LOOP;
1964: bsc_dbgen_utils.add_string(h_sql,
1965: ' end if;'||l_newline||
1966: ' end if;'||l_newline||
1967: ' else'||l_newline||
1968: ' i_count:=i_count+1;'||l_newline);
1966: ' end if;'||l_newline||
1967: ' else'||l_newline||
1968: ' i_count:=i_count+1;'||l_newline);
1969: FOR h_i IN 1..x_num_key_columns LOOP
1970: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'(i_count):=v1(i).'||x_key_columns(h_i)||';'||l_newline);
1971: END LOOP;
1972:
1973: bsc_dbgen_utils.add_string(h_sql,
1974: ' i_YEAR(i_count):=v1(i).year;'||l_newline||
1969: FOR h_i IN 1..x_num_key_columns LOOP
1970: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'(i_count):=v1(i).'||x_key_columns(h_i)||';'||l_newline);
1971: END LOOP;
1972:
1973: bsc_dbgen_utils.add_string(h_sql,
1974: ' i_YEAR(i_count):=v1(i).year;'||l_newline||
1975: ' i_TYPE(i_count):=v1(i).type;'||l_newline||
1976: ' i_PERIOD(i_count):=v1(i).period;'||l_newline||
1977: ' i_periodicity_id(i_count):=v1(i).periodicity_id;'||l_newline);
1979: l_sql := null;
1980: FOR h_i IN 1..x_num_data_columns LOOP
1981: l_sql := l_sql ||' i_data'||h_i||'(i_count):=v1(i).'||x_data_columns(h_i)||';'||l_newline;
1982: END LOOP;
1983: bsc_dbgen_utils.add_string(h_sql, l_sql);
1984: l_sql := null;
1985: l_sql :=
1986: ' i_batch(i_count):=v1(i).'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||';'||l_newline||
1987: ' end if;'||l_newline||
1990: ' update '||x_base_table||l_newline;
1991: IF x_parallel_jobs = 'Y' THEN
1992: l_sql := l_sql||' partition('||x_partition_name||')';
1993: END IF;
1994: bsc_dbgen_utils.add_string(h_sql, l_sql);
1995: l_sql := null;
1996: l_sql := ' set '||x_data_columns(1)||'=u_data1(i)'||l_newline;
1997: FOR h_i IN 2..x_num_data_columns LOOP
1998: l_sql := l_sql||', '||x_data_columns(h_i)||' = u_data'||h_i||'(i)'||l_newline;
1996: l_sql := ' set '||x_data_columns(1)||'=u_data1(i)'||l_newline;
1997: FOR h_i IN 2..x_num_data_columns LOOP
1998: l_sql := l_sql||', '||x_data_columns(h_i)||' = u_data'||h_i||'(i)'||l_newline;
1999: END LOOP;
2000: bsc_dbgen_utils.add_string(h_sql, l_sql);
2001: l_sql := null;
2002: l_sql := ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2003: ' forall i in 1..i_count'||l_newline||
2004: ' insert /*+append*/ into '||x_base_table||l_newline;
2001: l_sql := null;
2002: l_sql := ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2003: ' forall i in 1..i_count'||l_newline||
2004: ' insert /*+append*/ into '||x_base_table||l_newline;
2005: bsc_dbgen_utils.add_string(h_sql, l_sql);
2006: l_sql := null;
2007: -- ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2008: -- ' forall i in 1..i_count'||l_newline||
2009: -- ' insert /*+append*/ into '||x_base_table||l_newline;
2007: -- ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2008: -- ' forall i in 1..i_count'||l_newline||
2009: -- ' insert /*+append*/ into '||x_base_table||l_newline;
2010: IF x_parallel_jobs = 'Y' THEN
2011: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2012: END IF;
2013: bsc_dbgen_utils.add_string(h_sql, ' (');
2014: FOR h_i IN 1..x_num_key_columns LOOP
2015: l_sql := x_key_columns(h_i)||', '||l_newline;
2009: -- ' insert /*+append*/ into '||x_base_table||l_newline;
2010: IF x_parallel_jobs = 'Y' THEN
2011: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2012: END IF;
2013: bsc_dbgen_utils.add_string(h_sql, ' (');
2014: FOR h_i IN 1..x_num_key_columns LOOP
2015: l_sql := x_key_columns(h_i)||', '||l_newline;
2016: bsc_dbgen_utils.add_string(h_sql, l_sql);
2017: END LOOP;
2012: END IF;
2013: bsc_dbgen_utils.add_string(h_sql, ' (');
2014: FOR h_i IN 1..x_num_key_columns LOOP
2015: l_sql := x_key_columns(h_i)||', '||l_newline;
2016: bsc_dbgen_utils.add_string(h_sql, l_sql);
2017: END LOOP;
2018: bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD, PERIODICITY_ID');
2019: FOR h_i IN 1..x_num_data_columns LOOP
2020: l_sql := ', '||x_data_columns(h_i)||l_newline;
2014: FOR h_i IN 1..x_num_key_columns LOOP
2015: l_sql := x_key_columns(h_i)||', '||l_newline;
2016: bsc_dbgen_utils.add_string(h_sql, l_sql);
2017: END LOOP;
2018: bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD, PERIODICITY_ID');
2019: FOR h_i IN 1..x_num_data_columns LOOP
2020: l_sql := ', '||x_data_columns(h_i)||l_newline;
2021: bsc_dbgen_utils.add_string(h_sql, l_sql);
2022: END LOOP;
2017: END LOOP;
2018: bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD, PERIODICITY_ID');
2019: FOR h_i IN 1..x_num_data_columns LOOP
2020: l_sql := ', '||x_data_columns(h_i)||l_newline;
2021: bsc_dbgen_utils.add_string(h_sql, l_sql);
2022: END LOOP;
2023:
2024: l_sql := ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||')'||l_newline||
2025: ' values('||l_newline;
2026: FOR h_i IN 1..x_num_key_columns LOOP
2027: l_sql := l_sql||'i_'||x_key_columns(h_i)||'(i), ';
2028: END LOOP;
2029: l_sql := l_sql||'i_YEAR(i),i_TYPE(i),i_PERIOD(i),i_periodicity_id(i)';
2030: bsc_dbgen_utils.add_string(h_sql, l_sql);
2031: l_sql := null;
2032: FOR h_i IN 1..x_num_data_columns LOOP
2033: l_sql := ', i_data'||h_i||'(i)';
2034: --bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)';
2030: bsc_dbgen_utils.add_string(h_sql, l_sql);
2031: l_sql := null;
2032: FOR h_i IN 1..x_num_data_columns LOOP
2033: l_sql := ', i_data'||h_i||'(i)';
2034: --bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)';
2035: bsc_dbgen_utils.add_string(h_sql, l_sql);
2036: END LOOP;
2037: l_sql := ', i_BATCH(i));'||l_newline||
2038: ' commit;'|| l_newline||
2031: l_sql := null;
2032: FOR h_i IN 1..x_num_data_columns LOOP
2033: l_sql := ', i_data'||h_i||'(i)';
2034: --bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)';
2035: bsc_dbgen_utils.add_string(h_sql, l_sql);
2036: END LOOP;
2037: l_sql := ', i_BATCH(i));'||l_newline||
2038: ' commit;'|| l_newline||
2039: ' v1_rollup.delete;'||l_newline||
2058: l_sql := l_sql||
2059: ' end loop;'||l_newline||
2060: ' forall i in 1..u_count'||l_newline||
2061: ' update '||x_base_table||l_newline;
2062: bsc_dbgen_utils.add_string(h_sql, l_sql);
2063: l_sql :=null;
2064:
2065: IF x_parallel_jobs = 'Y' THEN
2066: l_sql := l_sql||' partition('||x_partition_name||')';
2074: ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2075: ' commit;'||l_newline||
2076: ' end loop;'||l_newline||
2077: ' end;';
2078: bsc_dbgen_utils.add_string(h_sql, l_sql);
2079:
2080: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2081: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
2082: commit;
2077: ' end;';
2078: bsc_dbgen_utils.add_string(h_sql, l_sql);
2079:
2080: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2081: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
2082: commit;
2083: x_return_status := 'success';
2084:
2085: EXCEPTION
2128: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2129: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2130: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2131:
2132: bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||
2133: ' type bsc_b_r is record(');
2134: FOR h_i IN 1..x_num_key_columns LOOP
2135: -- Fix bug#4880895 use varchar2 to support bis dimensions
2136: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), ');
2132: bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||
2133: ' type bsc_b_r is record(');
2134: FOR h_i IN 1..x_num_key_columns LOOP
2135: -- Fix bug#4880895 use varchar2 to support bis dimensions
2136: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), ');
2137: END LOOP;
2138: bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5)');
2139: FOR h_i IN 1..x_num_data_columns LOOP
2140: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||
2134: FOR h_i IN 1..x_num_key_columns LOOP
2135: -- Fix bug#4880895 use varchar2 to support bis dimensions
2136: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), ');
2137: END LOOP;
2138: bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5)');
2139: FOR h_i IN 1..x_num_data_columns LOOP
2140: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||
2141: ', B_DATA'||h_i||' number');
2142: END LOOP;
2136: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), ');
2137: END LOOP;
2138: bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5)');
2139: FOR h_i IN 1..x_num_data_columns LOOP
2140: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||
2141: ', B_DATA'||h_i||' number');
2142: END LOOP;
2143: bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' number, row_id rowid);'||
2144: ' type bsc_b_t is table of bsc_b_r index by pls_integer;'||
2139: FOR h_i IN 1..x_num_data_columns LOOP
2140: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||
2141: ', B_DATA'||h_i||' number');
2142: END LOOP;
2143: bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' number, row_id rowid);'||
2144: ' type bsc_b_t is table of bsc_b_r index by pls_integer;'||
2145: ' type t_rowid_table is table of rowid index by pls_integer;'||
2146: ' h_load_batch number;'||
2147: ' h_batch_value number;'||
2149: ' h_periodicity number := '||x_periodicity||';'||
2150: ' cursor c1 is'||
2151: ' select ');
2152: FOR h_i IN 1..x_num_key_columns LOOP
2153: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', ');
2154: END LOOP;
2155: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2156: FOR h_i IN 1..x_num_data_columns LOOP
2157: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||
2151: ' select ');
2152: FOR h_i IN 1..x_num_key_columns LOOP
2153: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', ');
2154: END LOOP;
2155: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2156: FOR h_i IN 1..x_num_data_columns LOOP
2157: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||
2158: ', '||x_base_table||'.'||x_data_columns(h_i));
2159: END LOOP;
2153: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', ');
2154: END LOOP;
2155: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2156: FOR h_i IN 1..x_num_data_columns LOOP
2157: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||
2158: ', '||x_base_table||'.'||x_data_columns(h_i));
2159: END LOOP;
2160: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2161: ', '||x_base_table||'.rowid row_id'||
2156: FOR h_i IN 1..x_num_data_columns LOOP
2157: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||
2158: ', '||x_base_table||'.'||x_data_columns(h_i));
2159: END LOOP;
2160: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2161: ', '||x_base_table||'.rowid row_id'||
2162: ' FROM (SELECT ');
2163: FOR h_i IN 1..x_num_key_columns LOOP
2164: bsc_dbgen_utils.add_string(h_sql, x_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', ');
2160: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2161: ', '||x_base_table||'.rowid row_id'||
2162: ' FROM (SELECT ');
2163: FOR h_i IN 1..x_num_key_columns LOOP
2164: bsc_dbgen_utils.add_string(h_sql, x_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', ');
2165: END LOOP;
2166: IF h_calendar_source = 'BSC' THEN
2167: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2168: ELSE
2163: FOR h_i IN 1..x_num_key_columns LOOP
2164: bsc_dbgen_utils.add_string(h_sql, x_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', ');
2165: END LOOP;
2166: IF h_calendar_source = 'BSC' THEN
2167: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2168: ELSE
2169: -- BIS calendar
2170: IF h_yearly_flag = 1 THEN
2171: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
2167: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2168: ELSE
2169: -- BIS calendar
2170: IF h_yearly_flag = 1 THEN
2171: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
2172: ELSE
2173: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
2174: END IF;
2175: END IF;
2169: -- BIS calendar
2170: IF h_yearly_flag = 1 THEN
2171: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
2172: ELSE
2173: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
2174: END IF;
2175: END IF;
2176: FOR h_i IN 1..x_num_data_columns LOOP
2177: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i));
2173: bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
2174: END IF;
2175: END IF;
2176: FOR h_i IN 1..x_num_data_columns LOOP
2177: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i));
2178: END LOOP;
2179: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2180: ' FROM (SELECT /*+ ordered */ ');
2181: IF x_parallel_jobs = 'Y' THEN
2175: END IF;
2176: FOR h_i IN 1..x_num_data_columns LOOP
2177: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i));
2178: END LOOP;
2179: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2180: ' FROM (SELECT /*+ ordered */ ');
2181: IF x_parallel_jobs = 'Y' THEN
2182: bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2183: ELSE
2178: END LOOP;
2179: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2180: ' FROM (SELECT /*+ ordered */ ');
2181: IF x_parallel_jobs = 'Y' THEN
2182: bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2183: ELSE
2184: IF x_num_partitions > 0 THEN
2185: bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
2186: FOR h_i IN 1..x_num_key_columns LOOP
2181: IF x_parallel_jobs = 'Y' THEN
2182: bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2183: ELSE
2184: IF x_num_partitions > 0 THEN
2185: bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
2186: FOR h_i IN 1..x_num_key_columns LOOP
2187: IF h_i = 1 THEN
2188: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2189: ELSE
2184: IF x_num_partitions > 0 THEN
2185: bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
2186: FOR h_i IN 1..x_num_key_columns LOOP
2187: IF h_i = 1 THEN
2188: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2189: ELSE
2190: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2191: END IF;
2192: END LOOP;
2186: FOR h_i IN 1..x_num_key_columns LOOP
2187: IF h_i = 1 THEN
2188: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2189: ELSE
2190: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2191: END IF;
2192: END LOOP;
2193: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2194: ELSE
2189: ELSE
2190: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2191: END IF;
2192: END LOOP;
2193: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2194: ELSE
2195: bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2196: END IF;
2197: END IF;
2191: END IF;
2192: END LOOP;
2193: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2194: ELSE
2195: bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2196: END IF;
2197: END IF;
2198: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.*'||
2199: ' FROM '||x_rowid_table||', '||x_input_table||
2194: ELSE
2195: bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2196: END IF;
2197: END IF;
2198: bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.*'||
2199: ' FROM '||x_rowid_table||', '||x_input_table||
2200: ' WHERE '||x_rowid_table||'.row_id = '||x_input_table||'.rowid and'||
2201: ' '||x_rowid_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_load_batch');
2202: IF x_parallel_jobs = 'Y' THEN
2199: ' FROM '||x_rowid_table||', '||x_input_table||
2200: ' WHERE '||x_rowid_table||'.row_id = '||x_input_table||'.rowid and'||
2201: ' '||x_rowid_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_load_batch');
2202: IF x_parallel_jobs = 'Y' THEN
2203: bsc_dbgen_utils.add_string(h_sql, ' and dbms_utility.get_hash_value(');
2204: FOR h_i IN 1..x_num_key_columns LOOP
2205: IF h_i = 1 THEN
2206: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2207: ELSE
2202: IF x_parallel_jobs = 'Y' THEN
2203: bsc_dbgen_utils.add_string(h_sql, ' and dbms_utility.get_hash_value(');
2204: FOR h_i IN 1..x_num_key_columns LOOP
2205: IF h_i = 1 THEN
2206: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2207: ELSE
2208: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2209: END IF;
2210: END LOOP;
2204: FOR h_i IN 1..x_num_key_columns LOOP
2205: IF h_i = 1 THEN
2206: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2207: ELSE
2208: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2209: END IF;
2210: END LOOP;
2211: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
2212: END IF;
2207: ELSE
2208: bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2209: END IF;
2210: END LOOP;
2211: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
2212: END IF;
2213: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
2214: FOR h_i IN 1..x_num_key_columns LOOP
2215: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i));
2209: END IF;
2210: END LOOP;
2211: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
2212: END IF;
2213: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
2214: FOR h_i IN 1..x_num_key_columns LOOP
2215: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i));
2216: END LOOP;
2217: IF h_calendar_source <> 'BSC' THEN
2211: bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
2212: END IF;
2213: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
2214: FOR h_i IN 1..x_num_key_columns LOOP
2215: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i));
2216: END LOOP;
2217: IF h_calendar_source <> 'BSC' THEN
2218: -- BIS calendar
2219: bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
2215: bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i));
2216: END LOOP;
2217: IF h_calendar_source <> 'BSC' THEN
2218: -- BIS calendar
2219: bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
2220: END IF;
2221: IF x_num_key_columns > 0 THEN
2222: bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
2223: x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
2218: -- BIS calendar
2219: bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
2220: END IF;
2221: IF x_num_key_columns > 0 THEN
2222: bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
2223: x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
2224: FOR h_i IN 2..x_num_key_columns LOOP
2225: bsc_dbgen_utils.add_string(h_sql, ' AND '||
2226: x_input_table||'.'||x_key_columns(h_i)||' = '||x_key_dim_tables(h_i)||'.USER_CODE');
2221: IF x_num_key_columns > 0 THEN
2222: bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
2223: x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
2224: FOR h_i IN 2..x_num_key_columns LOOP
2225: bsc_dbgen_utils.add_string(h_sql, ' AND '||
2226: x_input_table||'.'||x_key_columns(h_i)||' = '||x_key_dim_tables(h_i)||'.USER_CODE');
2227: END LOOP;
2228: END IF;
2229: IF h_calendar_source <> 'BSC' THEN
2228: END IF;
2229: IF h_calendar_source <> 'BSC' THEN
2230: -- BIS calendar
2231: IF x_num_key_columns > 0 THEN
2232: bsc_dbgen_utils.add_string(h_sql, ' AND');
2233: ELSE
2234: bsc_dbgen_utils.add_string(h_sql, ' WHERE');
2235: END IF;
2236: IF h_periodicity_type = 9 THEN
2230: -- BIS calendar
2231: IF x_num_key_columns > 0 THEN
2232: bsc_dbgen_utils.add_string(h_sql, ' AND');
2233: ELSE
2234: bsc_dbgen_utils.add_string(h_sql, ' WHERE');
2235: END IF;
2236: IF h_periodicity_type = 9 THEN
2237: -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
2238: bsc_dbgen_utils.add_string(h_sql,
2234: bsc_dbgen_utils.add_string(h_sql, ' WHERE');
2235: END IF;
2236: IF h_periodicity_type = 9 THEN
2237: -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
2238: bsc_dbgen_utils.add_string(h_sql,
2239: ' TRUNC('||x_input_table||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND'||
2240: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
2241: ELSE
2242: -- Other periodicity. TIME_FK is VARCHAR2
2239: ' TRUNC('||x_input_table||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND'||
2240: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
2241: ELSE
2242: -- Other periodicity. TIME_FK is VARCHAR2
2243: bsc_dbgen_utils.add_string(h_sql,
2244: ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
2245: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
2246: END IF;
2247: END IF;
2244: ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
2245: ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
2246: END IF;
2247: END IF;
2248: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table||
2249: ', (select * from '||x_base_table);
2250: IF x_parallel_jobs = 'Y' THEN
2251: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value');
2252: END IF;
2247: END IF;
2248: bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table||
2249: ', (select * from '||x_base_table);
2250: IF x_parallel_jobs = 'Y' THEN
2251: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value');
2252: END IF;
2253: bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||
2254: ' where ');
2255: FOR h_i IN 1..x_num_key_columns LOOP
2249: ', (select * from '||x_base_table);
2250: IF x_parallel_jobs = 'Y' THEN
2251: bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value');
2252: END IF;
2253: bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||
2254: ' where ');
2255: FOR h_i IN 1..x_num_key_columns LOOP
2256: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and ');
2257: END LOOP;
2252: END IF;
2253: bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||
2254: ' where ');
2255: FOR h_i IN 1..x_num_key_columns LOOP
2256: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and ');
2257: END LOOP;
2258: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR = '||x_base_table||'.YEAR(+) and '||
2259: x_input_table||'.TYPE = '||x_base_table||'.TYPE(+) and '||
2260: x_input_table||'.PERIOD = '||x_base_table||'.PERIOD(+);'||
2254: ' where ');
2255: FOR h_i IN 1..x_num_key_columns LOOP
2256: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and ');
2257: END LOOP;
2258: bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR = '||x_base_table||'.YEAR(+) and '||
2259: x_input_table||'.TYPE = '||x_base_table||'.TYPE(+) and '||
2260: x_input_table||'.PERIOD = '||x_base_table||'.PERIOD(+);'||
2261: ' v1 bsc_b_t;');
2262: FOR h_i IN 1..x_num_data_columns LOOP
2259: x_input_table||'.TYPE = '||x_base_table||'.TYPE(+) and '||
2260: x_input_table||'.PERIOD = '||x_base_table||'.PERIOD(+);'||
2261: ' v1 bsc_b_t;');
2262: FOR h_i IN 1..x_num_data_columns LOOP
2263: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table dbms_sql.number_table;');
2264: END LOOP;
2265: bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table t_rowid_table;');
2266: FOR h_i IN 1..x_num_key_columns LOOP
2267: -- Fix bug#4880895 use varchar2 to support bis dimensions
2261: ' v1 bsc_b_t;');
2262: FOR h_i IN 1..x_num_data_columns LOOP
2263: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table dbms_sql.number_table;');
2264: END LOOP;
2265: bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table t_rowid_table;');
2266: FOR h_i IN 1..x_num_key_columns LOOP
2267: -- Fix bug#4880895 use varchar2 to support bis dimensions
2268: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;');
2269: END LOOP;
2264: END LOOP;
2265: bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table t_rowid_table;');
2266: FOR h_i IN 1..x_num_key_columns LOOP
2267: -- Fix bug#4880895 use varchar2 to support bis dimensions
2268: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;');
2269: END LOOP;
2270: bsc_dbgen_utils.add_string(h_sql,
2271: ' i_YEAR dbms_sql.number_table;'||
2272: ' i_TYPE dbms_sql.number_table;'||
2266: FOR h_i IN 1..x_num_key_columns LOOP
2267: -- Fix bug#4880895 use varchar2 to support bis dimensions
2268: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;');
2269: END LOOP;
2270: bsc_dbgen_utils.add_string(h_sql,
2271: ' i_YEAR dbms_sql.number_table;'||
2272: ' i_TYPE dbms_sql.number_table;'||
2273: ' i_PERIOD dbms_sql.number_table;');
2274: FOR h_i IN 1..x_num_data_columns LOOP
2271: ' i_YEAR dbms_sql.number_table;'||
2272: ' i_TYPE dbms_sql.number_table;'||
2273: ' i_PERIOD dbms_sql.number_table;');
2274: FOR h_i IN 1..x_num_data_columns LOOP
2275: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||' dbms_sql.number_table;');
2276: END LOOP;
2277: bsc_dbgen_utils.add_string(h_sql,
2278: ' i_BATCH dbms_sql.number_table;');
2279: IF x_aw_flag THEN
2273: ' i_PERIOD dbms_sql.number_table;');
2274: FOR h_i IN 1..x_num_data_columns LOOP
2275: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||' dbms_sql.number_table;');
2276: END LOOP;
2277: bsc_dbgen_utils.add_string(h_sql,
2278: ' i_BATCH dbms_sql.number_table;');
2279: IF x_aw_flag THEN
2280: bsc_dbgen_utils.add_string(h_sql,
2281: ' i_PROJECTION dbms_sql.varchar2_table;');
2276: END LOOP;
2277: bsc_dbgen_utils.add_string(h_sql,
2278: ' i_BATCH dbms_sql.number_table;');
2279: IF x_aw_flag THEN
2280: bsc_dbgen_utils.add_string(h_sql,
2281: ' i_PROJECTION dbms_sql.varchar2_table;');
2282: END IF;
2283: bsc_dbgen_utils.add_string(h_sql,
2284: ' u_count integer := 0;'||
2279: IF x_aw_flag THEN
2280: bsc_dbgen_utils.add_string(h_sql,
2281: ' i_PROJECTION dbms_sql.varchar2_table;');
2282: END IF;
2283: bsc_dbgen_utils.add_string(h_sql,
2284: ' u_count integer := 0;'||
2285: ' i_count integer := 0;'||
2286: ' begin');
2287: IF x_parallel_jobs = 'Y' THEN
2284: ' u_count integer := 0;'||
2285: ' i_count integer := 0;'||
2286: ' begin');
2287: IF x_parallel_jobs = 'Y' THEN
2288: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := '||x_batch_value||';');
2289: ELSE
2290: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := 0;');
2291: END IF;
2292: bsc_dbgen_utils.add_string(h_sql,
2286: ' begin');
2287: IF x_parallel_jobs = 'Y' THEN
2288: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := '||x_batch_value||';');
2289: ELSE
2290: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := 0;');
2291: END IF;
2292: bsc_dbgen_utils.add_string(h_sql,
2293: ' h_num_partitions := '||x_num_partitions||';'||
2294: ' for k in 0..'||x_num_loads||' loop'||
2288: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := '||x_batch_value||';');
2289: ELSE
2290: bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := 0;');
2291: END IF;
2292: bsc_dbgen_utils.add_string(h_sql,
2293: ' h_num_partitions := '||x_num_partitions||';'||
2294: ' for k in 0..'||x_num_loads||' loop'||
2295: ' h_load_batch := k;'||
2296: ' v1.delete;');
2294: ' for k in 0..'||x_num_loads||' loop'||
2295: ' h_load_batch := k;'||
2296: ' v1.delete;');
2297: FOR h_i IN 1..x_num_data_columns LOOP
2298: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table.delete;');
2299: END LOOP;
2300: bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table.delete;');
2301: FOR h_i IN 1..x_num_key_columns LOOP
2302: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;');
2296: ' v1.delete;');
2297: FOR h_i IN 1..x_num_data_columns LOOP
2298: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table.delete;');
2299: END LOOP;
2300: bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table.delete;');
2301: FOR h_i IN 1..x_num_key_columns LOOP
2302: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;');
2303: END LOOP;
2304: bsc_dbgen_utils.add_string(h_sql,
2298: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table.delete;');
2299: END LOOP;
2300: bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table.delete;');
2301: FOR h_i IN 1..x_num_key_columns LOOP
2302: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;');
2303: END LOOP;
2304: bsc_dbgen_utils.add_string(h_sql,
2305: ' i_YEAR.delete;'||
2306: ' i_TYPE.delete;'||
2300: bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table.delete;');
2301: FOR h_i IN 1..x_num_key_columns LOOP
2302: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;');
2303: END LOOP;
2304: bsc_dbgen_utils.add_string(h_sql,
2305: ' i_YEAR.delete;'||
2306: ' i_TYPE.delete;'||
2307: ' i_PERIOD.delete;');
2308: FOR h_i IN 1..x_num_data_columns LOOP
2305: ' i_YEAR.delete;'||
2306: ' i_TYPE.delete;'||
2307: ' i_PERIOD.delete;');
2308: FOR h_i IN 1..x_num_data_columns LOOP
2309: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;');
2310: END LOOP;
2311: bsc_dbgen_utils.add_string(h_sql,
2312: ' i_BATCH.delete;');
2313: IF x_aw_flag THEN
2307: ' i_PERIOD.delete;');
2308: FOR h_i IN 1..x_num_data_columns LOOP
2309: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;');
2310: END LOOP;
2311: bsc_dbgen_utils.add_string(h_sql,
2312: ' i_BATCH.delete;');
2313: IF x_aw_flag THEN
2314: bsc_dbgen_utils.add_string(h_sql,
2315: ' i_PROJECTION.delete;');
2310: END LOOP;
2311: bsc_dbgen_utils.add_string(h_sql,
2312: ' i_BATCH.delete;');
2313: IF x_aw_flag THEN
2314: bsc_dbgen_utils.add_string(h_sql,
2315: ' i_PROJECTION.delete;');
2316: END IF;
2317: -- Fix bug#4915276: use nvl() since 10<>null is false
2318: bsc_dbgen_utils.add_string(h_sql,
2314: bsc_dbgen_utils.add_string(h_sql,
2315: ' i_PROJECTION.delete;');
2316: END IF;
2317: -- Fix bug#4915276: use nvl() since 10<>null is false
2318: bsc_dbgen_utils.add_string(h_sql,
2319: ' u_count := 0;'||
2320: ' i_count := 0;'||
2321: ' open c1;'||
2322: ' loop'||
2327: ' for i in 1..v1.count loop'||
2328: ' if v1(i).row_id is not null then'||
2329: ' if nvl(v1(i).b_data1,-9999999999)<>nvl(v1(i).'||x_data_columns(1)||',-9999999999)');
2330: FOR h_i IN 2..x_num_data_columns LOOP
2331: bsc_dbgen_utils.add_string(h_sql, ' OR nvl(v1(i).b_data'||h_i||',-9999999999)<>nvl(v1(i).'||x_data_columns(h_i)||',-9999999999)');
2332: END LOOP;
2333: bsc_dbgen_utils.add_string(h_sql, ' then'||
2334: ' u_count := u_count+1;'||
2335: ' u_rowid_table(u_count) := v1(i).row_id;');
2329: ' if nvl(v1(i).b_data1,-9999999999)<>nvl(v1(i).'||x_data_columns(1)||',-9999999999)');
2330: FOR h_i IN 2..x_num_data_columns LOOP
2331: bsc_dbgen_utils.add_string(h_sql, ' OR nvl(v1(i).b_data'||h_i||',-9999999999)<>nvl(v1(i).'||x_data_columns(h_i)||',-9999999999)');
2332: END LOOP;
2333: bsc_dbgen_utils.add_string(h_sql, ' then'||
2334: ' u_count := u_count+1;'||
2335: ' u_rowid_table(u_count) := v1(i).row_id;');
2336: FOR h_i IN 1..x_num_data_columns LOOP
2337: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table(u_count) := v1(i).'||x_data_columns(h_i)||';');
2333: bsc_dbgen_utils.add_string(h_sql, ' then'||
2334: ' u_count := u_count+1;'||
2335: ' u_rowid_table(u_count) := v1(i).row_id;');
2336: FOR h_i IN 1..x_num_data_columns LOOP
2337: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table(u_count) := v1(i).'||x_data_columns(h_i)||';');
2338: END LOOP;
2339: bsc_dbgen_utils.add_string(h_sql,
2340: ' end if;'||
2341: ' else'||
2335: ' u_rowid_table(u_count) := v1(i).row_id;');
2336: FOR h_i IN 1..x_num_data_columns LOOP
2337: bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table(u_count) := v1(i).'||x_data_columns(h_i)||';');
2338: END LOOP;
2339: bsc_dbgen_utils.add_string(h_sql,
2340: ' end if;'||
2341: ' else'||
2342: ' i_count := i_count+1;');
2343: FOR h_i IN 1..x_num_key_columns LOOP
2340: ' end if;'||
2341: ' else'||
2342: ' i_count := i_count+1;');
2343: FOR h_i IN 1..x_num_key_columns LOOP
2344: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'(i_count) := v1(i).'||x_key_columns(h_i)||';');
2345: END LOOP;
2346: bsc_dbgen_utils.add_string(h_sql,
2347: ' i_YEAR(i_count) := v1(i).year;'||
2348: ' i_TYPE(i_count) := v1(i).type;'||
2342: ' i_count := i_count+1;');
2343: FOR h_i IN 1..x_num_key_columns LOOP
2344: bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'(i_count) := v1(i).'||x_key_columns(h_i)||';');
2345: END LOOP;
2346: bsc_dbgen_utils.add_string(h_sql,
2347: ' i_YEAR(i_count) := v1(i).year;'||
2348: ' i_TYPE(i_count) := v1(i).type;'||
2349: ' i_PERIOD(i_count) := v1(i).period;');
2350: FOR h_i IN 1..x_num_data_columns LOOP
2347: ' i_YEAR(i_count) := v1(i).year;'||
2348: ' i_TYPE(i_count) := v1(i).type;'||
2349: ' i_PERIOD(i_count) := v1(i).period;');
2350: FOR h_i IN 1..x_num_data_columns LOOP
2351: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'(i_count) := v1(i).'||x_data_columns(h_i)||';');
2352: END LOOP;
2353: bsc_dbgen_utils.add_string(h_sql,
2354: ' i_BATCH(i_count) := v1(i).'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||';');
2355: IF x_aw_flag THEN
2349: ' i_PERIOD(i_count) := v1(i).period;');
2350: FOR h_i IN 1..x_num_data_columns LOOP
2351: bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'(i_count) := v1(i).'||x_data_columns(h_i)||';');
2352: END LOOP;
2353: bsc_dbgen_utils.add_string(h_sql,
2354: ' i_BATCH(i_count) := v1(i).'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||';');
2355: IF x_aw_flag THEN
2356: IF h_yearly_flag = 1 THEN
2357: bsc_dbgen_utils.add_string(h_sql,
2353: bsc_dbgen_utils.add_string(h_sql,
2354: ' i_BATCH(i_count) := v1(i).'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||';');
2355: IF x_aw_flag THEN
2356: IF h_yearly_flag = 1 THEN
2357: bsc_dbgen_utils.add_string(h_sql,
2358: ' if v1(i).year>'||x_current_fy||' then');
2359: ELSE
2360: bsc_dbgen_utils.add_string(h_sql,
2361: ' if (v1(i).year='||x_current_fy||' and v1(i).period>'||x_current_period||') or'||
2356: IF h_yearly_flag = 1 THEN
2357: bsc_dbgen_utils.add_string(h_sql,
2358: ' if v1(i).year>'||x_current_fy||' then');
2359: ELSE
2360: bsc_dbgen_utils.add_string(h_sql,
2361: ' if (v1(i).year='||x_current_fy||' and v1(i).period>'||x_current_period||') or'||
2362: ' v1(i).year>'||x_current_fy||' then');
2363: END IF;
2364: bsc_dbgen_utils.add_string(h_sql,
2360: bsc_dbgen_utils.add_string(h_sql,
2361: ' if (v1(i).year='||x_current_fy||' and v1(i).period>'||x_current_period||') or'||
2362: ' v1(i).year>'||x_current_fy||' then');
2363: END IF;
2364: bsc_dbgen_utils.add_string(h_sql,
2365: ' i_PROJECTION(i_count) := ''Y'';'||
2366: ' else'||
2367: ' i_PROJECTION(i_count) := ''N'';'||
2368: ' end if;');
2366: ' else'||
2367: ' i_PROJECTION(i_count) := ''N'';'||
2368: ' end if;');
2369: END IF;
2370: bsc_dbgen_utils.add_string(h_sql,
2371: ' end if;'||
2372: ' end loop;'||
2373: ' forall i in 1..u_count'||
2374: ' update '||x_base_table);
2372: ' end loop;'||
2373: ' forall i in 1..u_count'||
2374: ' update '||x_base_table);
2375: IF x_parallel_jobs = 'Y' THEN
2376: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2377: END IF;
2378: bsc_dbgen_utils.add_string(h_sql,
2379: ' set '||x_data_columns(1)||' = u_data1_table(i)');
2380: FOR h_i IN 2..x_num_data_columns LOOP
2374: ' update '||x_base_table);
2375: IF x_parallel_jobs = 'Y' THEN
2376: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2377: END IF;
2378: bsc_dbgen_utils.add_string(h_sql,
2379: ' set '||x_data_columns(1)||' = u_data1_table(i)');
2380: FOR h_i IN 2..x_num_data_columns LOOP
2381: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' = u_data'||h_i||'_table(i)');
2382: END LOOP;
2377: END IF;
2378: bsc_dbgen_utils.add_string(h_sql,
2379: ' set '||x_data_columns(1)||' = u_data1_table(i)');
2380: FOR h_i IN 2..x_num_data_columns LOOP
2381: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' = u_data'||h_i||'_table(i)');
2382: END LOOP;
2383: IF x_aw_flag THEN
2384: bsc_dbgen_utils.add_string(h_sql, ', CHANGE_VECTOR = '||x_change_vector_value);
2385: END IF;
2380: FOR h_i IN 2..x_num_data_columns LOOP
2381: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' = u_data'||h_i||'_table(i)');
2382: END LOOP;
2383: IF x_aw_flag THEN
2384: bsc_dbgen_utils.add_string(h_sql, ', CHANGE_VECTOR = '||x_change_vector_value);
2385: END IF;
2386: bsc_dbgen_utils.add_string(h_sql, ' where '||x_base_table||'.rowid = u_rowid_table(i);'||
2387: ' forall i in 1..i_count'||
2388: ' insert /*+append*/ into '||x_base_table);
2382: END LOOP;
2383: IF x_aw_flag THEN
2384: bsc_dbgen_utils.add_string(h_sql, ', CHANGE_VECTOR = '||x_change_vector_value);
2385: END IF;
2386: bsc_dbgen_utils.add_string(h_sql, ' where '||x_base_table||'.rowid = u_rowid_table(i);'||
2387: ' forall i in 1..i_count'||
2388: ' insert /*+append*/ into '||x_base_table);
2389: IF x_parallel_jobs = 'Y' THEN
2390: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2386: bsc_dbgen_utils.add_string(h_sql, ' where '||x_base_table||'.rowid = u_rowid_table(i);'||
2387: ' forall i in 1..i_count'||
2388: ' insert /*+append*/ into '||x_base_table);
2389: IF x_parallel_jobs = 'Y' THEN
2390: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2391: END IF;
2392: bsc_dbgen_utils.add_string(h_sql, ' (');
2393: FOR h_i IN 1..x_num_key_columns LOOP
2394: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
2388: ' insert /*+append*/ into '||x_base_table);
2389: IF x_parallel_jobs = 'Y' THEN
2390: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2391: END IF;
2392: bsc_dbgen_utils.add_string(h_sql, ' (');
2393: FOR h_i IN 1..x_num_key_columns LOOP
2394: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
2395: END LOOP;
2396: bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD');
2390: bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2391: END IF;
2392: bsc_dbgen_utils.add_string(h_sql, ' (');
2393: FOR h_i IN 1..x_num_key_columns LOOP
2394: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
2395: END LOOP;
2396: bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD');
2397: FOR h_i IN 1..x_num_data_columns LOOP
2398: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
2392: bsc_dbgen_utils.add_string(h_sql, ' (');
2393: FOR h_i IN 1..x_num_key_columns LOOP
2394: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
2395: END LOOP;
2396: bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD');
2397: FOR h_i IN 1..x_num_data_columns LOOP
2398: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
2399: END LOOP;
2400: IF x_aw_flag THEN
2394: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
2395: END LOOP;
2396: bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD');
2397: FOR h_i IN 1..x_num_data_columns LOOP
2398: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
2399: END LOOP;
2400: IF x_aw_flag THEN
2401: bsc_dbgen_utils.add_string(h_sql, ', PROJECTION, CHANGE_VECTOR');
2402: END IF;
2397: FOR h_i IN 1..x_num_data_columns LOOP
2398: bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
2399: END LOOP;
2400: IF x_aw_flag THEN
2401: bsc_dbgen_utils.add_string(h_sql, ', PROJECTION, CHANGE_VECTOR');
2402: END IF;
2403: bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||')'||
2404: ' values(');
2405: FOR h_i IN 1..x_num_key_columns LOOP
2399: END LOOP;
2400: IF x_aw_flag THEN
2401: bsc_dbgen_utils.add_string(h_sql, ', PROJECTION, CHANGE_VECTOR');
2402: END IF;
2403: bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||')'||
2404: ' values(');
2405: FOR h_i IN 1..x_num_key_columns LOOP
2406: bsc_dbgen_utils.add_string(h_sql, 'i_'||x_key_columns(h_i)||'(i), ');
2407: END LOOP;
2402: END IF;
2403: bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||')'||
2404: ' values(');
2405: FOR h_i IN 1..x_num_key_columns LOOP
2406: bsc_dbgen_utils.add_string(h_sql, 'i_'||x_key_columns(h_i)||'(i), ');
2407: END LOOP;
2408: bsc_dbgen_utils.add_string(h_sql, 'i_YEAR(i), i_TYPE(i), i_PERIOD(i)');
2409: FOR h_i IN 1..x_num_data_columns LOOP
2410: bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)');
2404: ' values(');
2405: FOR h_i IN 1..x_num_key_columns LOOP
2406: bsc_dbgen_utils.add_string(h_sql, 'i_'||x_key_columns(h_i)||'(i), ');
2407: END LOOP;
2408: bsc_dbgen_utils.add_string(h_sql, 'i_YEAR(i), i_TYPE(i), i_PERIOD(i)');
2409: FOR h_i IN 1..x_num_data_columns LOOP
2410: bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)');
2411: END LOOP;
2412: IF x_aw_flag THEN
2406: bsc_dbgen_utils.add_string(h_sql, 'i_'||x_key_columns(h_i)||'(i), ');
2407: END LOOP;
2408: bsc_dbgen_utils.add_string(h_sql, 'i_YEAR(i), i_TYPE(i), i_PERIOD(i)');
2409: FOR h_i IN 1..x_num_data_columns LOOP
2410: bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)');
2411: END LOOP;
2412: IF x_aw_flag THEN
2413: bsc_dbgen_utils.add_string(h_sql, ', i_PROJECTION(i), '||x_change_vector_value);
2414: END IF;
2409: FOR h_i IN 1..x_num_data_columns LOOP
2410: bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)');
2411: END LOOP;
2412: IF x_aw_flag THEN
2413: bsc_dbgen_utils.add_string(h_sql, ', i_PROJECTION(i), '||x_change_vector_value);
2414: END IF;
2415: bsc_dbgen_utils.add_string(h_sql, ', i_BATCH(i));'||
2416: ' commit;'||
2417: ' end loop;'||
2411: END LOOP;
2412: IF x_aw_flag THEN
2413: bsc_dbgen_utils.add_string(h_sql, ', i_PROJECTION(i), '||x_change_vector_value);
2414: END IF;
2415: bsc_dbgen_utils.add_string(h_sql, ', i_BATCH(i));'||
2416: ' commit;'||
2417: ' end loop;'||
2418: ' end;');
2419:
2418: ' end;');
2419:
2420: -- Create the stored procedure
2421: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2422: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
2423: commit;
2424:
2425: x_return_status := 'success';
2426: