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_BSC_METADATA dependencies on DBMS_SQL

Line 5: p_dim_level_list dbms_sql.varchar2_table,

1: package body BSC_AW_BSC_METADATA AS
2: /*$Header: BSCAWMDB.pls 120.16 2006/04/20 11:31 vsurendr noship $*/
3:
4: procedure get_all_parent_child(
5: p_dim_level_list dbms_sql.varchar2_table,
6: p_dim_parent_child out nocopy BSC_AW_ADAPTER_DIM.dim_parent_child_tb,
7: p_dim_levels out nocopy BSC_AW_ADAPTER_DIM.levels_tv
8: ) is
9: --

Line 10: l_level_considered dbms_sql.varchar2_table;

6: p_dim_parent_child out nocopy BSC_AW_ADAPTER_DIM.dim_parent_child_tb,
7: p_dim_levels out nocopy BSC_AW_ADAPTER_DIM.levels_tv
8: ) is
9: --
10: l_level_considered dbms_sql.varchar2_table;
11: --
12: Begin
13: p_dim_parent_child.delete;
14: l_level_considered.delete;

Line 31: p_level_considered in out nocopy dbms_sql.varchar2_table,

27: End;
28:
29: procedure get_parent_children(
30: p_level varchar2,
31: p_level_considered in out nocopy dbms_sql.varchar2_table,
32: p_dim_parent_child in out nocopy BSC_AW_ADAPTER_DIM.dim_parent_child_tb
33: ) is
34: --
35: l_parents BSC_AW_ADAPTER_DIM.dim_parent_child_tb;

Line 90: procedure get_parent_level(p_level varchar2,p_parents out nocopy dbms_sql.varchar2_table) is

86: log_n('Exception in get_parent_children '||sqlerrm);
87: raise;
88: End;
89:
90: procedure get_parent_level(p_level varchar2,p_parents out nocopy dbms_sql.varchar2_table) is
91: l_parents BSC_AW_ADAPTER_DIM.dim_parent_child_tb;
92: Begin
93: bsc_metadata.get_parent_level(p_level,l_parents);
94: for i in 1..l_parents.count loop

Line 104: procedure get_child_level(p_level varchar2,p_children out nocopy dbms_sql.varchar2_table) is

100: log_n('Exception in get_parent_level '||sqlerrm);
101: raise;
102: End;
103:
104: procedure get_child_level(p_level varchar2,p_children out nocopy dbms_sql.varchar2_table) is
105: l_children BSC_AW_ADAPTER_DIM.dim_parent_child_tb;
106: Begin
107: bsc_metadata.get_child_level(p_level,l_children);
108: for i in 1..l_children.count loop

Line 122: p_levels dbms_sql.varchar2_table,

118: /*
119: position is not set here. its set in the dim adapter
120: */
121: procedure get_all_distinct_levels(
122: p_levels dbms_sql.varchar2_table,
123: p_dim_levels out nocopy BSC_AW_ADAPTER_DIM.levels_tv
124: ) is
125: --
126: l_level_id number;

Line 160: l_kpi dbms_sql.varchar2_table;

156: procedure get_kpi_for_dim(
157: p_dim in out nocopy bsc_aw_adapter_dim.dimension_r
158: ) is
159: --
160: l_kpi dbms_sql.varchar2_table;
161: l_dimset dbms_sql.varchar2_table;
162: l_kpi_flag boolean;
163: l_dimset_flag boolean;
164: l_kpi_index number;

Line 161: l_dimset dbms_sql.varchar2_table;

157: p_dim in out nocopy bsc_aw_adapter_dim.dimension_r
158: ) is
159: --
160: l_kpi dbms_sql.varchar2_table;
161: l_dimset dbms_sql.varchar2_table;
162: l_kpi_flag boolean;
163: l_dimset_flag boolean;
164: l_kpi_index number;
165: --

Line 210: p_kpi_list dbms_sql.varchar2_table,

206: End;
207:
208: --given a list of kpi, get the list of all dim levels that are referenced by the kpi
209: procedure get_dims_for_kpis(
210: p_kpi_list dbms_sql.varchar2_table,
211: p_dim_list out nocopy dbms_sql.varchar2_table
212: ) is
213: Begin
214: bsc_metadata.get_dims_for_kpis(p_kpi_list,p_dim_list);

Line 211: p_dim_list out nocopy dbms_sql.varchar2_table

207:
208: --given a list of kpi, get the list of all dim levels that are referenced by the kpi
209: procedure get_dims_for_kpis(
210: p_kpi_list dbms_sql.varchar2_table,
211: p_dim_list out nocopy dbms_sql.varchar2_table
212: ) is
213: Begin
214: bsc_metadata.get_dims_for_kpis(p_kpi_list,p_dim_list);
215: Exception when others then

Line 253: l_level_list dbms_sql.varchar2_table;

249: */
250: procedure create_data_source(
251: p_dimension in out nocopy bsc_aw_adapter_dim.dimension_r
252: ) is
253: l_level_list dbms_sql.varchar2_table;
254: l_level_pk_col dbms_sql.varchar2_table;
255: Begin
256: for i in 1..p_dimension.level_groups(1).levels.count loop
257: l_level_list(i):=p_dimension.level_groups(1).levels(i).level_name;

Line 254: l_level_pk_col dbms_sql.varchar2_table;

250: procedure create_data_source(
251: p_dimension in out nocopy bsc_aw_adapter_dim.dimension_r
252: ) is
253: l_level_list dbms_sql.varchar2_table;
254: l_level_pk_col dbms_sql.varchar2_table;
255: Begin
256: for i in 1..p_dimension.level_groups(1).levels.count loop
257: l_level_list(i):=p_dimension.level_groups(1).levels(i).level_name;
258: end loop;

Line 287: l_kpi_list dbms_sql.varchar2_table;

283: raise;
284: End;
285:
286: procedure get_kpi_for_calendar(p_calendar in out nocopy bsc_aw_calendar.calendar_r) is
287: l_kpi_list dbms_sql.varchar2_table;
288: Begin
289: bsc_metadata.get_kpi_for_calendar(p_calendar.calendar_id,l_kpi_list);
290: for i in 1..l_kpi_list.count loop
291: p_calendar.kpi_for_dim(i).kpi:=l_kpi_list(i);

Line 317: l_dim_set dbms_sql.varchar2_table;

313: given a kpi find out
314: 1. dim sets
315: */
316: procedure get_kpi_dim_sets(p_kpi in out nocopy bsc_aw_adapter_kpi.kpi_r) is
317: l_dim_set dbms_sql.varchar2_table;
318: Begin
319: bsc_metadata.get_kpi_dim_sets(p_kpi.kpi,l_dim_set);
320: --l_dim_set will be numbers like 1,2 etc to make the name unique, append with kpi
321: for i in 1..l_dim_set.count loop

Line 339: p_dim_level out nocopy dbms_sql.varchar2_table,

335: */
336: procedure get_dim_set_dims(
337: p_kpi varchar2,
338: p_dim_set varchar2,
339: p_dim_level out nocopy dbms_sql.varchar2_table,
340: p_mo_dim_group out nocopy dbms_sql.varchar2_table,
341: p_skip_level out nocopy dbms_sql.varchar2_table
342: ) is
343: Begin

Line 340: p_mo_dim_group out nocopy dbms_sql.varchar2_table,

336: procedure get_dim_set_dims(
337: p_kpi varchar2,
338: p_dim_set varchar2,
339: p_dim_level out nocopy dbms_sql.varchar2_table,
340: p_mo_dim_group out nocopy dbms_sql.varchar2_table,
341: p_skip_level out nocopy dbms_sql.varchar2_table
342: ) is
343: Begin
344: bsc_metadata.get_dim_set_dims(p_kpi,p_dim_set,p_dim_level,p_mo_dim_group,p_skip_level);

Line 341: p_skip_level out nocopy dbms_sql.varchar2_table

337: p_kpi varchar2,
338: p_dim_set varchar2,
339: p_dim_level out nocopy dbms_sql.varchar2_table,
340: p_mo_dim_group out nocopy dbms_sql.varchar2_table,
341: p_skip_level out nocopy dbms_sql.varchar2_table
342: ) is
343: Begin
344: bsc_metadata.get_dim_set_dims(p_kpi,p_dim_set,p_dim_level,p_mo_dim_group,p_skip_level);
345: Exception when others then

Line 360: l_measure dbms_sql.varchar2_table;

356: p_kpi varchar2,
357: p_dim_set varchar2,
358: p_measure in out nocopy bsc_aw_adapter_kpi.measure_tb
359: ) is
360: l_measure dbms_sql.varchar2_table;
361: l_measure_type dbms_sql.varchar2_table;
362: l_data_type dbms_sql.varchar2_table;
363: l_agg_formula dbms_sql.varchar2_table;
364: l_projection dbms_sql.varchar2_table;

Line 361: l_measure_type dbms_sql.varchar2_table;

357: p_dim_set varchar2,
358: p_measure in out nocopy bsc_aw_adapter_kpi.measure_tb
359: ) is
360: l_measure dbms_sql.varchar2_table;
361: l_measure_type dbms_sql.varchar2_table;
362: l_data_type dbms_sql.varchar2_table;
363: l_agg_formula dbms_sql.varchar2_table;
364: l_projection dbms_sql.varchar2_table;
365: l_property dbms_sql.varchar2_table;

Line 362: l_data_type dbms_sql.varchar2_table;

358: p_measure in out nocopy bsc_aw_adapter_kpi.measure_tb
359: ) is
360: l_measure dbms_sql.varchar2_table;
361: l_measure_type dbms_sql.varchar2_table;
362: l_data_type dbms_sql.varchar2_table;
363: l_agg_formula dbms_sql.varchar2_table;
364: l_projection dbms_sql.varchar2_table;
365: l_property dbms_sql.varchar2_table;
366: Begin

Line 363: l_agg_formula dbms_sql.varchar2_table;

359: ) is
360: l_measure dbms_sql.varchar2_table;
361: l_measure_type dbms_sql.varchar2_table;
362: l_data_type dbms_sql.varchar2_table;
363: l_agg_formula dbms_sql.varchar2_table;
364: l_projection dbms_sql.varchar2_table;
365: l_property dbms_sql.varchar2_table;
366: Begin
367: p_measure.delete;

Line 364: l_projection dbms_sql.varchar2_table;

360: l_measure dbms_sql.varchar2_table;
361: l_measure_type dbms_sql.varchar2_table;
362: l_data_type dbms_sql.varchar2_table;
363: l_agg_formula dbms_sql.varchar2_table;
364: l_projection dbms_sql.varchar2_table;
365: l_property dbms_sql.varchar2_table;
366: Begin
367: p_measure.delete;
368: bsc_metadata.get_dim_set_measures(p_kpi,p_dim_set,l_measure,l_measure_type,l_data_type,l_agg_formula,l_projection,l_property);

Line 365: l_property dbms_sql.varchar2_table;

361: l_measure_type dbms_sql.varchar2_table;
362: l_data_type dbms_sql.varchar2_table;
363: l_agg_formula dbms_sql.varchar2_table;
364: l_projection dbms_sql.varchar2_table;
365: l_property dbms_sql.varchar2_table;
366: Begin
367: p_measure.delete;
368: bsc_metadata.get_dim_set_measures(p_kpi,p_dim_set,l_measure,l_measure_type,l_data_type,l_agg_formula,l_projection,l_property);
369: for i in 1..l_measure.count loop

Line 395: l_s_views dbms_sql.varchar2_table;

391: given a dimset, get all the S views and their properties
392: gets both ZMV and regular MV
393: */
394: procedure get_s_views(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
395: l_s_views dbms_sql.varchar2_table;
396: l_levels dbms_sql.varchar2_table;
397: l_sview_level bsc_aw_adapter_kpi.level_r;
398: Begin
399: --get_s_views will return only regular MV not ZMV

Line 396: l_levels dbms_sql.varchar2_table;

392: gets both ZMV and regular MV
393: */
394: procedure get_s_views(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
395: l_s_views dbms_sql.varchar2_table;
396: l_levels dbms_sql.varchar2_table;
397: l_sview_level bsc_aw_adapter_kpi.level_r;
398: Begin
399: --get_s_views will return only regular MV not ZMV
400: bsc_metadata.get_s_views(p_kpi,p_dim_set.dim_set,l_s_views);

Line 445: l_levels dbms_sql.varchar2_table;

441: End;
442:
443: --this api called only when there are targets at higher levels
444: procedure get_target_dim_levels(p_kpi varchar2,p_target_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
445: l_levels dbms_sql.varchar2_table;
446: l_dim bsc_aw_adapter_kpi.dim_tb;--just temp backup
447: Begin
448: --backup dim and level info
449: l_dim:=p_target_dim_set.dim;

Line 475: l_periodicities dbms_sql.number_table;

471: End;
472:
473: procedure get_target_dim_periodicity(p_kpi varchar2,p_target_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
474: --
475: l_periodicities dbms_sql.number_table;
476: l_calendar bsc_aw_adapter_kpi.calendar_r;
477: Begin
478: --backup
479: l_calendar:=p_target_dim_set.calendar;

Line 548: l_levels dbms_sql.varchar2_table;

544: /*
545: given a kpi and the dim set, find out the levels and their dim where there is zero code
546: */
547: procedure check_dim_zero_code(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
548: l_levels dbms_sql.varchar2_table;
549: Begin
550: bsc_metadata.get_zero_code_levels(p_kpi,p_dim_set.dim_set,l_levels);
551: for i in 1..p_dim_set.dim.count loop
552: for j in 1..p_dim_set.dim(i).levels.count loop

Line 614: l_base_tables dbms_sql.varchar2_table;

610: day level B table feeds month.
611: if the B table is at a lower periodicity, we need to check which periodicity it feeds
612: */
613: procedure get_dim_set_data_source(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
614: l_base_tables dbms_sql.varchar2_table;
615: l_data_source bsc_aw_adapter_kpi.data_source_r;
616: l_ds_periodicity bsc_aw_adapter_kpi.periodicity_tb;
617: l_ds_copy bsc_aw_adapter_kpi.data_source_r;
618: l_feed_periodicity dbms_sql.number_table;

Line 618: l_feed_periodicity dbms_sql.number_table;

614: l_base_tables dbms_sql.varchar2_table;
615: l_data_source bsc_aw_adapter_kpi.data_source_r;
616: l_ds_periodicity bsc_aw_adapter_kpi.periodicity_tb;
617: l_ds_copy bsc_aw_adapter_kpi.data_source_r;
618: l_feed_periodicity dbms_sql.number_table;
619: Begin
620: if p_dim_set.dim_set_type='actual' then
621: bsc_metadata.get_dim_set_base_tables(p_kpi,p_dim_set.dim_set,l_base_tables);
622: else

Line 726: procedure get_B_table_feed_periodicity(p_kpi varchar2,p_dim_set varchar2,p_base_table varchar2,p_feed_periodicity out nocopy dbms_sql.number_table) is

722: raise;
723: End;
724:
725: --given a B table, what are the target periodicities it feeds
726: procedure get_B_table_feed_periodicity(p_kpi varchar2,p_dim_set varchar2,p_base_table varchar2,p_feed_periodicity out nocopy dbms_sql.number_table) is
727: Begin
728: bsc_metadata.get_B_table_feed_periodicity(p_kpi,p_dim_set,p_base_table,p_feed_periodicity);
729: Exception when others then
730: log_n('Exception in get_B_table_feed_periodicity '||sqlerrm);

Line 745: l_bt_levels dbms_sql.varchar2_table;

741: p_dim_set varchar2,
742: p_base_table varchar2,
743: p_data_source in out nocopy bsc_aw_adapter_kpi.data_source_r) is
744: --
745: l_bt_levels dbms_sql.varchar2_table;
746: l_bt_feed_levels dbms_sql.varchar2_table;
747: l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table
748: l_bt_level_pks dbms_sql.varchar2_table;
749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures

Line 746: l_bt_feed_levels dbms_sql.varchar2_table;

742: p_base_table varchar2,
743: p_data_source in out nocopy bsc_aw_adapter_kpi.data_source_r) is
744: --
745: l_bt_levels dbms_sql.varchar2_table;
746: l_bt_feed_levels dbms_sql.varchar2_table;
747: l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table
748: l_bt_level_pks dbms_sql.varchar2_table;
749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures
750: l_formula dbms_sql.varchar2_table;

Line 747: l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table

743: p_data_source in out nocopy bsc_aw_adapter_kpi.data_source_r) is
744: --
745: l_bt_levels dbms_sql.varchar2_table;
746: l_bt_feed_levels dbms_sql.varchar2_table;
747: l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table
748: l_bt_level_pks dbms_sql.varchar2_table;
749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures
750: l_formula dbms_sql.varchar2_table;
751: l_level bsc_aw_adapter_kpi.level_r;

Line 748: l_bt_level_pks dbms_sql.varchar2_table;

744: --
745: l_bt_levels dbms_sql.varchar2_table;
746: l_bt_feed_levels dbms_sql.varchar2_table;
747: l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table
748: l_bt_level_pks dbms_sql.varchar2_table;
749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures
750: l_formula dbms_sql.varchar2_table;
751: l_level bsc_aw_adapter_kpi.level_r;
752: l_prj_table varchar2(100);

Line 749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures

745: l_bt_levels dbms_sql.varchar2_table;
746: l_bt_feed_levels dbms_sql.varchar2_table;
747: l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table
748: l_bt_level_pks dbms_sql.varchar2_table;
749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures
750: l_formula dbms_sql.varchar2_table;
751: l_level bsc_aw_adapter_kpi.level_r;
752: l_prj_table varchar2(100);
753: l_partition bsc_aw_utility.object_partition_r;

Line 750: l_formula dbms_sql.varchar2_table;

746: l_bt_feed_levels dbms_sql.varchar2_table;
747: l_bt_level_fks dbms_sql.varchar2_table; --contains the fk like city_code etc from the base table
748: l_bt_level_pks dbms_sql.varchar2_table;
749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures
750: l_formula dbms_sql.varchar2_table;
751: l_level bsc_aw_adapter_kpi.level_r;
752: l_prj_table varchar2(100);
753: l_partition bsc_aw_utility.object_partition_r;
754: l_bt_copy bsc_aw_adapter_kpi.base_table_r;

Line 788: l_periodicity dbms_sql.number_table;

784: raise;
785: End;
786:
787: procedure get_dim_set_calendar(p_kpi varchar2,p_dim_set in out nocopy bsc_aw_adapter_kpi.dim_set_r) is
788: l_periodicity dbms_sql.number_table;
789: Begin
790: bsc_metadata.get_kpi_periodicities(p_kpi,p_dim_set.dim_set,l_periodicity);
791: for i in 1..l_periodicity.count loop
792: p_dim_set.calendar.periodicity(p_dim_set.calendar.periodicity.count+1).periodicity:=l_periodicity(i);

Line 856: p_kpi out nocopy dbms_sql.varchar2_table,

852: raise;
853: End;
854:
855: procedure get_all_kpi_in_aw(
856: p_kpi out nocopy dbms_sql.varchar2_table,
857: p_kpi_dimset out nocopy dbms_sql.varchar2_table
858: ) is
859: --
860: l_kpi dbms_sql.varchar2_table;

Line 857: p_kpi_dimset out nocopy dbms_sql.varchar2_table

853: End;
854:
855: procedure get_all_kpi_in_aw(
856: p_kpi out nocopy dbms_sql.varchar2_table,
857: p_kpi_dimset out nocopy dbms_sql.varchar2_table
858: ) is
859: --
860: l_kpi dbms_sql.varchar2_table;
861: l_dimset dbms_sql.varchar2_table;

Line 860: l_kpi dbms_sql.varchar2_table;

856: p_kpi out nocopy dbms_sql.varchar2_table,
857: p_kpi_dimset out nocopy dbms_sql.varchar2_table
858: ) is
859: --
860: l_kpi dbms_sql.varchar2_table;
861: l_dimset dbms_sql.varchar2_table;
862: Begin
863: bsc_metadata.get_all_kpi_in_aw(l_kpi);
864: for i in 1..l_kpi.count loop

Line 861: l_dimset dbms_sql.varchar2_table;

857: p_kpi_dimset out nocopy dbms_sql.varchar2_table
858: ) is
859: --
860: l_kpi dbms_sql.varchar2_table;
861: l_dimset dbms_sql.varchar2_table;
862: Begin
863: bsc_metadata.get_all_kpi_in_aw(l_kpi);
864: for i in 1..l_kpi.count loop
865: l_dimset.delete;

Line 884: p_short_name dbms_sql.varchar2_table,

880: so we decided to do the lookup for them.
881: get_measures_for_short_names and get_dim_levels_for_short_names
882: */
883: procedure get_measures_for_short_names(
884: p_short_name dbms_sql.varchar2_table,
885: p_measure_name out nocopy dbms_sql.varchar2_table
886: ) is
887: Begin
888: bsc_metadata.get_measures_for_short_names(p_short_name,p_measure_name);

Line 885: p_measure_name out nocopy dbms_sql.varchar2_table

881: get_measures_for_short_names and get_dim_levels_for_short_names
882: */
883: procedure get_measures_for_short_names(
884: p_short_name dbms_sql.varchar2_table,
885: p_measure_name out nocopy dbms_sql.varchar2_table
886: ) is
887: Begin
888: bsc_metadata.get_measures_for_short_names(p_short_name,p_measure_name);
889: Exception when others then

Line 895: p_short_name dbms_sql.varchar2_table,

891: raise;
892: End;
893:
894: procedure get_dim_levels_for_short_names(
895: p_short_name dbms_sql.varchar2_table,
896: p_dim_level_name out nocopy dbms_sql.varchar2_table
897: ) is
898: Begin
899: bsc_metadata.get_dim_levels_for_short_names(p_short_name,p_dim_level_name);

Line 896: p_dim_level_name out nocopy dbms_sql.varchar2_table

892: End;
893:
894: procedure get_dim_levels_for_short_names(
895: p_short_name dbms_sql.varchar2_table,
896: p_dim_level_name out nocopy dbms_sql.varchar2_table
897: ) is
898: Begin
899: bsc_metadata.get_dim_levels_for_short_names(p_short_name,p_dim_level_name);
900: Exception when others then