eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_AW_LOAD_KPI dependencies on DBMS_SQL

Line 15: p_kpi_list dbms_sql.varchar2_table

11: 10g:each kpi may be a separate conc process
12: */
13:
14: procedure load_kpi(
15: p_kpi_list dbms_sql.varchar2_table
16: ) is
17: --
18: l_parallel boolean;
19: Begin

Line 39: procedure load_kpi_jobs(p_kpi_list dbms_sql.varchar2_table,p_base_table_list varchar2) is

35: raise;
36: End;
37:
38: --p_base_table_list : comma separated list of base tables
39: procedure load_kpi_jobs(p_kpi_list dbms_sql.varchar2_table,p_base_table_list varchar2) is
40: --
41: l_job_name varchar2(100);
42: l_process varchar2(8000);
43: l_job_status bsc_aw_utility.parallel_job_tb;

Line 71: p_base_table_list dbms_sql.varchar2_table,

67: BSC_B_2 3014
68: in 10g, we can parallelize each B -> KPI load
69: */
70: procedure load_base_table(
71: p_base_table_list dbms_sql.varchar2_table,
72: p_kpi_list dbms_sql.varchar2_table
73: ) is
74: --
75: l_kpi_list dbms_sql.varchar2_table; --distinct list of kpi

Line 72: p_kpi_list dbms_sql.varchar2_table

68: in 10g, we can parallelize each B -> KPI load
69: */
70: procedure load_base_table(
71: p_base_table_list dbms_sql.varchar2_table,
72: p_kpi_list dbms_sql.varchar2_table
73: ) is
74: --
75: l_kpi_list dbms_sql.varchar2_table; --distinct list of kpi
76: --l_base_table_list dbms_sql.varchar2_table;--base tables in each kpi

Line 75: l_kpi_list dbms_sql.varchar2_table; --distinct list of kpi

71: p_base_table_list dbms_sql.varchar2_table,
72: p_kpi_list dbms_sql.varchar2_table
73: ) is
74: --
75: l_kpi_list dbms_sql.varchar2_table; --distinct list of kpi
76: --l_base_table_list dbms_sql.varchar2_table;--base tables in each kpi
77: l_parallel boolean;
78: Begin
79: for i in 1..p_kpi_list.count loop

Line 76: --l_base_table_list dbms_sql.varchar2_table;--base tables in each kpi

72: p_kpi_list dbms_sql.varchar2_table
73: ) is
74: --
75: l_kpi_list dbms_sql.varchar2_table; --distinct list of kpi
76: --l_base_table_list dbms_sql.varchar2_table;--base tables in each kpi
77: l_parallel boolean;
78: Begin
79: for i in 1..p_kpi_list.count loop
80: if bsc_aw_utility.in_array(l_kpi_list,p_kpi_list(i))=false then

Line 125: l_dim_set dbms_sql.varchar2_table; --this is all the dimsets, actuals and targets

121: This is the single point of entry for both loading kpi and base tables
122: p_base_table_list can contain all the base tables. this means base tables of the kpi and not of the kpi
123: */
124: procedure load_kpi(p_kpi varchar2,p_base_table_list varchar2,p_run_id number,p_job_name varchar2,p_options varchar2) is
125: l_dim_set dbms_sql.varchar2_table; --this is all the dimsets, actuals and targets
126: l_aggregate_dim_set dbms_sql.varchar2_table; --dimsets used in aggregation. (Targets eliminated)
127: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
128: l_base_table_list dbms_sql.varchar2_table;
129: l_varchar_table dbms_sql.varchar2_table;--temp

Line 126: l_aggregate_dim_set dbms_sql.varchar2_table; --dimsets used in aggregation. (Targets eliminated)

122: p_base_table_list can contain all the base tables. this means base tables of the kpi and not of the kpi
123: */
124: procedure load_kpi(p_kpi varchar2,p_base_table_list varchar2,p_run_id number,p_job_name varchar2,p_options varchar2) is
125: l_dim_set dbms_sql.varchar2_table; --this is all the dimsets, actuals and targets
126: l_aggregate_dim_set dbms_sql.varchar2_table; --dimsets used in aggregation. (Targets eliminated)
127: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
128: l_base_table_list dbms_sql.varchar2_table;
129: l_varchar_table dbms_sql.varchar2_table;--temp
130: --

Line 128: l_base_table_list dbms_sql.varchar2_table;

124: procedure load_kpi(p_kpi varchar2,p_base_table_list varchar2,p_run_id number,p_job_name varchar2,p_options varchar2) is
125: l_dim_set dbms_sql.varchar2_table; --this is all the dimsets, actuals and targets
126: l_aggregate_dim_set dbms_sql.varchar2_table; --dimsets used in aggregation. (Targets eliminated)
127: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
128: l_base_table_list dbms_sql.varchar2_table;
129: l_varchar_table dbms_sql.varchar2_table;--temp
130: --
131: l_parallel boolean;
132: l_aggregation aggregation_r;

Line 129: l_varchar_table dbms_sql.varchar2_table;--temp

125: l_dim_set dbms_sql.varchar2_table; --this is all the dimsets, actuals and targets
126: l_aggregate_dim_set dbms_sql.varchar2_table; --dimsets used in aggregation. (Targets eliminated)
127: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
128: l_base_table_list dbms_sql.varchar2_table;
129: l_varchar_table dbms_sql.varchar2_table;--temp
130: --
131: l_parallel boolean;
132: l_aggregation aggregation_r;
133: l_dim_set_parallel dbms_sql.varchar2_table;

Line 133: l_dim_set_parallel dbms_sql.varchar2_table;

129: l_varchar_table dbms_sql.varchar2_table;--temp
130: --
131: l_parallel boolean;
132: l_aggregation aggregation_r;
133: l_dim_set_parallel dbms_sql.varchar2_table;
134: l_dim_set_r bsc_aw_adapter_kpi.dim_set_r;
135: l_pl_base_tables dbms_sql.varchar2_table;
136: Begin
137: if p_run_id is not null then

Line 135: l_pl_base_tables dbms_sql.varchar2_table;

131: l_parallel boolean;
132: l_aggregation aggregation_r;
133: l_dim_set_parallel dbms_sql.varchar2_table;
134: l_dim_set_r bsc_aw_adapter_kpi.dim_set_r;
135: l_pl_base_tables dbms_sql.varchar2_table;
136: Begin
137: if p_run_id is not null then
138: --this is a dbms job. we have to do the initializations since this is a new session
139: bsc_aw_utility.g_options.delete;

Line 312: procedure load_kpi_dimset_job(p_kpi varchar2,p_dimset_list dbms_sql.varchar2_table,p_base_tables dbms_sql.varchar2_table) is

308:
309: /*
310: p_base_tables will be base tables belonging to the kpi
311: */
312: procedure load_kpi_dimset_job(p_kpi varchar2,p_dimset_list dbms_sql.varchar2_table,p_base_tables dbms_sql.varchar2_table) is
313: --
314: l_job_name varchar2(100);
315: l_process varchar2(8000);
316: l_job_status bsc_aw_utility.parallel_job_tb;

Line 337: --just a wrapper to call load_kpi_dimset as a job. we cannot pass dbms_sql.varchar2_table when launching a job

333: log_n('Exception in load_kpi_dimset_job '||sqlerrm);
334: raise;
335: End;
336:
337: --just a wrapper to call load_kpi_dimset as a job. we cannot pass dbms_sql.varchar2_table when launching a job
338: procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables varchar2,
339: p_run_id number,p_job_name varchar2,p_options varchar2) is
340: --
341: l_base_tables dbms_sql.varchar2_table;

Line 341: l_base_tables dbms_sql.varchar2_table;

337: --just a wrapper to call load_kpi_dimset as a job. we cannot pass dbms_sql.varchar2_table when launching a job
338: procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables varchar2,
339: p_run_id number,p_job_name varchar2,p_options varchar2) is
340: --
341: l_base_tables dbms_sql.varchar2_table;
342: Begin
343: if p_base_tables is not null and p_base_tables <> 'null' then
344: bsc_aw_utility.parse_parameter_values(p_base_tables,',',l_base_tables);
345: end if;

Line 387: procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables dbms_sql.varchar2_table) is

383: --
384: base tables can also have corresponding prj tables. p_base_tables will not contain the prj tables. however, get_ds_bt_parameters will grab
385: the correspnding string that contains the prj table also. so for now, we are not adding the prj tables to p_base_tables or l_oor_dimsets
386: */
387: procedure load_kpi_dimset(p_kpi varchar2,p_dim_set varchar2,p_base_tables dbms_sql.varchar2_table) is
388: --
389: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
390: l_load_type varchar2(40); --initial or inc
391: l_oor_dimsets bsc_aw_md_wrapper.bsc_olap_object_relation_tb;

Line 451: l_pl_base_tables dbms_sql.varchar2_table;/to check parallel load /

447: --
448: l_kpi varchar2(300);
449: l_dimset varchar2(300);
450: --
451: l_pl_base_tables dbms_sql.varchar2_table;/*to check parallel load */
452: l_pl_change_vector dbms_sql.number_table;
453: l_min_value dbms_sql.number_table;
454: l_max_value dbms_sql.number_table;
455: l_bt_current_period dbms_sql.varchar2_table;

Line 452: l_pl_change_vector dbms_sql.number_table;

448: l_kpi varchar2(300);
449: l_dimset varchar2(300);
450: --
451: l_pl_base_tables dbms_sql.varchar2_table;/*to check parallel load */
452: l_pl_change_vector dbms_sql.number_table;
453: l_min_value dbms_sql.number_table;
454: l_max_value dbms_sql.number_table;
455: l_bt_current_period dbms_sql.varchar2_table;
456: l_measures dbms_sql.varchar2_table;--to see if we can launch parallel jobs, we need the count of the no. of measures in the dimset

Line 453: l_min_value dbms_sql.number_table;

449: l_dimset varchar2(300);
450: --
451: l_pl_base_tables dbms_sql.varchar2_table;/*to check parallel load */
452: l_pl_change_vector dbms_sql.number_table;
453: l_min_value dbms_sql.number_table;
454: l_max_value dbms_sql.number_table;
455: l_bt_current_period dbms_sql.varchar2_table;
456: l_measures dbms_sql.varchar2_table;--to see if we can launch parallel jobs, we need the count of the no. of measures in the dimset
457: l_property varchar2(3000);

Line 454: l_max_value dbms_sql.number_table;

450: --
451: l_pl_base_tables dbms_sql.varchar2_table;/*to check parallel load */
452: l_pl_change_vector dbms_sql.number_table;
453: l_min_value dbms_sql.number_table;
454: l_max_value dbms_sql.number_table;
455: l_bt_current_period dbms_sql.varchar2_table;
456: l_measures dbms_sql.varchar2_table;--to see if we can launch parallel jobs, we need the count of the no. of measures in the dimset
457: l_property varchar2(3000);
458: l_aggregation aggregation_r;

Line 455: l_bt_current_period dbms_sql.varchar2_table;

451: l_pl_base_tables dbms_sql.varchar2_table;/*to check parallel load */
452: l_pl_change_vector dbms_sql.number_table;
453: l_min_value dbms_sql.number_table;
454: l_max_value dbms_sql.number_table;
455: l_bt_current_period dbms_sql.varchar2_table;
456: l_measures dbms_sql.varchar2_table;--to see if we can launch parallel jobs, we need the count of the no. of measures in the dimset
457: l_property varchar2(3000);
458: l_aggregation aggregation_r;
459: l_LB_resync_program varchar2(200);

Line 456: l_measures dbms_sql.varchar2_table;--to see if we can launch parallel jobs, we need the count of the no. of measures in the dimset

452: l_pl_change_vector dbms_sql.number_table;
453: l_min_value dbms_sql.number_table;
454: l_max_value dbms_sql.number_table;
455: l_bt_current_period dbms_sql.varchar2_table;
456: l_measures dbms_sql.varchar2_table;--to see if we can launch parallel jobs, we need the count of the no. of measures in the dimset
457: l_property varchar2(3000);
458: l_aggregation aggregation_r;
459: l_LB_resync_program varchar2(200);
460: parallel_flag varchar2(10);

Line 626: p_min_value dbms_sql.number_table,

622: p_dimset varchar2,
623: p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
624: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
625: p_load_program varchar2,
626: p_min_value dbms_sql.number_table,
627: p_max_value dbms_sql.number_table,
628: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor*/
629: ) is
630: --

Line 627: p_max_value dbms_sql.number_table,

623: p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
624: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
625: p_load_program varchar2,
626: p_min_value dbms_sql.number_table,
627: p_max_value dbms_sql.number_table,
628: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor*/
629: ) is
630: --
631: l_base_tables dbms_sql.varchar2_table;

Line 628: p_bt_current_period dbms_sql.varchar2_table /null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor/

624: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
625: p_load_program varchar2,
626: p_min_value dbms_sql.number_table,
627: p_max_value dbms_sql.number_table,
628: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor*/
629: ) is
630: --
631: l_base_tables dbms_sql.varchar2_table;
632: l_bt_current_period dbms_sql.varchar2_table;

Line 631: l_base_tables dbms_sql.varchar2_table;

627: p_max_value dbms_sql.number_table,
628: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor*/
629: ) is
630: --
631: l_base_tables dbms_sql.varchar2_table;
632: l_bt_current_period dbms_sql.varchar2_table;
633: l_flag boolean;
634: l_ds_parameters dbms_sql.varchar2_table;
635: l_aggregation aggregation_r;

Line 632: l_bt_current_period dbms_sql.varchar2_table;

628: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null'. this has to be in sync with p_base_table_dimset_oor*/
629: ) is
630: --
631: l_base_tables dbms_sql.varchar2_table;
632: l_bt_current_period dbms_sql.varchar2_table;
633: l_flag boolean;
634: l_ds_parameters dbms_sql.varchar2_table;
635: l_aggregation aggregation_r;
636: l_dim_set bsc_aw_adapter_kpi.dim_set_r;

Line 634: l_ds_parameters dbms_sql.varchar2_table;

630: --
631: l_base_tables dbms_sql.varchar2_table;
632: l_bt_current_period dbms_sql.varchar2_table;
633: l_flag boolean;
634: l_ds_parameters dbms_sql.varchar2_table;
635: l_aggregation aggregation_r;
636: l_dim_set bsc_aw_adapter_kpi.dim_set_r;
637: --
638: l_cubes dbms_sql.varchar2_table;

Line 638: l_cubes dbms_sql.varchar2_table;

634: l_ds_parameters dbms_sql.varchar2_table;
635: l_aggregation aggregation_r;
636: l_dim_set bsc_aw_adapter_kpi.dim_set_r;
637: --
638: l_cubes dbms_sql.varchar2_table;
639: l_measures dbms_sql.varchar2_table;
640: l_bt_measures dbms_sql.varchar2_table;
641: Begin
642: l_flag:=false;

Line 639: l_measures dbms_sql.varchar2_table;

635: l_aggregation aggregation_r;
636: l_dim_set bsc_aw_adapter_kpi.dim_set_r;
637: --
638: l_cubes dbms_sql.varchar2_table;
639: l_measures dbms_sql.varchar2_table;
640: l_bt_measures dbms_sql.varchar2_table;
641: Begin
642: l_flag:=false;
643: for i in 1..p_base_table_dimset_oor.count loop

Line 640: l_bt_measures dbms_sql.varchar2_table;

636: l_dim_set bsc_aw_adapter_kpi.dim_set_r;
637: --
638: l_cubes dbms_sql.varchar2_table;
639: l_measures dbms_sql.varchar2_table;
640: l_bt_measures dbms_sql.varchar2_table;
641: Begin
642: l_flag:=false;
643: for i in 1..p_base_table_dimset_oor.count loop
644: if p_min_value(i)<=p_max_value(i) then

Line 709: p_min_value dbms_sql.number_table,

705: p_base_table_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get baes table, dimset and bt measures
706: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
707: p_load_program varchar2,
708: p_LB_resync_program varchar2,
709: p_min_value dbms_sql.number_table,
710: p_max_value dbms_sql.number_table,
711: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null' */
712: ) is
713: --

Line 710: p_max_value dbms_sql.number_table,

706: p_dimset_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb, --to get measures etc
707: p_load_program varchar2,
708: p_LB_resync_program varchar2,
709: p_min_value dbms_sql.number_table,
710: p_max_value dbms_sql.number_table,
711: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null' */
712: ) is
713: --
714: l_job_name varchar2(100);

Line 711: p_bt_current_period dbms_sql.varchar2_table /null are indicated as 'null' /

707: p_load_program varchar2,
708: p_LB_resync_program varchar2,
709: p_min_value dbms_sql.number_table,
710: p_max_value dbms_sql.number_table,
711: p_bt_current_period dbms_sql.varchar2_table /*null are indicated as 'null' */
712: ) is
713: --
714: l_job_name varchar2(100);
715: l_process varchar2(8000);

Line 718: l_measures dbms_sql.varchar2_table;

714: l_job_name varchar2(100);
715: l_process varchar2(8000);
716: l_job_status bsc_aw_utility.parallel_job_tb;
717: --
718: l_measures dbms_sql.varchar2_table;
719: l_cubes dbms_sql.varchar2_table;
720: l_start_lock_objects varchar2(3000);
721: l_end_lock_objects varchar2(3000);
722: l_parameter_string varchar2(2000);

Line 719: l_cubes dbms_sql.varchar2_table;

715: l_process varchar2(8000);
716: l_job_status bsc_aw_utility.parallel_job_tb;
717: --
718: l_measures dbms_sql.varchar2_table;
719: l_cubes dbms_sql.varchar2_table;
720: l_start_lock_objects varchar2(3000);
721: l_end_lock_objects varchar2(3000);
722: l_parameter_string varchar2(2000);
723: l_base_table_stmt varchar2(2000);

Line 727: l_lock_object_cubes dbms_sql.varchar2_table;

723: l_base_table_stmt varchar2(2000);
724: l_min_stmt varchar2(2000);
725: l_max_stmt varchar2(2000);
726: l_bt_current_period_stmt varchar2(2000);
727: l_lock_object_cubes dbms_sql.varchar2_table;
728: l_lock_object_limit_cubes dbms_sql.varchar2_table;
729: l_lock_object_reset_cubes dbms_sql.varchar2_table;
730: l_run_id number;
731: l_cubes_to_load dbms_sql.varchar2_table;

Line 728: l_lock_object_limit_cubes dbms_sql.varchar2_table;

724: l_min_stmt varchar2(2000);
725: l_max_stmt varchar2(2000);
726: l_bt_current_period_stmt varchar2(2000);
727: l_lock_object_cubes dbms_sql.varchar2_table;
728: l_lock_object_limit_cubes dbms_sql.varchar2_table;
729: l_lock_object_reset_cubes dbms_sql.varchar2_table;
730: l_run_id number;
731: l_cubes_to_load dbms_sql.varchar2_table;
732: l_measures_to_load dbms_sql.varchar2_table;

Line 729: l_lock_object_reset_cubes dbms_sql.varchar2_table;

725: l_max_stmt varchar2(2000);
726: l_bt_current_period_stmt varchar2(2000);
727: l_lock_object_cubes dbms_sql.varchar2_table;
728: l_lock_object_limit_cubes dbms_sql.varchar2_table;
729: l_lock_object_reset_cubes dbms_sql.varchar2_table;
730: l_run_id number;
731: l_cubes_to_load dbms_sql.varchar2_table;
732: l_measures_to_load dbms_sql.varchar2_table;
733: --

Line 731: l_cubes_to_load dbms_sql.varchar2_table;

727: l_lock_object_cubes dbms_sql.varchar2_table;
728: l_lock_object_limit_cubes dbms_sql.varchar2_table;
729: l_lock_object_reset_cubes dbms_sql.varchar2_table;
730: l_run_id number;
731: l_cubes_to_load dbms_sql.varchar2_table;
732: l_measures_to_load dbms_sql.varchar2_table;
733: --
734: l_aggregation aggregation_r;
735: l_dim_set bsc_aw_adapter_kpi.dim_set_r;

Line 732: l_measures_to_load dbms_sql.varchar2_table;

728: l_lock_object_limit_cubes dbms_sql.varchar2_table;
729: l_lock_object_reset_cubes dbms_sql.varchar2_table;
730: l_run_id number;
731: l_cubes_to_load dbms_sql.varchar2_table;
732: l_measures_to_load dbms_sql.varchar2_table;
733: --
734: l_aggregation aggregation_r;
735: l_dim_set bsc_aw_adapter_kpi.dim_set_r;
736: l_cube_pt bsc_aw_adapter_kpi.partition_template_r;

Line 737: l_bt_considered dbms_sql.varchar2_table;

733: --
734: l_aggregation aggregation_r;
735: l_dim_set bsc_aw_adapter_kpi.dim_set_r;
736: l_cube_pt bsc_aw_adapter_kpi.partition_template_r;
737: l_bt_considered dbms_sql.varchar2_table;
738: l_partition_options varchar2(2000); /*hold partition dim value */
739: Begin
740: bsc_aw_utility.clean_up_jobs('all');
741: for i in 1..p_dimset_oor.count loop

Line 918: l_cubes dbms_sql.varchar2_table;

914: p_bt_current_period varchar2,
915: p_partition_options varchar2,
916: p_run_id number,p_job_name varchar2,p_options varchar2) is
917: --
918: l_cubes dbms_sql.varchar2_table;
919: l_measures dbms_sql.varchar2_table;
920: l_start_lock_objects dbms_sql.varchar2_table;
921: l_end_lock_objects dbms_sql.varchar2_table;
922: l_base_tables dbms_sql.varchar2_table;

Line 919: l_measures dbms_sql.varchar2_table;

915: p_partition_options varchar2,
916: p_run_id number,p_job_name varchar2,p_options varchar2) is
917: --
918: l_cubes dbms_sql.varchar2_table;
919: l_measures dbms_sql.varchar2_table;
920: l_start_lock_objects dbms_sql.varchar2_table;
921: l_end_lock_objects dbms_sql.varchar2_table;
922: l_base_tables dbms_sql.varchar2_table;
923: l_stmt varchar2(4000);

Line 920: l_start_lock_objects dbms_sql.varchar2_table;

916: p_run_id number,p_job_name varchar2,p_options varchar2) is
917: --
918: l_cubes dbms_sql.varchar2_table;
919: l_measures dbms_sql.varchar2_table;
920: l_start_lock_objects dbms_sql.varchar2_table;
921: l_end_lock_objects dbms_sql.varchar2_table;
922: l_base_tables dbms_sql.varchar2_table;
923: l_stmt varchar2(4000);
924: l_min_values dbms_sql.varchar2_table;

Line 921: l_end_lock_objects dbms_sql.varchar2_table;

917: --
918: l_cubes dbms_sql.varchar2_table;
919: l_measures dbms_sql.varchar2_table;
920: l_start_lock_objects dbms_sql.varchar2_table;
921: l_end_lock_objects dbms_sql.varchar2_table;
922: l_base_tables dbms_sql.varchar2_table;
923: l_stmt varchar2(4000);
924: l_min_values dbms_sql.varchar2_table;
925: l_max_values dbms_sql.varchar2_table;

Line 922: l_base_tables dbms_sql.varchar2_table;

918: l_cubes dbms_sql.varchar2_table;
919: l_measures dbms_sql.varchar2_table;
920: l_start_lock_objects dbms_sql.varchar2_table;
921: l_end_lock_objects dbms_sql.varchar2_table;
922: l_base_tables dbms_sql.varchar2_table;
923: l_stmt varchar2(4000);
924: l_min_values dbms_sql.varchar2_table;
925: l_max_values dbms_sql.varchar2_table;
926: l_bt_current_period dbms_sql.varchar2_table;

Line 924: l_min_values dbms_sql.varchar2_table;

920: l_start_lock_objects dbms_sql.varchar2_table;
921: l_end_lock_objects dbms_sql.varchar2_table;
922: l_base_tables dbms_sql.varchar2_table;
923: l_stmt varchar2(4000);
924: l_min_values dbms_sql.varchar2_table;
925: l_max_values dbms_sql.varchar2_table;
926: l_bt_current_period dbms_sql.varchar2_table;
927: l_lock_objects dbms_sql.varchar2_table;
928: l_flag boolean;

Line 925: l_max_values dbms_sql.varchar2_table;

921: l_end_lock_objects dbms_sql.varchar2_table;
922: l_base_tables dbms_sql.varchar2_table;
923: l_stmt varchar2(4000);
924: l_min_values dbms_sql.varchar2_table;
925: l_max_values dbms_sql.varchar2_table;
926: l_bt_current_period dbms_sql.varchar2_table;
927: l_lock_objects dbms_sql.varchar2_table;
928: l_flag boolean;
929: l_ds_parameters dbms_sql.varchar2_table;

Line 926: l_bt_current_period dbms_sql.varchar2_table;

922: l_base_tables dbms_sql.varchar2_table;
923: l_stmt varchar2(4000);
924: l_min_values dbms_sql.varchar2_table;
925: l_max_values dbms_sql.varchar2_table;
926: l_bt_current_period dbms_sql.varchar2_table;
927: l_lock_objects dbms_sql.varchar2_table;
928: l_flag boolean;
929: l_ds_parameters dbms_sql.varchar2_table;
930: --

Line 927: l_lock_objects dbms_sql.varchar2_table;

923: l_stmt varchar2(4000);
924: l_min_values dbms_sql.varchar2_table;
925: l_max_values dbms_sql.varchar2_table;
926: l_bt_current_period dbms_sql.varchar2_table;
927: l_lock_objects dbms_sql.varchar2_table;
928: l_flag boolean;
929: l_ds_parameters dbms_sql.varchar2_table;
930: --
931: l_aggregation aggregation_r;

Line 929: l_ds_parameters dbms_sql.varchar2_table;

925: l_max_values dbms_sql.varchar2_table;
926: l_bt_current_period dbms_sql.varchar2_table;
927: l_lock_objects dbms_sql.varchar2_table;
928: l_flag boolean;
929: l_ds_parameters dbms_sql.varchar2_table;
930: --
931: l_aggregation aggregation_r;
932: l_dim_set bsc_aw_adapter_kpi.dim_set_r;
933: l_partition_options varchar2(2000);

Line 1061: procedure aggregate_kpi_dimset_job(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is

1057:
1058: /*
1059: dbms job wrapper for aggregate_kpi_dimset
1060: */
1061: procedure aggregate_kpi_dimset_job(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is
1062: --
1063: l_job_name varchar2(100);
1064: l_process varchar2(8000);
1065: l_job_status bsc_aw_utility.parallel_job_tb;

Line 1131: p_dim_set dbms_sql.varchar2_table,

1127: then we aggregate standalone targets
1128: */
1129: procedure get_aggregate_dimsets(
1130: p_kpi varchar2,
1131: p_dim_set dbms_sql.varchar2_table,
1132: p_aggregate_dimset out nocopy dbms_sql.varchar2_table
1133: ) is
1134: l_aggregation aggregation_r;
1135: l_agg_flag dbms_sql.varchar2_table;--will be used to eliminate "target" dimsets that have "actual" dimsets aggregating

Line 1132: p_aggregate_dimset out nocopy dbms_sql.varchar2_table

1128: */
1129: procedure get_aggregate_dimsets(
1130: p_kpi varchar2,
1131: p_dim_set dbms_sql.varchar2_table,
1132: p_aggregate_dimset out nocopy dbms_sql.varchar2_table
1133: ) is
1134: l_aggregation aggregation_r;
1135: l_agg_flag dbms_sql.varchar2_table;--will be used to eliminate "target" dimsets that have "actual" dimsets aggregating
1136: Begin

Line 1135: l_agg_flag dbms_sql.varchar2_table;--will be used to eliminate "target" dimsets that have "actual" dimsets aggregating

1131: p_dim_set dbms_sql.varchar2_table,
1132: p_aggregate_dimset out nocopy dbms_sql.varchar2_table
1133: ) is
1134: l_aggregation aggregation_r;
1135: l_agg_flag dbms_sql.varchar2_table;--will be used to eliminate "target" dimsets that have "actual" dimsets aggregating
1136: Begin
1137: --l_aggregation has info on all dimsets for the kpi
1138: set_aggregation(p_kpi,l_aggregation);
1139: --find the actual and target pairs

Line 1299: l_measures dbms_sql.varchar2_table;

1295: do a commit here
1296: */
1297: procedure aggregate_kpi_dimset_actuals(p_kpi varchar2,p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
1298: --
1299: l_measures dbms_sql.varchar2_table;
1300: l_parallel boolean;
1301: l_aggregate_option varchar2(2000);
1302: Begin
1303: --we aggregate all measures that are not BALANCE aggregation

Line 1461: l_measures dbms_sql.varchar2_table;

1457: ) is
1458: --
1459: l_dim_set_reagg bsc_aw_adapter_kpi.dim_set_r;
1460: l_parallel boolean;
1461: l_measures dbms_sql.varchar2_table;
1462: l_lock_object_limit_cubes dbms_sql.varchar2_table;
1463: Begin
1464: if g_debug then
1465: log_n('=====================================');

Line 1462: l_lock_object_limit_cubes dbms_sql.varchar2_table;

1458: --
1459: l_dim_set_reagg bsc_aw_adapter_kpi.dim_set_r;
1460: l_parallel boolean;
1461: l_measures dbms_sql.varchar2_table;
1462: l_lock_object_limit_cubes dbms_sql.varchar2_table;
1463: Begin
1464: if g_debug then
1465: log_n('=====================================');
1466: log('aggregate_kpi_dimset_targets');

Line 1574: p_actual_measures dbms_sql.varchar2_table) is

1570: */
1571: procedure copy_target_to_actual_serial(
1572: p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1573: p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1574: p_actual_measures dbms_sql.varchar2_table) is
1575: --
1576: l_cubes dbms_sql.varchar2_table;
1577: Begin
1578: get_cubes_for_measures(p_actual_measures,p_actual_dim_set,l_cubes);

Line 1576: l_cubes dbms_sql.varchar2_table;

1572: p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1573: p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1574: p_actual_measures dbms_sql.varchar2_table) is
1575: --
1576: l_cubes dbms_sql.varchar2_table;
1577: Begin
1578: get_cubes_for_measures(p_actual_measures,p_actual_dim_set,l_cubes);
1579: copy_target_to_actual(p_actual_dim_set,p_target_dim_set,l_cubes,null,null);
1580: Exception when others then

Line 1600: p_actual_measures dbms_sql.varchar2_table

1596: procedure copy_target_to_actual_job(
1597: p_kpi varchar2,
1598: p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1599: p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1600: p_actual_measures dbms_sql.varchar2_table
1601: ) is
1602: --
1603: l_lock_cubes dbms_sql.varchar2_table;
1604: --

Line 1603: l_lock_cubes dbms_sql.varchar2_table;

1599: p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1600: p_actual_measures dbms_sql.varchar2_table
1601: ) is
1602: --
1603: l_lock_cubes dbms_sql.varchar2_table;
1604: --
1605: l_job_name varchar2(100);
1606: l_process varchar2(8000);
1607: l_job_status bsc_aw_utility.parallel_job_tb;

Line 1609: l_cubes dbms_sql.varchar2_table;

1605: l_job_name varchar2(100);
1606: l_process varchar2(8000);
1607: l_job_status bsc_aw_utility.parallel_job_tb;
1608: --
1609: l_cubes dbms_sql.varchar2_table;
1610: pt_comp dbms_sql.varchar2_table;/*for each l_cubes */
1611: pt_comp_type dbms_sql.varchar2_table;
1612: agg_cubes dbms_sql.varchar2_table;
1613: agg_pt_comp dbms_sql.varchar2_table;

Line 1610: pt_comp dbms_sql.varchar2_table;/for each l_cubes /

1606: l_process varchar2(8000);
1607: l_job_status bsc_aw_utility.parallel_job_tb;
1608: --
1609: l_cubes dbms_sql.varchar2_table;
1610: pt_comp dbms_sql.varchar2_table;/*for each l_cubes */
1611: pt_comp_type dbms_sql.varchar2_table;
1612: agg_cubes dbms_sql.varchar2_table;
1613: agg_pt_comp dbms_sql.varchar2_table;
1614: cube_pt bsc_aw_adapter_kpi.partition_template_r;

Line 1611: pt_comp_type dbms_sql.varchar2_table;

1607: l_job_status bsc_aw_utility.parallel_job_tb;
1608: --
1609: l_cubes dbms_sql.varchar2_table;
1610: pt_comp dbms_sql.varchar2_table;/*for each l_cubes */
1611: pt_comp_type dbms_sql.varchar2_table;
1612: agg_cubes dbms_sql.varchar2_table;
1613: agg_pt_comp dbms_sql.varchar2_table;
1614: cube_pt bsc_aw_adapter_kpi.partition_template_r;
1615: aggregate_options varchar2(2000);

Line 1612: agg_cubes dbms_sql.varchar2_table;

1608: --
1609: l_cubes dbms_sql.varchar2_table;
1610: pt_comp dbms_sql.varchar2_table;/*for each l_cubes */
1611: pt_comp_type dbms_sql.varchar2_table;
1612: agg_cubes dbms_sql.varchar2_table;
1613: agg_pt_comp dbms_sql.varchar2_table;
1614: cube_pt bsc_aw_adapter_kpi.partition_template_r;
1615: aggregate_options varchar2(2000);
1616: l_run_id number;

Line 1613: agg_pt_comp dbms_sql.varchar2_table;

1609: l_cubes dbms_sql.varchar2_table;
1610: pt_comp dbms_sql.varchar2_table;/*for each l_cubes */
1611: pt_comp_type dbms_sql.varchar2_table;
1612: agg_cubes dbms_sql.varchar2_table;
1613: agg_pt_comp dbms_sql.varchar2_table;
1614: cube_pt bsc_aw_adapter_kpi.partition_template_r;
1615: aggregate_options varchar2(2000);
1616: l_run_id number;
1617: Begin

Line 1716: l_cubes dbms_sql.varchar2_table;

1712: --
1713: l_aggregation aggregation_r;
1714: l_actual_dimset bsc_aw_adapter_kpi.dim_set_r;
1715: l_target_dimset bsc_aw_adapter_kpi.dim_set_r;
1716: l_cubes dbms_sql.varchar2_table;
1717: l_lock_objects dbms_sql.varchar2_table;
1718: partition_value varchar2(40);
1719: partition_dim_value varchar2(40);
1720: Begin

Line 1717: l_lock_objects dbms_sql.varchar2_table;

1713: l_aggregation aggregation_r;
1714: l_actual_dimset bsc_aw_adapter_kpi.dim_set_r;
1715: l_target_dimset bsc_aw_adapter_kpi.dim_set_r;
1716: l_cubes dbms_sql.varchar2_table;
1717: l_lock_objects dbms_sql.varchar2_table;
1718: partition_value varchar2(40);
1719: partition_dim_value varchar2(40);
1720: Begin
1721: if p_run_id is not null then

Line 1775: p_actual_cubes dbms_sql.varchar2_table,

1771: */
1772: procedure copy_target_to_actual(
1773: p_actual_dim_set bsc_aw_adapter_kpi.dim_set_r,
1774: p_target_dim_set bsc_aw_adapter_kpi.dim_set_r,
1775: p_actual_cubes dbms_sql.varchar2_table,
1776: p_partition_value varchar2,
1777: p_partition_dim_value varchar2
1778: ) is
1779: --

Line 1889: p_measures dbms_sql.varchar2_table,

1885: */
1886: procedure aggregate_measure_job(
1887: p_kpi varchar2,
1888: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
1889: p_measures dbms_sql.varchar2_table,
1890: p_options varchar2,
1891: p_measure_agg_type varchar2 --normal, balance or formula
1892: ) is
1893: --

Line 1895: l_lock_objects dbms_sql.varchar2_table;

1891: p_measure_agg_type varchar2 --normal, balance or formula
1892: ) is
1893: --
1894: l_kpi_r bsc_aw_adapter_kpi.kpi_r;
1895: l_lock_objects dbms_sql.varchar2_table;
1896: --
1897: l_job_name varchar2(100);
1898: l_process varchar2(8000);
1899: l_job_status bsc_aw_utility.parallel_job_tb;

Line 1905: l_cubes_to_aggregate dbms_sql.varchar2_table;

1901: l_run_id number;
1902: --
1903: l_cube_set bsc_aw_adapter_kpi.cube_set_r;
1904: l_cube_pt bsc_aw_adapter_kpi.partition_template_r;
1905: l_cubes_to_aggregate dbms_sql.varchar2_table;
1906: l_aggregate_options varchar2(2000);
1907: l_cube_for_measure dbms_sql.varchar2_table;
1908: l_measures_for_cube dbms_sql.varchar2_table;
1909: Begin

Line 1907: l_cube_for_measure dbms_sql.varchar2_table;

1903: l_cube_set bsc_aw_adapter_kpi.cube_set_r;
1904: l_cube_pt bsc_aw_adapter_kpi.partition_template_r;
1905: l_cubes_to_aggregate dbms_sql.varchar2_table;
1906: l_aggregate_options varchar2(2000);
1907: l_cube_for_measure dbms_sql.varchar2_table;
1908: l_measures_for_cube dbms_sql.varchar2_table;
1909: Begin
1910: if g_debug then
1911: log('aggregate_measure_job '||p_kpi);

Line 1908: l_measures_for_cube dbms_sql.varchar2_table;

1904: l_cube_pt bsc_aw_adapter_kpi.partition_template_r;
1905: l_cubes_to_aggregate dbms_sql.varchar2_table;
1906: l_aggregate_options varchar2(2000);
1907: l_cube_for_measure dbms_sql.varchar2_table;
1908: l_measures_for_cube dbms_sql.varchar2_table;
1909: Begin
1910: if g_debug then
1911: log('aggregate_measure_job '||p_kpi);
1912: end if;

Line 2009: l_measures dbms_sql.varchar2_table;

2005: p_aggregate_options varchar2,
2006: p_measure_agg_type varchar2, --normal, balance or formula
2007: p_run_id number,p_job_name varchar2,p_options varchar2) is
2008: --
2009: l_measures dbms_sql.varchar2_table;
2010: l_aggregation aggregation_r;
2011: l_dimset bsc_aw_adapter_kpi.dim_set_r;
2012: --
2013: l_lock_objects dbms_sql.varchar2_table;

Line 2013: l_lock_objects dbms_sql.varchar2_table;

2009: l_measures dbms_sql.varchar2_table;
2010: l_aggregation aggregation_r;
2011: l_dimset bsc_aw_adapter_kpi.dim_set_r;
2012: --
2013: l_lock_objects dbms_sql.varchar2_table;
2014: l_partition_value varchar2(200);
2015: l_partition_dim_value varchar2(200);
2016: Begin
2017: if p_run_id is not null then

Line 2080: p_measures dbms_sql.varchar2_table,

2076: */
2077: procedure aggregate_measure(
2078: p_kpi varchar2,
2079: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
2080: p_measures dbms_sql.varchar2_table,
2081: p_aggregate_options varchar2
2082: ) is
2083: --
2084: l_agg_stmt varchar2(4000);

Line 2085: l_flag dbms_sql.varchar2_table;

2081: p_aggregate_options varchar2
2082: ) is
2083: --
2084: l_agg_stmt varchar2(4000);
2085: l_flag dbms_sql.varchar2_table;
2086: l_measures bsc_aw_adapter_kpi.measure_tb;
2087: l_aggregate_flag boolean;
2088: --
2089: l_lock_objects dbms_sql.varchar2_table;

Line 2089: l_lock_objects dbms_sql.varchar2_table;

2085: l_flag dbms_sql.varchar2_table;
2086: l_measures bsc_aw_adapter_kpi.measure_tb;
2087: l_aggregate_flag boolean;
2088: --
2089: l_lock_objects dbms_sql.varchar2_table;
2090: l_cubes_to_aggregate dbms_sql.varchar2_table;
2091: l_cube_set bsc_aw_adapter_kpi.cube_set_tb;
2092: l_partition_value varchar2(200);
2093: l_partition_dim_value varchar2(200);

Line 2090: l_cubes_to_aggregate dbms_sql.varchar2_table;

2086: l_measures bsc_aw_adapter_kpi.measure_tb;
2087: l_aggregate_flag boolean;
2088: --
2089: l_lock_objects dbms_sql.varchar2_table;
2090: l_cubes_to_aggregate dbms_sql.varchar2_table;
2091: l_cube_set bsc_aw_adapter_kpi.cube_set_tb;
2092: l_partition_value varchar2(200);
2093: l_partition_dim_value varchar2(200);
2094: l_agg_map varchar2(200);

Line 2268: p_measures dbms_sql.varchar2_table,

2264: */
2265: procedure aggregate_measure_formula(
2266: p_kpi varchar2,
2267: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
2268: p_measures dbms_sql.varchar2_table,
2269: p_aggregate_options varchar2
2270: ) is
2271: --
2272: l_agg_stmt varchar2(4000);

Line 2273: l_flag dbms_sql.varchar2_table;

2269: p_aggregate_options varchar2
2270: ) is
2271: --
2272: l_agg_stmt varchar2(4000);
2273: l_flag dbms_sql.varchar2_table;
2274: l_measures bsc_aw_adapter_kpi.measure_tb;
2275: l_lock_objects dbms_sql.varchar2_table;
2276: --
2277: l_cube_set bsc_aw_adapter_kpi.cube_set_tb;

Line 2275: l_lock_objects dbms_sql.varchar2_table;

2271: --
2272: l_agg_stmt varchar2(4000);
2273: l_flag dbms_sql.varchar2_table;
2274: l_measures bsc_aw_adapter_kpi.measure_tb;
2275: l_lock_objects dbms_sql.varchar2_table;
2276: --
2277: l_cube_set bsc_aw_adapter_kpi.cube_set_tb;
2278: l_pt_name dbms_sql.varchar2_table;
2279: l_pt_type dbms_sql.varchar2_table;

Line 2278: l_pt_name dbms_sql.varchar2_table;

2274: l_measures bsc_aw_adapter_kpi.measure_tb;
2275: l_lock_objects dbms_sql.varchar2_table;
2276: --
2277: l_cube_set bsc_aw_adapter_kpi.cube_set_tb;
2278: l_pt_name dbms_sql.varchar2_table;
2279: l_pt_type dbms_sql.varchar2_table;
2280: l_partition_value varchar2(200);
2281: l_partition_dim_value varchar2(200);
2282: Begin

Line 2279: l_pt_type dbms_sql.varchar2_table;

2275: l_lock_objects dbms_sql.varchar2_table;
2276: --
2277: l_cube_set bsc_aw_adapter_kpi.cube_set_tb;
2278: l_pt_name dbms_sql.varchar2_table;
2279: l_pt_type dbms_sql.varchar2_table;
2280: l_partition_value varchar2(200);
2281: l_partition_dim_value varchar2(200);
2282: Begin
2283: --in 10g, l_aggregation will be null if this is a new thread. in 9i, this is already set in aggregate_kpi_dimset

Line 2476: l_cubes dbms_sql.varchar2_table;

2472: l_pt_comp varchar2(200);
2473: l_pt_comp_type varchar2(200);
2474: l_partition_value varchar2(200);
2475: l_partition_dim_value varchar2(200);
2476: l_cubes dbms_sql.varchar2_table;
2477: l_measures_to_limit dbms_sql.varchar2_table;
2478: Begin
2479: if bsc_aw_adapter_kpi.is_calendar_aggregated(p_dim_set.calendar) then
2480: push_dim(get_projection_dim(p_dim_set));

Line 2477: l_measures_to_limit dbms_sql.varchar2_table;

2473: l_pt_comp_type varchar2(200);
2474: l_partition_value varchar2(200);
2475: l_partition_dim_value varchar2(200);
2476: l_cubes dbms_sql.varchar2_table;
2477: l_measures_to_limit dbms_sql.varchar2_table;
2478: Begin
2479: if bsc_aw_adapter_kpi.is_calendar_aggregated(p_dim_set.calendar) then
2480: push_dim(get_projection_dim(p_dim_set));
2481: push_dim(p_dim_set.dim);

Line 2712: procedure limit_dim(p_dim varchar2,p_value dbms_sql.varchar2_table,p_mode varchar2) is

2708: raise;
2709: End;
2710:
2711: --limit a given dim to a given set of value
2712: procedure limit_dim(p_dim varchar2,p_value dbms_sql.varchar2_table,p_mode varchar2) is
2713: Begin
2714: for i in 1..p_value.count loop
2715: limit_dim(p_dim,p_value(i),p_mode);
2716: end loop;

Line 3170: p_cubes dbms_sql.varchar2_table,

3166:
3167: --limit the measuredim to the measures that we are going to aggregate
3168: procedure limit_measure_dim(
3169: p_aggmap_operator bsc_aw_adapter_kpi.aggmap_operator_r,
3170: p_cubes dbms_sql.varchar2_table,
3171: p_partition_value varchar2
3172: ) is
3173: Begin
3174: bsc_aw_dbms_aw.execute('limit '||p_aggmap_operator.measure_dim||' to NULL');

Line 3446: procedure load_dim_if_needed(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is

3442: End;
3443:
3444: /*enh needed to load dim on demand. two scenarios. aw kpi created fresh. aw dim are empty. case II view based dim.
3445: */
3446: procedure load_dim_if_needed(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is
3447: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3448: l_dim dbms_sql.varchar2_table;
3449: Begin
3450: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_oor);

Line 3448: l_dim dbms_sql.varchar2_table;

3444: /*enh needed to load dim on demand. two scenarios. aw kpi created fresh. aw dim are empty. case II view based dim.
3445: */
3446: procedure load_dim_if_needed(p_kpi varchar2,p_dim_set dbms_sql.varchar2_table) is
3447: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3448: l_dim dbms_sql.varchar2_table;
3449: Begin
3450: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_kpi,'kpi',l_oor);
3451: for i in 1..l_oor.count loop
3452: if l_oor(i).object_type='kpi dimension set' and (l_oor(i).relation_type='dim set dim' or l_oor(i).relation_type='dim set std dim')

Line 3470: p_dim_levels dbms_sql.varchar2_table,

3466:
3467: procedure dmp_kpi_cubes_into_table(
3468: p_kpi varchar2,
3469: p_dimset varchar2,
3470: p_dim_levels dbms_sql.varchar2_table,
3471: p_table_name varchar2) is
3472: --
3473: l_name varchar2(200);
3474: Begin

Line 3492: p_tables out nocopy dbms_sql.varchar2_table

3488: */
3489: procedure dmp_kpi_cubes_into_table(
3490: p_kpi varchar2,
3491: p_table_name varchar2,
3492: p_tables out nocopy dbms_sql.varchar2_table
3493: ) is
3494: --
3495: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
3496: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;

Line 3499: l_combinations dbms_sql.varchar2_table;

3495: l_oo_dimset bsc_aw_md_wrapper.bsc_olap_object_tb;
3496: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3497: l_dimset varchar2(100);
3498: l_dimset_name varchar2(200);
3499: l_combinations dbms_sql.varchar2_table;
3500: l_combinations_copy dbms_sql.varchar2_table;
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_levels dbms_sql.varchar2_table;
3503: l_values bsc_aw_utility.value_tb;

Line 3500: l_combinations_copy dbms_sql.varchar2_table;

3496: l_oo_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3497: l_dimset varchar2(100);
3498: l_dimset_name varchar2(200);
3499: l_combinations dbms_sql.varchar2_table;
3500: l_combinations_copy dbms_sql.varchar2_table;
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_levels dbms_sql.varchar2_table;
3503: l_values bsc_aw_utility.value_tb;
3504: l_count number;

Line 3501: l_dimensions dbms_sql.varchar2_table;

3497: l_dimset varchar2(100);
3498: l_dimset_name varchar2(200);
3499: l_combinations dbms_sql.varchar2_table;
3500: l_combinations_copy dbms_sql.varchar2_table;
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_levels dbms_sql.varchar2_table;
3503: l_values bsc_aw_utility.value_tb;
3504: l_count number;
3505: Begin

Line 3502: l_levels dbms_sql.varchar2_table;

3498: l_dimset_name varchar2(200);
3499: l_combinations dbms_sql.varchar2_table;
3500: l_combinations_copy dbms_sql.varchar2_table;
3501: l_dimensions dbms_sql.varchar2_table;
3502: l_levels dbms_sql.varchar2_table;
3503: l_values bsc_aw_utility.value_tb;
3504: l_count number;
3505: Begin
3506: bsc_aw_md_api.get_kpi_dimset_actual(p_kpi,l_oo_dimset);

Line 3583: l_lock_objects dbms_sql.varchar2_table;

3579: inc means only go for additional locks.let the existing locks remain as they are
3580: */
3581: procedure lock_dimset_objects(p_kpi varchar2,p_dimset varchar2,p_object_type varchar2,p_lock_type varchar2) is
3582: --
3583: l_lock_objects dbms_sql.varchar2_table;
3584: Begin
3585: get_dimset_objects_to_lock(p_kpi,p_dimset,p_object_type,l_lock_objects);
3586: if p_lock_type='inc' then
3587: bsc_aw_management.get_lock(l_lock_objects,null);

Line 3600: p_lock_objects out nocopy dbms_sql.varchar2_table) is

3596: procedure get_dimset_objects_to_lock(
3597: p_kpi varchar2,
3598: p_dimset varchar2,
3599: p_object_type varchar2,
3600: p_lock_objects out nocopy dbms_sql.varchar2_table) is
3601: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3602: l_objects dbms_sql.varchar2_table;
3603: Begin
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);

Line 3602: l_objects dbms_sql.varchar2_table;

3598: p_dimset varchar2,
3599: p_object_type varchar2,
3600: p_lock_objects out nocopy dbms_sql.varchar2_table) is
3601: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
3602: l_objects dbms_sql.varchar2_table;
3603: Begin
3604: bsc_aw_md_api.get_bsc_olap_object(null,null,p_kpi,'kpi',l_bsc_olap_object);
3605: for i in 1..l_bsc_olap_object.count loop
3606: if l_bsc_olap_object(i).olap_object_type='dimension' and l_bsc_olap_object(i).object_type<>'agg map measure dim'

Line 3645: p_base_table_list dbms_sql.varchar2_table,

3641: in that case l_oor_dimset.count will be 0
3642: */
3643: procedure get_dimset_for_base_table(
3644: p_kpi varchar2,
3645: p_base_table_list dbms_sql.varchar2_table,
3646: p_dim_set out nocopy dbms_sql.varchar2_table
3647: ) is
3648: --
3649: l_oor_dimset bsc_aw_md_wrapper.bsc_olap_object_relation_tb;

Line 3646: p_dim_set out nocopy dbms_sql.varchar2_table

3642: */
3643: procedure get_dimset_for_base_table(
3644: p_kpi varchar2,
3645: p_base_table_list dbms_sql.varchar2_table,
3646: p_dim_set out nocopy dbms_sql.varchar2_table
3647: ) is
3648: --
3649: l_oor_dimset bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3650: Begin

Line 3666: procedure get_base_table_for_dimset(p_kpi varchar2,p_base_table_list dbms_sql.varchar2_table,p_dim_set dbms_sql.varchar2_table,

3662: raise;
3663: End;
3664:
3665: /*given a set of dimsets and B tables, finds out the list of B tables from p_base_table_list that belong to the dimset */
3666: procedure get_base_table_for_dimset(p_kpi varchar2,p_base_table_list dbms_sql.varchar2_table,p_dim_set dbms_sql.varchar2_table,
3667: p_dimset_base_tables out nocopy dbms_sql.varchar2_table) is
3668: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3669: Begin
3670: for i in 1..p_dim_set.count loop

Line 3667: p_dimset_base_tables out nocopy dbms_sql.varchar2_table) is

3663: End;
3664:
3665: /*given a set of dimsets and B tables, finds out the list of B tables from p_base_table_list that belong to the dimset */
3666: procedure get_base_table_for_dimset(p_kpi varchar2,p_base_table_list dbms_sql.varchar2_table,p_dim_set dbms_sql.varchar2_table,
3667: p_dimset_base_tables out nocopy dbms_sql.varchar2_table) is
3668: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3669: Begin
3670: for i in 1..p_dim_set.count loop
3671: l_oor.delete;

Line 3690: p_base_table_list dbms_sql.varchar2_table,

3686: p_base_table_list will have all the base tables.
3687: */
3688: procedure get_kpi_base_tables(
3689: p_kpi varchar2,
3690: p_base_table_list dbms_sql.varchar2_table,
3691: p_kpi_base_tables out nocopy dbms_sql.varchar2_table
3692: ) is
3693: --
3694: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;

Line 3691: p_kpi_base_tables out nocopy dbms_sql.varchar2_table

3687: */
3688: procedure get_kpi_base_tables(
3689: p_kpi varchar2,
3690: p_base_table_list dbms_sql.varchar2_table,
3691: p_kpi_base_tables out nocopy dbms_sql.varchar2_table
3692: ) is
3693: --
3694: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
3695: l_base_table varchar2(100);

Line 3753: p_measures dbms_sql.varchar2_table,

3749: (compressed)
3750: */
3751: procedure get_measure_objects_to_lock(
3752: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
3753: p_measures dbms_sql.varchar2_table,
3754: p_lock_objects out nocopy dbms_sql.varchar2_table
3755: ) is
3756: Begin
3757: for i in 1..p_dim_set.measure.count loop

Line 3754: p_lock_objects out nocopy dbms_sql.varchar2_table

3750: */
3751: procedure get_measure_objects_to_lock(
3752: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
3753: p_measures dbms_sql.varchar2_table,
3754: p_lock_objects out nocopy dbms_sql.varchar2_table
3755: ) is
3756: Begin
3757: for i in 1..p_dim_set.measure.count loop
3758: if bsc_aw_utility.in_array(p_measures,p_dim_set.measure(i).measure) then

Line 3780: p_measures dbms_sql.varchar2_table,

3776: --overloaded. first find dim_set_r, then call get_measure_objects_to_lock
3777: procedure get_measure_objects_to_lock(
3778: p_kpi varchar2,
3779: p_dimset varchar2,
3780: p_measures dbms_sql.varchar2_table,
3781: p_lock_objects out nocopy dbms_sql.varchar2_table
3782: ) is
3783: --
3784: l_aggregation aggregation_r;

Line 3781: p_lock_objects out nocopy dbms_sql.varchar2_table

3777: procedure get_measure_objects_to_lock(
3778: p_kpi varchar2,
3779: p_dimset varchar2,
3780: p_measures dbms_sql.varchar2_table,
3781: p_lock_objects out nocopy dbms_sql.varchar2_table
3782: ) is
3783: --
3784: l_aggregation aggregation_r;
3785: l_index number;

Line 3815: function can_launch_jobs(p_kpi varchar2,p_dimset bsc_aw_adapter_kpi.dim_set_r,p_measures dbms_sql.varchar2_table) return varchar2 is

3811:
3812: /*
3813: we will look at how many cubes are involved and whether partitions are involved
3814: */
3815: function can_launch_jobs(p_kpi varchar2,p_dimset bsc_aw_adapter_kpi.dim_set_r,p_measures dbms_sql.varchar2_table) return varchar2 is
3816: l_cubes dbms_sql.varchar2_table;
3817: l_pt_comp varchar2(200);
3818: l_pt_comp_type varchar2(200);
3819: l_number_jobs number;

Line 3816: l_cubes dbms_sql.varchar2_table;

3812: /*
3813: we will look at how many cubes are involved and whether partitions are involved
3814: */
3815: function can_launch_jobs(p_kpi varchar2,p_dimset bsc_aw_adapter_kpi.dim_set_r,p_measures dbms_sql.varchar2_table) return varchar2 is
3816: l_cubes dbms_sql.varchar2_table;
3817: l_pt_comp varchar2(200);
3818: l_pt_comp_type varchar2(200);
3819: l_number_jobs number;
3820: l_cube_set bsc_aw_adapter_kpi.cube_set_r;

Line 3853: p_measures dbms_sql.varchar2_table,

3849: raise;
3850: End;
3851:
3852: procedure get_cubes_for_measures(
3853: p_measures dbms_sql.varchar2_table,
3854: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
3855: p_cubes out nocopy dbms_sql.varchar2_table) is
3856: l_cube_set bsc_aw_adapter_kpi.cube_set_r;
3857: Begin

Line 3855: p_cubes out nocopy dbms_sql.varchar2_table) is

3851:
3852: procedure get_cubes_for_measures(
3853: p_measures dbms_sql.varchar2_table,
3854: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
3855: p_cubes out nocopy dbms_sql.varchar2_table) is
3856: l_cube_set bsc_aw_adapter_kpi.cube_set_r;
3857: Begin
3858: for i in 1..p_measures.count loop
3859: l_cube_set:=bsc_aw_adapter_kpi.get_cube_set_for_measure(p_measures(i),p_dim_set);

Line 3893: p_b_tables dbms_sql.varchar2_table,

3889: procedure get_ds_BT_parameters(
3890: p_kpi varchar2,
3891: p_dimset varchar2,
3892: p_load_program varchar2,
3893: p_b_tables dbms_sql.varchar2_table,
3894: p_ds_parameters out nocopy dbms_sql.varchar2_table) is
3895: --
3896: l_bt_string varchar2(4000);
3897: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;

Line 3894: p_ds_parameters out nocopy dbms_sql.varchar2_table) is

3890: p_kpi varchar2,
3891: p_dimset varchar2,
3892: p_load_program varchar2,
3893: p_b_tables dbms_sql.varchar2_table,
3894: p_ds_parameters out nocopy dbms_sql.varchar2_table) is
3895: --
3896: l_bt_string varchar2(4000);
3897: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
3898: l_ds_parameters dbms_sql.varchar2_table;

Line 3898: l_ds_parameters dbms_sql.varchar2_table;

3894: p_ds_parameters out nocopy dbms_sql.varchar2_table) is
3895: --
3896: l_bt_string varchar2(4000);
3897: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
3898: l_ds_parameters dbms_sql.varchar2_table;
3899: l_ds_tables dbms_sql.varchar2_table;
3900: l_flag boolean;
3901: l_bt_flag dbms_sql.varchar2_table;
3902: Begin

Line 3899: l_ds_tables dbms_sql.varchar2_table;

3895: --
3896: l_bt_string varchar2(4000);
3897: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
3898: l_ds_parameters dbms_sql.varchar2_table;
3899: l_ds_tables dbms_sql.varchar2_table;
3900: l_flag boolean;
3901: l_bt_flag dbms_sql.varchar2_table;
3902: Begin
3903: l_bt_string:=bsc_aw_utility.make_string_from_list(bsc_aw_utility.order_array(p_b_tables));

Line 3901: l_bt_flag dbms_sql.varchar2_table;

3897: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
3898: l_ds_parameters dbms_sql.varchar2_table;
3899: l_ds_tables dbms_sql.varchar2_table;
3900: l_flag boolean;
3901: l_bt_flag dbms_sql.varchar2_table;
3902: Begin
3903: l_bt_string:=bsc_aw_utility.make_string_from_list(bsc_aw_utility.order_array(p_b_tables));
3904: --
3905: bsc_aw_md_api.get_bsc_olap_object(p_load_program,'dml program',p_kpi,'kpi',l_oo);

Line 4002: p_cubes dbms_sql.varchar2_table,

3998: we fire this for actual and target dimsets*/
3999: procedure check_bt_current_period_change(
4000: p_kpi varchar2,
4001: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4002: p_cubes dbms_sql.varchar2_table,
4003: p_measures dbms_sql.varchar2_table,
4004: p_base_tables dbms_sql.varchar2_table,
4005: p_bt_current_period dbms_sql.varchar2_table,
4006: p_options varchar2

Line 4003: p_measures dbms_sql.varchar2_table,

3999: procedure check_bt_current_period_change(
4000: p_kpi varchar2,
4001: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4002: p_cubes dbms_sql.varchar2_table,
4003: p_measures dbms_sql.varchar2_table,
4004: p_base_tables dbms_sql.varchar2_table,
4005: p_bt_current_period dbms_sql.varchar2_table,
4006: p_options varchar2
4007: ) is

Line 4004: p_base_tables dbms_sql.varchar2_table,

4000: p_kpi varchar2,
4001: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4002: p_cubes dbms_sql.varchar2_table,
4003: p_measures dbms_sql.varchar2_table,
4004: p_base_tables dbms_sql.varchar2_table,
4005: p_bt_current_period dbms_sql.varchar2_table,
4006: p_options varchar2
4007: ) is
4008: --

Line 4005: p_bt_current_period dbms_sql.varchar2_table,

4001: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4002: p_cubes dbms_sql.varchar2_table,
4003: p_measures dbms_sql.varchar2_table,
4004: p_base_tables dbms_sql.varchar2_table,
4005: p_bt_current_period dbms_sql.varchar2_table,
4006: p_options varchar2
4007: ) is
4008: --
4009: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;

Line 4011: l_bt_periodicity dbms_sql.number_table;

4007: ) is
4008: --
4009: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4010: l_cube_measures bsc_aw_adapter_kpi.measure_tb;
4011: l_bt_periodicity dbms_sql.number_table;
4012: l_start_period dbms_sql.varchar2_table;
4013: l_end_period dbms_sql.varchar2_table;
4014: --
4015: type bt_r is record(

Line 4012: l_start_period dbms_sql.varchar2_table;

4008: --
4009: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4010: l_cube_measures bsc_aw_adapter_kpi.measure_tb;
4011: l_bt_periodicity dbms_sql.number_table;
4012: l_start_period dbms_sql.varchar2_table;
4013: l_end_period dbms_sql.varchar2_table;
4014: --
4015: type bt_r is record(
4016: table_name varchar2(100),

Line 4013: l_end_period dbms_sql.varchar2_table;

4009: l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
4010: l_cube_measures bsc_aw_adapter_kpi.measure_tb;
4011: l_bt_periodicity dbms_sql.number_table;
4012: l_start_period dbms_sql.varchar2_table;
4013: l_end_period dbms_sql.varchar2_table;
4014: --
4015: type bt_r is record(
4016: table_name varchar2(100),
4017: periodicity number,

Line 4020: measures dbms_sql.varchar2_table

4016: table_name varchar2(100),
4017: periodicity number,
4018: current_period varchar2(40),
4019: ds_current_period varchar2(40),
4020: measures dbms_sql.varchar2_table
4021: );
4022: type bt_tb is table of bt_r index by pls_integer;
4023: l_base_tables bt_tb;
4024: --

Line 4104: p_bt_periodicity dbms_sql.number_table, /p_bt_periodicity,p_start_period and p_end_period match in count/

4100: p_kpi varchar2,
4101: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4102: p_cube varchar2,
4103: p_measures bsc_aw_adapter_kpi.measure_tb,
4104: p_bt_periodicity dbms_sql.number_table, /*p_bt_periodicity,p_start_period and p_end_period match in count*/
4105: p_start_period dbms_sql.varchar2_table,
4106: p_end_period dbms_sql.varchar2_table,
4107: p_options varchar2 /*contains partition info */
4108: ) is

Line 4105: p_start_period dbms_sql.varchar2_table,

4101: p_dim_set bsc_aw_adapter_kpi.dim_set_r,
4102: p_cube varchar2,
4103: p_measures bsc_aw_adapter_kpi.measure_tb,
4104: p_bt_periodicity dbms_sql.number_table, /*p_bt_periodicity,p_start_period and p_end_period match in count*/
4105: p_start_period dbms_sql.varchar2_table,
4106: p_end_period dbms_sql.varchar2_table,
4107: p_options varchar2 /*contains partition info */
4108: ) is
4109: --

Line 4106: p_end_period dbms_sql.varchar2_table,

4102: p_cube varchar2,
4103: p_measures bsc_aw_adapter_kpi.measure_tb,
4104: p_bt_periodicity dbms_sql.number_table, /*p_bt_periodicity,p_start_period and p_end_period match in count*/
4105: p_start_period dbms_sql.varchar2_table,
4106: p_end_period dbms_sql.varchar2_table,
4107: p_options varchar2 /*contains partition info */
4108: ) is
4109: --
4110: l_prj_measures bsc_aw_adapter_kpi.measure_tb;

Line 4244: function is_parallel_load(p_base_tables dbms_sql.varchar2_table,p_cutoff number) return boolean is

4240: End;
4241:
4242: /*need ability to control when to turn on parallel threads. parallelism enabled for large objects */
4243: /*for loads, we look at stats for the B tables and check the total count . if the stats is current, get an idea of count for this load */
4244: function is_parallel_load(p_base_tables dbms_sql.varchar2_table,p_cutoff number) return boolean is
4245: change_vector dbms_sql.number_table;
4246: Begin
4247: if p_base_tables.count>0 then
4248: for i in 1..p_base_tables.count loop

Line 4245: change_vector dbms_sql.number_table;

4241:
4242: /*need ability to control when to turn on parallel threads. parallelism enabled for large objects */
4243: /*for loads, we look at stats for the B tables and check the total count . if the stats is current, get an idea of count for this load */
4244: function is_parallel_load(p_base_tables dbms_sql.varchar2_table,p_cutoff number) return boolean is
4245: change_vector dbms_sql.number_table;
4246: Begin
4247: if p_base_tables.count>0 then
4248: for i in 1..p_base_tables.count loop
4249: change_vector(i):=bsc_aw_md_api.get_bt_change_vector(p_base_tables(i));

Line 4263: function is_parallel_load(p_base_tables dbms_sql.varchar2_table,p_change_vector dbms_sql.number_table,p_cutoff number) return boolean is

4259: log_n('Exception in is_parallel_load '||sqlerrm);
4260: raise;
4261: End;
4262:
4263: function is_parallel_load(p_base_tables dbms_sql.varchar2_table,p_change_vector dbms_sql.number_table,p_cutoff number) return boolean is
4264: table_count number;
4265: total_table_count number;
4266: Begin
4267: if p_base_tables.count=0 then