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

Line 323: baseColumns IN dbms_sql.varchar2_table , numBaseColumns IN NUMBER) IS

319: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
320: --***************************************************************************
321: /*
322: PROCEDURE AddInternalColumnInDB(internalColumn IN VARCHAR2, InternalColumnType NUMBER,
323: baseColumns IN dbms_sql.varchar2_table , numBaseColumns IN NUMBER) IS
324: L_Measure_Col BSC_METADATA_OPTIMIZER_PKG.clsmeasureLov;
325: i NUMBER;
326: prjMethod NUMBER;
327:

Line 541: measures dbms_sql.varchar2_table;

537: -- PRIVATE API
538: FUNCTION Get_All_Measures_For_Fact(p_fact IN VARCHAR2, p_dim_set IN NUMBER)
539: return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
540: l_measure_name varchar2(1000);
541: measures dbms_sql.varchar2_table;
542: l_num_measures NUMBER;
543: l_aggregation_method varchar2(1000);
544: l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
545: l_measure_null BSC_DBGEN_STD_METADATA.clsMeasure;

Line 636: p_missing_levels OUT nocopy dbms_sql.varchar2_table ) RETURN boolean AS

632: -- PARAMETERS:
633: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
634: --***************************************************************************
635: function is_parent_1N_any_level(p_child_level IN VARCHAR2, p_parent_level IN VARCHAR2,
636: p_missing_levels OUT nocopy dbms_sql.varchar2_table ) RETURN boolean AS
637: CURSOR cParent IS
638: WITH tree AS
639: (
640: SELECT childlvl.level_Table_name child_lvl

Line 754: p_missing_levels OUT nocopy DBMS_SQL.VARCHAR2_TABLE)

750:
751: Function get_Dimension_Index(p_dimensions IN BSC_DBGEN_STD_METADATA.tab_clsDimension,
752: p_level IN VARCHAR2,
753: p_include_missing_levels IN BOOLEAN,
754: p_missing_levels OUT nocopy DBMS_SQL.VARCHAR2_TABLE)
755: return NUMBER IS
756: l_dimension BSC_DBGEN_STD_METADATA.clsDimension;
757: l_level BSC_DBGEN_STD_METADATA.clsLevel;
758: l_ct NUMBER := 0;

Line 760: l_groups DBMS_SQL.NUMBER_TABLE;

756: l_dimension BSC_DBGEN_STD_METADATA.clsDimension;
757: l_level BSC_DBGEN_STD_METADATA.clsLevel;
758: l_ct NUMBER := 0;
759: j NUMBER := 0;
760: l_groups DBMS_SQL.NUMBER_TABLE;
761: l_missing_levels DBMS_SQL.VARCHAR2_TABLE;
762: BEGIN
763: IF (p_dimensions.count =0 ) THEN
764: return -1;

Line 761: l_missing_levels DBMS_SQL.VARCHAR2_TABLE;

757: l_level BSC_DBGEN_STD_METADATA.clsLevel;
758: l_ct NUMBER := 0;
759: j NUMBER := 0;
760: l_groups DBMS_SQL.NUMBER_TABLE;
761: l_missing_levels DBMS_SQL.VARCHAR2_TABLE;
762: BEGIN
763: IF (p_dimensions.count =0 ) THEN
764: return -1;
765: END IF;

Line 802: l_parents_list DBMS_SQL.NUMBER_TABLE;

798: End ;
799:
800:
801: PROCEDURE insert_parents(p_periodicity IN NUMBER, p_parents IN VARCHAR2, p_periodicity_list IN BSC_DBGEN_STD_METADATA.tab_clsPeriodicity) IS
802: l_parents_list DBMS_SQL.NUMBER_TABLE;
803: l_table_name VARCHAR2(100) := 'bsc_tmp_per_circ_'||userenv('SESSIONID');
804: l_stmt VARCHAR2(1000) := 'INSERT INTO '||l_table_name||'(periodicity, source) values (:1, :2)';
805: l_index NUMBER;
806: l_per_id_list DBMS_SQL.NUMBER_TABLE;

Line 806: l_per_id_list DBMS_SQL.NUMBER_TABLE;

802: l_parents_list DBMS_SQL.NUMBER_TABLE;
803: l_table_name VARCHAR2(100) := 'bsc_tmp_per_circ_'||userenv('SESSIONID');
804: l_stmt VARCHAR2(1000) := 'INSERT INTO '||l_table_name||'(periodicity, source) values (:1, :2)';
805: l_index NUMBER;
806: l_per_id_list DBMS_SQL.NUMBER_TABLE;
807: BEGIN
808: l_parents_list := bsc_mo_helper_pkg.decomposeStringtoNumber(p_parents, ',');
809: IF (p_periodicity_list.count>0) THEN
810: FOR i IN p_periodicity_list.first..p_periodicity_list.last LOOP

Line 880: FUNCTION Get_Fact_Info(p_process_id IN NUMBER, p_prototype_flag IN NUMBER, p_fact_list IN DBMS_SQL.NUMBER_TABLE ) return BSC_DBGEN_STD_METADATA.tab_clsFact IS

876: RAISE;
877: END;
878:
879:
880: FUNCTION Get_Fact_Info(p_process_id IN NUMBER, p_prototype_flag IN NUMBER, p_fact_list IN DBMS_SQL.NUMBER_TABLE ) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
881: l_stmt varchar2(1000);
882: l_Code number;
883: l_Name BSC_KPIS_VL.NAME%TYPE;
884: l_IndicatorType number;

Line 947: l_id_list DBMS_SQL.NUMBER_TABLE;

943: RAISE;
944:
945: END;
946: FUNCTION Get_Fact_Info(p_process_id IN NUMBER, p_prototype_flag IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
947: l_id_list DBMS_SQL.NUMBER_TABLE;
948: BEGIN
949: return Get_Fact_Info(p_process_id, p_prototype_flag, l_id_list);
950: END;
951:

Line 968: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;

964: strWhereInIndics Varchar2(1000);
965: strWhereNotInIndics Varchar2(1000);
966: strWhereInMeasures Varchar2(1000);
967: i NUMBER := 0;
968: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
969: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
970:
971: --measureCol Varchar2(1000);
972: Operands DBMS_SQL.VARCHAR2_TABLE;

Line 969: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;

965: strWhereNotInIndics Varchar2(1000);
966: strWhereInMeasures Varchar2(1000);
967: i NUMBER := 0;
968: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
969: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
970:
971: --measureCol Varchar2(1000);
972: Operands DBMS_SQL.VARCHAR2_TABLE;
973: NumOperands NUMBER;

Line 972: Operands DBMS_SQL.VARCHAR2_TABLE;

968: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
969: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
970:
971: --measureCol Varchar2(1000);
972: Operands DBMS_SQL.VARCHAR2_TABLE;
973: NumOperands NUMBER;
974: l_measureID NUMBER;
975: l_measureCol VARCHAR2(500);
976: TYPE CurTyp IS REF CURSOR;

Line 983: l_fact_ids DBMS_SQL.NUMBER_TABLE;

979: l_error varchar2(400);
980: l_indicator_id NUMBER;
981: l_fact_list BSC_DBGEN_STD_METADATA.tab_clsFact;
982: l_fact BSC_DBGEN_STD_METADATA.clsFact;
983: l_fact_ids DBMS_SQL.NUMBER_TABLE;
984: l_num_measures NUMBER;
985: BEGIN
986: l_fact_list := get_fact_info(p_process_id, 4);
987: IF (l_fact_list.count = 0) THEN

Line 1157: measures dbms_sql.varchar2_table;

1153:
1154: FUNCTION Get_Measures_For_Fact_dbgen(p_fact IN VARCHAR2, p_dim_set IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
1155: l_stmt VARCHAR2(1000);
1156: l_measure_name varchar2(1000);
1157: measures dbms_sql.varchar2_table;
1158: l_num_measures NUMBER;
1159: l_aggregation_method varchar2(1000);
1160: l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
1161: l_measure_null BSC_DBGEN_STD_METADATA.clsMeasure;

Line 1174: baseColumn dbms_sql.varchar2_table;

1170: FuncAgregSingleColumn VARCHAR2(1000);
1171: pAvgLSingleColumn VARCHAR2(1000);
1172: AvgLTotalColumn VARCHAR2(1000);
1173: AvgLCounterColumn VARCHAR2(1000);
1174: baseColumn dbms_sql.varchar2_table;
1175:
1176: l_stmt2 VARCHAR2(1000):= 'SELECT M.MEASURE_COL, NVL(M.OPERATION, ''SUM'') AS OPER,
1177: BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :1) AS PFORMULASOURCE,
1178: NVL(BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :2 ),''N'') AS PAVGL,

Line 1384: l_measures_list DBMS_SQL.VARCHAR2_TABLE;

1380: AND sysm.measure_col = p_col;
1381: l_measure_row cProperties%ROWTYPE;
1382: ColMeasures BSC_DBGEN_STD_METADATA.tab_clsMeasure;
1383: l_num_measures NUMBER;
1384: l_measures_list DBMS_SQL.VARCHAR2_TABLE;
1385: l_measure BSC_DBGEN_STD_METADATA.clsMeasure;
1386: l_measure_null BSC_DBGEN_STD_METADATA.clsMeasure;
1387:
1388: BEGIN

Line 1486: l_missing_levels DBMS_SQL.VARCHAR2_TABLE;

1482: RETURN BSC_DBGEN_STD_METADATA.tab_clsDimension IS
1483:
1484: l_dimensions BSC_DBGEN_STD_METADATA.tab_clsDimension;
1485: DimensionLevels BSC_DBGEN_STD_METADATA.clsDimension;
1486: l_missing_levels DBMS_SQL.VARCHAR2_TABLE;
1487: cLevel BSC_DBGEN_STD_METADATA.clsLevel;
1488:
1489: Level_null BSC_DBGEN_STD_METADATA.clsLevel;
1490: Parents1N varchar2(1000);

Line 1721: function get_facts_for_levels(p_levels dbms_sql.varchar2_table) return BSC_DBGEN_STD_METADATA.tab_clsFact is

1717: fnd_file.put_line(FND_FILE.LOG, 'Error in BSC_DBGEN_BSC_READER.Get_Level_Info:level='||p_level||', error='||sqlerrm);
1718: raise;
1719: End;
1720:
1721: function get_facts_for_levels(p_levels dbms_sql.varchar2_table) return BSC_DBGEN_STD_METADATA.tab_clsFact is
1722: l_facts BSC_DBGEN_STD_METADATA.tab_clsFact ;
1723: l_fact BSC_DBGEN_STD_METADATA.clsFact;
1724: l_stmt VARCHAR2(1000);
1725: TYPE CurTyp IS REF CURSOR;

Line 1727: l_dim_sets DBMS_SQL.NUMBER_TABLE;

1723: l_fact BSC_DBGEN_STD_METADATA.clsFact;
1724: l_stmt VARCHAR2(1000);
1725: TYPE CurTyp IS REF CURSOR;
1726: cv CurTyp;
1727: l_dim_sets DBMS_SQL.NUMBER_TABLE;
1728: Begin
1729: IF (p_levels.count=0) THEN
1730: return l_facts;
1731: END IF;

Line 1767: Function get_dim_sets_for_fact(p_fact IN VARCHAR2) return DBMS_SQL.NUMBER_TABLE IS

1763: raise;
1764: End;
1765:
1766:
1767: Function get_dim_sets_for_fact(p_fact IN VARCHAR2) return DBMS_SQL.NUMBER_TABLE IS
1768: l_dim_sets dbms_sql.number_table;
1769: l_dim_set NUMBER;
1770: CURSOR cDimSets IS
1771: SELECT DISTINCT DIM_SET_ID

Line 1768: l_dim_sets dbms_sql.number_table;

1764: End;
1765:
1766:
1767: Function get_dim_sets_for_fact(p_fact IN VARCHAR2) return DBMS_SQL.NUMBER_TABLE IS
1768: l_dim_sets dbms_sql.number_table;
1769: l_dim_set NUMBER;
1770: CURSOR cDimSets IS
1771: SELECT DISTINCT DIM_SET_ID
1772: FROM BSC_DB_DATASET_DIM_SETS_V

Line 1796: return dbms_sql.varchar2_table is

1792:
1793: function get_s_views(
1794: p_fact IN VARCHAR2,
1795: p_dim_set IN NUMBER)
1796: return dbms_sql.varchar2_table is
1797: cursor cList is
1798: select distinct mv_name from bsc_kpi_data_tables
1799: where indicator = to_number(p_fact)
1800: and dim_set_id = p_dim_set

Line 1802: l_mv_list dbms_sql.varchar2_table ;

1798: select distinct mv_name from bsc_kpi_data_tables
1799: where indicator = to_number(p_fact)
1800: and dim_set_id = p_dim_set
1801: and mv_name not like 'BSC_S_%ZMV';
1802: l_mv_list dbms_sql.varchar2_table ;
1803: Begin
1804: FOR i IN cList LOOP
1805: l_mv_list(l_mv_list.count+1) := i.mv_name;
1806: END LOOP;

Line 2114: p_targets in boolean) return dbms_sql.varchar2_table is

2110:
2111: function get_base_tables_for_dim_set(
2112: p_fact in varchar2,
2113: p_dim_set in number,
2114: p_targets in boolean) return dbms_sql.varchar2_table is
2115: CURSOR cBTables(p_prefix VARCHAR2) IS
2116: select distinct rels.table_name
2117: from bsc_db_Tables_rels rels,
2118: bsc_db_tables src

Line 2131: l_table_list dbms_sql.varchar2_table ;

2127: WHERE table_name like p_prefix||p_fact||'_'||p_dim_set||'%'
2128: AND source_table_name not like 'BSC_S%'
2129: ) ;
2130:
2131: l_table_list dbms_sql.varchar2_table ;
2132: l_prefix varchar2(10) := 'BSC_S_';
2133: -- this may be because of different lowest level periodicities or different measures from diff. tables
2134:
2135: Begin

Line 2315: l_number_table DBMS_SQL.NUMBER_TABLE;

2311: l_current_year NUMBER;
2312: l_source_periodicities VARCHAR2(1000);
2313: l_db_col VARCHAR2(100);
2314: l_cal_id NUMBER;
2315: l_number_table DBMS_SQL.NUMBER_TABLE;
2316:
2317:
2318: Begin
2319: OPEN cCurrentPeriod;

Line 2382: l_dim_sets dbms_sql.number_table;

2378: -- this API is only called from the BSC Metadata Optimizer UI for AW support
2379: function is_projection_enabled_for_kpi(
2380: p_kpi in varchar2
2381: ) return varchar2 is
2382: l_dim_sets dbms_sql.number_table;
2383: l_measures BSC_DBGEN_STD_METADATA.tab_clsMeasure;
2384: l_properties BSC_DBGEN_STD_METADATA.tab_ClsProperties;
2385: Begin
2386: l_dim_sets := bsc_dbgen_metadata_reader.get_dim_sets_for_fact(p_kpi);

Line 2408: function get_all_facts_in_aw return dbms_sql.varchar2_table is

2404: raise;
2405:
2406: End;
2407:
2408: function get_all_facts_in_aw return dbms_sql.varchar2_table is
2409: CURSOR cAWFacts IS
2410: SELECT kpi.indicator
2411: FROM BSC_KPIS_VL KPI,
2412: BSC_KPI_PROPERTIES PROP

Line 2416: l_facts DBMS_SQL.VARCHAR2_TABLE;

2412: BSC_KPI_PROPERTIES PROP
2413: WHERE KPI.INDICATOR = PROP.INDICATOR
2414: AND PROP.PROPERTY_CODE = BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE
2415: AND PROP.PROPERTY_VALUE = '2';
2416: l_facts DBMS_SQL.VARCHAR2_TABLE;
2417: Begin
2418: FOR i IN cAWFacts
2419: LOOP
2420: l_facts(l_facts.count+1) := i.indicator;

Line 2433: return dbms_sql.varchar2_table is

2429: --get the ZMV for a kpi and dimset
2430: function get_z_s_views(
2431: p_fact IN VARCHAR2,
2432: p_dim_set IN NUMBER)
2433: return dbms_sql.varchar2_table is
2434: cursor cList is
2435: SELECT DISTINCT mv_name
2436: FROM bsc_kpi_data_tables
2437: WHERE indicator = to_number(p_fact)

Line 2440: l_mv_list dbms_sql.varchar2_table ;

2436: FROM bsc_kpi_data_tables
2437: WHERE indicator = to_number(p_fact)
2438: AND dim_set_id = p_dim_set
2439: AND mv_name like 'BSC%ZMV';
2440: l_mv_list dbms_sql.varchar2_table ;
2441: Begin
2442: FOR i IN cList LOOP
2443: l_mv_list(l_mv_list.count+1) := i.mv_name;
2444: END LOOP;

Line 2453: RETURN DBMS_SQL.VARCHAR2_TABLE IS

2449:
2450: End;
2451:
2452: Function get_all_levels_for_fact(p_fact IN VARCHAR2)
2453: RETURN DBMS_SQL.VARCHAR2_TABLE IS
2454: l_stmt VARCHAR2(1000);
2455: l_level VARCHAR2(1000);
2456: l_level_index number;
2457: l_return DBMS_SQL.VARCHAR2_TABLE;

Line 2457: l_return DBMS_SQL.VARCHAR2_TABLE;

2453: RETURN DBMS_SQL.VARCHAR2_TABLE IS
2454: l_stmt VARCHAR2(1000);
2455: l_level VARCHAR2(1000);
2456: l_level_index number;
2457: l_return DBMS_SQL.VARCHAR2_TABLE;
2458: TYPE CurTyp IS REF CURSOR;
2459: cv CurTyp;
2460: BEGIN
2461: l_stmt := 'SELECT DISTINCT LEVEL_TABLE_NAME, DIM_LEVEL_INDEX ' ||

Line 2496: function get_measures_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is

2492: return l_short_name;
2493: END;
2494:
2495:
2496: function get_measures_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
2497: l_measure_cols dbms_sql.varchar2_table;
2498: l_session_id number := userenv('SESSIONID');
2499: --l_counter number := 0;
2500: l_index number;

Line 2497: l_measure_cols dbms_sql.varchar2_table;

2493: END;
2494:
2495:
2496: function get_measures_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
2497: l_measure_cols dbms_sql.varchar2_table;
2498: l_session_id number := userenv('SESSIONID');
2499: --l_counter number := 0;
2500: l_index number;
2501: l_variable_id number := 1;

Line 2535: function get_dim_levels_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is

2531: end;
2532:
2533:
2534:
2535: function get_dim_levels_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
2536: l_dim_levels dbms_sql.varchar2_table;
2537: l_session_id number := userenv('SESSIONID');
2538: --l_counter number := 0;
2539: l_index number;

Line 2536: l_dim_levels dbms_sql.varchar2_table;

2532:
2533:
2534:
2535: function get_dim_levels_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
2536: l_dim_levels dbms_sql.varchar2_table;
2537: l_session_id number := userenv('SESSIONID');
2538: --l_counter number := 0;
2539: l_index number;
2540: l_variable_id number := 1;

Line 2761: function get_target_per_for_b_table(p_fact in varchar2, p_dim_set in number, p_b_table in varchar2) return dbms_sql.varchar2_table is

2757: raise;
2758: END;
2759:
2760:
2761: function get_target_per_for_b_table(p_fact in varchar2, p_dim_set in number, p_b_table in varchar2) return dbms_sql.varchar2_table is
2762: cursor cTgtPeriodicities is
2763: select periodicity_id from bsc_db_tables where table_name in
2764: (
2765: select distinct table_name from bsc_db_tables_rels rels

Line 2773: l_periodicities dbms_sql.varchar2_table;

2769: connect by prior rels.table_name=rels.source_table_name
2770: and rels.relation_type<>2
2771: start with rels.source_table_name = p_b_table
2772: );
2773: l_periodicities dbms_sql.varchar2_table;
2774: l_stmt varchar2(1000);
2775: TYPE CurTyp IS REF CURSOR;
2776: cv CurTyp;
2777: l_pattern varchar2(30);

Line 2854: l_var1 dbms_sql.varchar2_table;

2850: where table_name = p_table_name
2851: and column_type in(p_col_type1, p_col_type2);
2852:
2853:
2854: l_var1 dbms_sql.varchar2_table;
2855: l_var2 dbms_sql.varchar2_table;
2856: l_var3 dbms_sql.varchar2_table;
2857: l_var4 dbms_sql.varchar2_table;
2858:

Line 2855: l_var2 dbms_sql.varchar2_table;

2851: and column_type in(p_col_type1, p_col_type2);
2852:
2853:
2854: l_var1 dbms_sql.varchar2_table;
2855: l_var2 dbms_sql.varchar2_table;
2856: l_var3 dbms_sql.varchar2_table;
2857: l_var4 dbms_sql.varchar2_table;
2858:
2859: l_col_maps_table varchar2(100) := 'BSC_TMP_OPT_COL_MAPS';

Line 2856: l_var3 dbms_sql.varchar2_table;

2852:
2853:
2854: l_var1 dbms_sql.varchar2_table;
2855: l_var2 dbms_sql.varchar2_table;
2856: l_var3 dbms_sql.varchar2_table;
2857: l_var4 dbms_sql.varchar2_table;
2858:
2859: l_col_maps_table varchar2(100) := 'BSC_TMP_OPT_COL_MAPS';
2860: l_stmt varchar2(1000) :='create global temporary table '||l_col_maps_table||' (column_name varchar2(100), source_column_name varchar2(100), table_name varchar2(100), source_table_name varchar2(100))';

Line 2857: l_var4 dbms_sql.varchar2_table;

2853:
2854: l_var1 dbms_sql.varchar2_table;
2855: l_var2 dbms_sql.varchar2_table;
2856: l_var3 dbms_sql.varchar2_table;
2857: l_var4 dbms_sql.varchar2_table;
2858:
2859: l_col_maps_table varchar2(100) := 'BSC_TMP_OPT_COL_MAPS';
2860: l_stmt varchar2(1000) :='create global temporary table '||l_col_maps_table||' (column_name varchar2(100), source_column_name varchar2(100), table_name varchar2(100), source_table_name varchar2(100))';
2861: PRAGMA AUTONOMOUS_TRANSACTION;