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

Line 457: PROCEDURE write_this (pTable IN DBMS_SQL.VARCHAR2_TABLE, pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT, pForce IN boolean default false) IS

453: l_count := pTable.next(l_count);
454: END LOOP;
455: END;
456: /*
457: PROCEDURE write_this (pTable IN DBMS_SQL.VARCHAR2_TABLE, pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT, pForce IN boolean default false) IS
458: l_count NUMBER;
459: l_string VARCHAR2(4000);
460: BEGIN
461: IF (pTable.count=0) THEN

Line 488: pTable IN DBMS_SQL.VARCHAR2_TABLE,

484: END;
485: */
486:
487: PROCEDURE write_this (
488: pTable IN DBMS_SQL.VARCHAR2_TABLE,
489: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
490: pForce IN boolean default false) IS
491: l_count NUMBER;
492: l_string VARCHAR2(4000);

Line 512: pTable IN DBMS_SQL.NUMBER_TABLE,

508:
509:
510:
511: PROCEDURE write_this (
512: pTable IN DBMS_SQL.NUMBER_TABLE,
513: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
514: pForce IN boolean default false)
515: IS
516: l_count NUMBER;

Line 1758: Function FindIndex(arrNum IN dbms_sql.NUMBER_TABLE, num IN NUMBER) RETURN NUMBER IS

1754: -- PARAMETERS:
1755: -- arrNum: Array to be searched
1756: -- num: Value to be searched within the array
1757: --****************************************************************************
1758: Function FindIndex(arrNum IN dbms_sql.NUMBER_TABLE, num IN NUMBER) RETURN NUMBER IS
1759: l_count Number := 0;
1760: BEGIN
1761:
1762: l_count := arrNum.Count;

Line 1790: Function FindIndexVARCHAR2(arrStr IN dbms_sql.varchar2_table, str in varchar2) return NUMBER IS

1786: -- ArrStr : array
1787: -- str: string to look for
1788: --***************************************************************************
1789:
1790: Function FindIndexVARCHAR2(arrStr IN dbms_sql.varchar2_table, str in varchar2) return NUMBER IS
1791: i NUMBER :=0;
1792: BEGIN
1793:
1794: IF (arrStr.count =0) THEN

Line 3061: PROCEDURE Add_Value_BULK(x_variable_id IN NUMBER, x_value IN DBMS_SQL.NUMBER_TABLE) IS

3057: writeTmp( 'Exception in Get_New_Big_In_Cond_Number :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
3058: RAISE;
3059: End;
3060:
3061: PROCEDURE Add_Value_BULK(x_variable_id IN NUMBER, x_value IN DBMS_SQL.NUMBER_TABLE) IS
3062:
3063: BEGIN
3064: IF (x_value.count=0) THEN
3065: return;

Line 3076: PROCEDURE Add_Value_BULK(x_variable_id IN NUMBER, x_value IN DBMS_SQL.VARCHAR2_TABLE) IS

3072: writeTmp('exception in add_value_bulk for number table', FND_LOG.LEVEL_EXCEPTION, true);
3073: raise;
3074: END;
3075:
3076: PROCEDURE Add_Value_BULK(x_variable_id IN NUMBER, x_value IN DBMS_SQL.VARCHAR2_TABLE) IS
3077:
3078: BEGIN
3079: IF (x_value.count=0) THEN
3080: return;

Line 3165: PROCEDURE InsertRelatedTables(arrTables in dbms_Sql.varchar2_table,

3161: -- arrTables().
3162: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3163: --****************************************************************************/
3164:
3165: PROCEDURE InsertRelatedTables(arrTables in dbms_Sql.varchar2_table,
3166: numTables in number) IS
3167:
3168: arrNewTables dbms_sql.varchar2_table;
3169: numNewTables number := 0;

Line 3168: arrNewTables dbms_sql.varchar2_table;

3164:
3165: PROCEDURE InsertRelatedTables(arrTables in dbms_Sql.varchar2_table,
3166: numTables in number) IS
3167:
3168: arrNewTables dbms_sql.varchar2_table;
3169: numNewTables number := 0;
3170: strWhereInNewTables varchar2(1000);
3171: strWhereNotInNewTables varchar2(1000);
3172: l_stmt varchar2(1000);

Line 3183: l_arr_tables dbms_sql.varchar2_table;

3179:
3180: l_error varchar2(1000);
3181: l_test number;
3182:
3183: l_arr_tables dbms_sql.varchar2_table;
3184: BEGIN
3185: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3186: writeTmp( 'Inside InsertRelatedTables, numTables='||numTables, fnd_log.level_procedure, false);
3187: END IF;

Line 3354: Function GetFieldExpression(FieldExpresion IN OUT NOCOPY dbms_sql.varchar2_table, Expresion IN VARCHAR2)

3350: -- CamposExpresion(): array to be populated
3351: -- Expresion: expression
3352: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3353: --***************************************************************************/
3354: Function GetFieldExpression(FieldExpresion IN OUT NOCOPY dbms_sql.varchar2_table, Expresion IN VARCHAR2)
3355: return NUMBER is
3356:
3357: i number;
3358: NumFieldsExpresion varchar2(1000);

Line 3359: fields dbms_sql.varchar2_table;

3355: return NUMBER is
3356:
3357: i number;
3358: NumFieldsExpresion varchar2(1000);
3359: fields dbms_sql.varchar2_table;
3360: NumFields number;
3361: cExpresion varchar2(1000);
3362: l_error VARCHAR2(400);
3363: BEGIN

Line 3425: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;

3421: strWhereInIndics Varchar2(1000);
3422: strWhereNotInIndics Varchar2(1000);
3423: strWhereInMeasures Varchar2(1000);
3424: i NUMBER := 0;
3425: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
3426: arrMeasures_src DBMS_SQL.VARCHAR2_TABLE;
3427: numMeasures NUMBER;
3428: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
3429:

Line 3426: arrMeasures_src DBMS_SQL.VARCHAR2_TABLE;

3422: strWhereNotInIndics Varchar2(1000);
3423: strWhereInMeasures Varchar2(1000);
3424: i NUMBER := 0;
3425: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
3426: arrMeasures_src DBMS_SQL.VARCHAR2_TABLE;
3427: numMeasures NUMBER;
3428: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
3429:
3430: --measureCol Varchar2(1000);

Line 3428: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;

3424: i NUMBER := 0;
3425: arrMeasuresCols DBMS_SQL.VARCHAR2_TABLE;
3426: arrMeasures_src DBMS_SQL.VARCHAR2_TABLE;
3427: numMeasures NUMBER;
3428: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
3429:
3430: --measureCol Varchar2(1000);
3431: Operands DBMS_SQL.VARCHAR2_TABLE;
3432: NumOperands NUMBER;

Line 3431: Operands DBMS_SQL.VARCHAR2_TABLE;

3427: numMeasures NUMBER;
3428: arrRelatedMeasuresIds DBMS_SQL.NUMBER_TABLE;
3429:
3430: --measureCol Varchar2(1000);
3431: Operands DBMS_SQL.VARCHAR2_TABLE;
3432: NumOperands NUMBER;
3433: l_measureID NUMBER;
3434: l_measureCol VARCHAR2(1000);
3435: l_source VARCHAR2(1000);

Line 3608: FUNCTION decomposeString(p_string IN VARCHAR2, p_separator IN VARCHAR2, p_return_array OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE)

3604: -- p_separator: Separator
3605: -- p_return_array: return array
3606: --****************************************************************************
3607:
3608: FUNCTION decomposeString(p_string IN VARCHAR2, p_separator IN VARCHAR2, p_return_array OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE)
3609: return NUMBER IS
3610:
3611: l_str VARCHAR2(32000);
3612: --l_substr VARCHAR2(4000) := null;

Line 3685: return DBMS_SQL.NUMBER_TABLE IS

3681: --****************************************************************************
3682:
3683:
3684: FUNCTION decomposeStringtoNumber(p_string IN VARCHAR2, p_separator IN VARCHAR2 )
3685: return DBMS_SQL.NUMBER_TABLE IS
3686:
3687: l_str VARCHAR2(32000);
3688: l_substr VARCHAR2(32000);
3689: position NUMBER;

Line 3691: l_return_array DBMS_SQL.NUMBER_TABLE;

3687: l_str VARCHAR2(32000);
3688: l_substr VARCHAR2(32000);
3689: position NUMBER;
3690: l_count NUMBER;
3691: l_return_array DBMS_SQL.NUMBER_TABLE;
3692: l_error VARCHAR2(400);
3693: BEGIN
3694:
3695: IF p_string IS NOT NULL THEN

Line 3732: DBMS_SQL.VARCHAR2_TABLE IS

3728: --****************************************************************************
3729:
3730:
3731: FUNCTION getDecomposedString(p_string IN VARCHAR2, p_separator IN VARCHAR2) RETURN
3732: DBMS_SQL.VARCHAR2_TABLE IS
3733: l_table DBMS_SQL.VARCHAR2_TABLE ;
3734: l_dummy NUMBER;
3735: BEGIN
3736: l_dummy := decomposeString(p_string, p_separator, l_table);

Line 3733: l_table DBMS_SQL.VARCHAR2_TABLE ;

3729:
3730:
3731: FUNCTION getDecomposedString(p_string IN VARCHAR2, p_separator IN VARCHAR2) RETURN
3732: DBMS_SQL.VARCHAR2_TABLE IS
3733: l_table DBMS_SQL.VARCHAR2_TABLE ;
3734: l_dummy NUMBER;
3735: BEGIN
3736: l_dummy := decomposeString(p_string, p_separator, l_table);
3737: return l_table;

Line 3741: l_origin_list DBMS_SQL.NUMBER_TABLE;

3737: return l_table;
3738: END;
3739:
3740: PROCEDURE insert_per(p_periodicity IN NUMBER, p_origin IN VARCHAR2) IS
3741: l_origin_list DBMS_SQL.NUMBER_TABLE;
3742: l_table_name VARCHAR2(100) := bsc_metadata_optimizer_pkg.g_period_circ_check ;
3743: l_stmt VARCHAR2(1000) := 'INSERT INTO '||l_table_name||'(periodicity, source) values (:1, :2)';
3744: l_index NUMBER;
3745: BEGIN

Line 3836: arrPerOri DBMS_SQL.VARCHAR2_TABLE;

3832:
3833: L_Periodicity BSC_METADATA_OPTIMIZER_PKG.clsPeriodicity;
3834: PerOri NUMBER;
3835: Origen VARCHAR2(1000);
3836: arrPerOri DBMS_SQL.VARCHAR2_TABLE;
3837: NumPerOri NUMBER;
3838: i NUMBER;
3839: l_stmt VARCHAR2(1000);
3840:

Line 3842: arrPerSourceCompleted DBMS_SQL.NUMBER_TABLE;

3838: i NUMBER;
3839: l_stmt VARCHAR2(1000);
3840:
3841: allSourcesCompleted Boolean;
3842: arrPerSourceCompleted DBMS_SQL.NUMBER_TABLE;
3843: numPerSourceCompleted NUMBER;
3844: srcPerOri NUMBER;
3845: newPerOri NUMBER;
3846:

Line 3858: l_origin_list DBMS_SQL.VARCHAR2_TABLE;

3854:
3855: cPeriodRow cPeriods%ROWTYPE;
3856:
3857: l_index NUMBER;
3858: l_origin_list DBMS_SQL.VARCHAR2_TABLE;
3859: l_origin_index NUMBER;
3860: l_src_origin_index NUMBER;
3861: l_src_origin_list DBMS_SQL.VARCHAR2_TABLE;
3862: l_error VARCHAR2(400);

Line 3861: l_src_origin_list DBMS_SQL.VARCHAR2_TABLE;

3857: l_index NUMBER;
3858: l_origin_list DBMS_SQL.VARCHAR2_TABLE;
3859: l_origin_index NUMBER;
3860: l_src_origin_index NUMBER;
3861: l_src_origin_list DBMS_SQL.VARCHAR2_TABLE;
3862: l_error VARCHAR2(400);
3863: l_time date := sysdate;
3864: l_seconds number := 0;
3865: l_prev_counter number := 0;

Line 4062: PROCEDURE InsertDirectTables(arrTables in out nocopy dbms_Sql.varchar2_table, p_variable_id IN NUMBER) IS

4058: -- starting from the S table going towards the I table. We need this to figure out which tables
4059: -- can be dropped safely while running the optimizer for a list
4060: -- of selected indicators
4061:
4062: PROCEDURE InsertDirectTables(arrTables in out nocopy dbms_Sql.varchar2_table, p_variable_id IN NUMBER) IS
4063: CURSOR cStoITables IS
4064: select distinct source_table_name from bsc_db_tables_rels
4065: connect by table_name = prior source_table_name
4066: start with table_name in (

Line 4083: FUNCTION GetAffectedProductionTables(inIndicators in VARCHAR2) return dbms_Sql.varchar2_table IS

4079:
4080: -- Given a list of production indicators, get the list of production tables they need
4081: -- Start with BSC_KPI_DATA_TABLES to get the S table name and then move from S to I table
4082:
4083: FUNCTION GetAffectedProductionTables(inIndicators in VARCHAR2) return dbms_Sql.varchar2_table IS
4084:
4085: cv CurTyp;
4086:
4087: l_stmt VARCHAR2(1000):= 'select distinct source_table_name from bsc_db_tables_rels

Line 4091: l_tables dbms_Sql.varchar2_table ;

4087: l_stmt VARCHAR2(1000):= 'select distinct source_table_name from bsc_db_tables_rels
4088: connect by table_name = prior source_table_name
4089: start with table_name in (';
4090:
4091: l_tables dbms_Sql.varchar2_table ;
4092: l_table varchar2(100);
4093: BEGIN
4094: l_stmt := l_stmt ||inIndicators ||' )';
4095: OPEN cv for l_stmt;

Line 4116: arrayDirectTables dbms_sql.varchar2_table;

4112: i number := 0;
4113: lTable varchar2(100);
4114: cv CurTyp;
4115: lError VARCHAR2(400);
4116: arrayDirectTables dbms_sql.varchar2_table;
4117: strWhereInDirectTables varchar2(1000);
4118: strWhereInDirectIndics varchar2(1000);
4119:
4120: arrayAllAffectedTables dbms_sql.varchar2_table;

Line 4120: arrayAllAffectedTables dbms_sql.varchar2_table;

4116: arrayDirectTables dbms_sql.varchar2_table;
4117: strWhereInDirectTables varchar2(1000);
4118: strWhereInDirectIndics varchar2(1000);
4119:
4120: arrayAllAffectedTables dbms_sql.varchar2_table;
4121: l_varchar2_list dbms_sql.varchar2_table;
4122:
4123: l_dontProcessIndics VARCHAR2(1000);
4124: l_dontDropTables VARCHAR2(1000);

Line 4121: l_varchar2_list dbms_sql.varchar2_table;

4117: strWhereInDirectTables varchar2(1000);
4118: strWhereInDirectIndics varchar2(1000);
4119:
4120: arrayAllAffectedTables dbms_sql.varchar2_table;
4121: l_varchar2_list dbms_sql.varchar2_table;
4122:
4123: l_dontProcessIndics VARCHAR2(1000);
4124: l_dontDropTables VARCHAR2(1000);
4125: l_DropTables VARCHAR2(1000);

Line 4126: l_varchar2_table dbms_sql.varchar2_table;

4122:
4123: l_dontProcessIndics VARCHAR2(1000);
4124: l_dontDropTables VARCHAR2(1000);
4125: l_DropTables VARCHAR2(1000);
4126: l_varchar2_table dbms_sql.varchar2_table;
4127: BEGIN
4128: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4129: writeTmp( 'Inside MarkTablesForSelectedKPIs', FND_LOG.LEVEL_PROCEDURE, false);
4130: END IF;

Line 4473: l_aw_kpi_list dbms_sql.varchar2_table;

4469:
4470: l_total_count NUMBER := 0;
4471: l_objectives_count NUMBER:=0;
4472: l_counter number;
4473: l_aw_kpi_list dbms_sql.varchar2_table;
4474:
4475: BEGIN
4476:
4477:

Line 5245: Function searchStringExists(arrStr dbms_sql.varchar2_table, Num number, str varchar2)

5241: -- Num: Size of the array (not used)
5242: -- Cadena: String to look for.
5243: --****************************************************************************
5244:
5245: Function searchStringExists(arrStr dbms_sql.varchar2_table, Num number, str varchar2)
5246: return Boolean IS
5247: l_count number := 0;
5248: l_error Varchar2(1000);
5249:

Line 5282: Function searchNumberExists(arrStr dbms_sql.number_table, Num number, l_findThis NUMBER)

5278: -- arrStr(): array of numbers
5279: -- Num: Size of the array (not used)
5280: -- Cadena: Number to look for.
5281: --****************************************************************************
5282: Function searchNumberExists(arrStr dbms_sql.number_table, Num number, l_findThis NUMBER)
5283: return Boolean IS
5284: l_count number := 0;
5285: l_error Varchar2(1000);
5286:

Line 5321: PROCEDURE InsertChildTables_LAST(arrTables IN dbms_sql.varchar2_table,

5317: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
5318: --****************************************************************************
5319:
5320:
5321: PROCEDURE InsertChildTables_LAST(arrTables IN dbms_sql.varchar2_table,
5322: numTables IN OUT NOCOPY number,
5323: arrChildTables IN OUT NOCOPY dbms_sql.varchar2_table,
5324: numChildTables IN OUT NOCOPY number) IS
5325:

Line 5323: arrChildTables IN OUT NOCOPY dbms_sql.varchar2_table,

5319:
5320:
5321: PROCEDURE InsertChildTables_LAST(arrTables IN dbms_sql.varchar2_table,
5322: numTables IN OUT NOCOPY number,
5323: arrChildTables IN OUT NOCOPY dbms_sql.varchar2_table,
5324: numChildTables IN OUT NOCOPY number) IS
5325:
5326: l_table_name varchar2(300);
5327: l_source_table_name VARCHAR2(300);

Line 5328: arrTablesAux dbms_sql.varchar2_table;

5324: numChildTables IN OUT NOCOPY number) IS
5325:
5326: l_table_name varchar2(300);
5327: l_source_table_name VARCHAR2(300);
5328: arrTablesAux dbms_sql.varchar2_table;
5329: numTablesAux NUMBER;
5330:
5331: --l_stmt VARCHAR2(2000) := 'SELECT TABLE_NAME FROM BSC_DB_TABLES_RELS_LAST WHERE UPPER(SOURCE_TABLE_NAME) = :1' ;
5332: CURSOR cChildTables (pOriginTable IN VARCHAR2) IS

Line 5403: arrChildTables dbms_sql.varchar2_table;

5399: BaseTable VARCHAR2(1000);
5400: BakTable VARCHAR2(100);
5401: l_InputTable varchar2(100);
5402: periodicity pls_Integer;
5403: arrChildTables dbms_sql.varchar2_table;
5404: numChildTables pls_Integer;
5405: arrTables dbms_sql.varchar2_table;
5406: numTables pls_Integer;
5407: strWhereInChildTables varchar2(1000);

Line 5405: arrTables dbms_sql.varchar2_table;

5401: l_InputTable varchar2(100);
5402: periodicity pls_Integer;
5403: arrChildTables dbms_sql.varchar2_table;
5404: numChildTables pls_Integer;
5405: arrTables dbms_sql.varchar2_table;
5406: numTables pls_Integer;
5407: strWhereInChildTables varchar2(1000);
5408:
5409: l_stmt VARCHAR2(1000):='SELECT R.TABLE_NAME, R.SOURCE_TABLE_NAME, BT.PERIODICITY_ID

Line 5439: l_arr_indicators dbms_sql.number_table;

5435: AND owner = pOwner
5436: ORDER BY column_id;
5437:
5438: l_error varchar2(1000);
5439: l_arr_indicators dbms_sql.number_table;
5440: BEGIN
5441:
5442: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
5443: writeTmp( 'Inside InitInfoOldSystem, time is '||get_time); --commit;

Line 5553: l_drop_list_aw DBMS_SQL.VARCHAR2_TABLE;

5549: uv_name varchar2(100);
5550: pt_name varchar2(100);
5551: cv CurTyp;
5552: l_error VARCHAR2(1000);
5553: l_drop_list_aw DBMS_SQL.VARCHAR2_TABLE;
5554: l_drop_list_number DBMS_SQL.NUMBER_TABLE;
5555:
5556: l_child_table VARCHAR2(1000);
5557: BEGIN

Line 5554: l_drop_list_number DBMS_SQL.NUMBER_TABLE;

5550: pt_name varchar2(100);
5551: cv CurTyp;
5552: l_error VARCHAR2(1000);
5553: l_drop_list_aw DBMS_SQL.VARCHAR2_TABLE;
5554: l_drop_list_number DBMS_SQL.NUMBER_TABLE;
5555:
5556: l_child_table VARCHAR2(1000);
5557: BEGIN
5558: writeTmp( 'Inside deletePreviousRunTables, time is '||get_time, FND_LOG.LEVEL_STATEMENT, true);

Line 5999: FUNCTION all_columns_used(p_arr_source_table DBMS_SQL.VARCHAR2_TABLE, p_table_name OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE, p_unused_columns OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE) return boolean IS

5995:
5996: --TerminarPorError (Get_Message('BSC_TABLES_INIT_FAILED'))
5997: End ;
5998:
5999: FUNCTION all_columns_used(p_arr_source_table DBMS_SQL.VARCHAR2_TABLE, p_table_name OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE, p_unused_columns OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE) return boolean IS
6000:
6001: l_stmt VARCHAR2(1000):=
6002: 'SELECT last.table_name, last.column_name
6003: FROM '||bsc_metadata_optimizer_pkg.g_db_tables_cols_last||' last

Line 6059: PROCEDURE drop_unused_columns_for_tables(p_table_name IN DBMS_SQL.VARCHAR2_TABLE) IS

6055: WHERE table_name = p_Table_name
6056: AND column_name = p_column_name;
6057: END;
6058:
6059: PROCEDURE drop_unused_columns_for_tables(p_table_name IN DBMS_SQL.VARCHAR2_TABLE) IS
6060: strWhereInIndics VARCHAR2(1000);
6061: cv CurTyp;
6062: l_stmt VARCHAR2(1000):=
6063: 'select distinct source_table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_db_table_rels_last||'

Line 6066: l_unused_columns DBMS_SQL.VARCHAR2_TABLE;

6062: l_stmt VARCHAR2(1000):=
6063: 'select distinct source_table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_db_table_rels_last||'
6064: connect by table_name = prior source_table_name
6065: start with table_name in (select value_v from bsc_tmp_big_in_cond where variable_id=:1 and session_id=:2)';
6066: l_unused_columns DBMS_SQL.VARCHAR2_TABLE;
6067: l_mvlog_name VARCHAR2(100);
6068: l_source_table_name VARCHAR2(100);
6069: l_i_table DBMS_SQL.VARCHAR2_TABLE;
6070:

Line 6069: l_i_table DBMS_SQL.VARCHAR2_TABLE;

6065: start with table_name in (select value_v from bsc_tmp_big_in_cond where variable_id=:1 and session_id=:2)';
6066: l_unused_columns DBMS_SQL.VARCHAR2_TABLE;
6067: l_mvlog_name VARCHAR2(100);
6068: l_source_table_name VARCHAR2(100);
6069: l_i_table DBMS_SQL.VARCHAR2_TABLE;
6070:
6071: l_arr_source_table dbms_sql.varchar2_table;
6072: l_arr_table_name dbms_sql.varchar2_table;
6073:

Line 6071: l_arr_source_table dbms_sql.varchar2_table;

6067: l_mvlog_name VARCHAR2(100);
6068: l_source_table_name VARCHAR2(100);
6069: l_i_table DBMS_SQL.VARCHAR2_TABLE;
6070:
6071: l_arr_source_table dbms_sql.varchar2_table;
6072: l_arr_table_name dbms_sql.varchar2_table;
6073:
6074: l_dummy varchar2(1000);
6075: BEGIN

Line 6072: l_arr_table_name dbms_sql.varchar2_table;

6068: l_source_table_name VARCHAR2(100);
6069: l_i_table DBMS_SQL.VARCHAR2_TABLE;
6070:
6071: l_arr_source_table dbms_sql.varchar2_table;
6072: l_arr_table_name dbms_sql.varchar2_table;
6073:
6074: l_dummy varchar2(1000);
6075: BEGIN
6076: writeTmp('Drop unused columns for tables');

Line 6115: l_number_list dbms_sql.number_table;

6111: strWhereInIndics VARCHAR2(1000);
6112: l_stmt varchar2(4000);
6113: cv CurTyp;
6114: L_TABLE_NAME VARCHAR2(100);
6115: l_number_list dbms_sql.number_table;
6116: l_arr_table_names dbms_sql.varchar2_table;
6117: BEGIN
6118:
6119: writeTmp('Starting drop_unused_columns');

Line 6116: l_arr_table_names dbms_sql.varchar2_table;

6112: l_stmt varchar2(4000);
6113: cv CurTyp;
6114: L_TABLE_NAME VARCHAR2(100);
6115: l_number_list dbms_sql.number_table;
6116: l_arr_table_names dbms_sql.varchar2_table;
6117: BEGIN
6118:
6119: writeTmp('Starting drop_unused_columns');
6120: strWhereInIndics := Get_New_Big_In_Cond_Number(11, 'INDICATOR');

Line 6192: arrAllTables DBMS_SQL.VARCHAR2_TABLE;

6188: -- This situation should not happen, but due to some unkown issue in the past
6189: -- some tables could be there but no indicator is using it.
6190: --****************************************************************************
6191: PROCEDURE CleanDatabase IS
6192: arrAllTables DBMS_SQL.VARCHAR2_TABLE;
6193: numAllTables NUMBER;
6194:
6195: l_stmt VARCHAR2(1000);
6196: i NUMBER;

Line 6197: arrNotUsedTables DBMS_SQL.VARCHAR2_TABLE;

6193: numAllTables NUMBER;
6194:
6195: l_stmt VARCHAR2(1000);
6196: i NUMBER;
6197: arrNotUsedTables DBMS_SQL.VARCHAR2_TABLE;
6198: numNotUsedTables NUMBER;
6199: strWhereInCondition VARCHAR2(1000);
6200:
6201: mv_name VARCHAR2(100);

Line 6454: l_varchar2_table DBMS_SQL.VARCHAR2_TABLE;

6450:
6451: PROCEDURE dump_stack IS
6452: l_msg VARCHAR2(2000);
6453: l_chunk VARCHAR2(256);
6454: l_varchar2_table DBMS_SQL.VARCHAR2_TABLE;
6455: BEGIN
6456: fnd_file.put_line(FND_FILE.LOG, ' ');
6457: BSC_MO_HELPER_PKG.TerminateWithMsg( 'Dumping stack contents ');
6458: fnd_file.put_line(FND_FILE.LOG, '-------------START OF STACK CONTENTS------------ ');

Line 6508: l_varchar2_table DBMS_SQL.VARCHAR2_TABLE;

6504: l_msg VARCHAR2(32767);
6505: l_chunk VARCHAR2(4000);
6506: l_severity NUMBER;
6507: l_force_logging boolean := false;
6508: l_varchar2_table DBMS_SQL.VARCHAR2_TABLE;
6509: BEGIN
6510: IF (msg is null) THEN
6511: return;
6512: END IF;

Line 6892: FUNCTION getGroupIds (levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) RETURN DBMS_SQL.NUMBER_TABLE IS

6888: raise;
6889: END;
6890: */
6891:
6892: FUNCTION getGroupIds (levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) RETURN DBMS_SQL.NUMBER_TABLE IS
6893: l_groups DBMS_SQL.NUMBER_TABLE;
6894: l_count NUMBER := 0;
6895: i NUMBER;
6896: BEGIN

Line 6893: l_groups DBMS_SQL.NUMBER_TABLE;

6889: END;
6890: */
6891:
6892: FUNCTION getGroupIds (levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) RETURN DBMS_SQL.NUMBER_TABLE IS
6893: l_groups DBMS_SQL.NUMBER_TABLE;
6894: l_count NUMBER := 0;
6895: i NUMBER;
6896: BEGIN
6897: i := levels.first;

Line 6910: FUNCTION getGroupIds (levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations) RETURN DBMS_SQL.NUMBER_TABLE IS

6906: END LOOP;
6907: return l_groups;
6908: END;
6909:
6910: FUNCTION getGroupIds (levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations) RETURN DBMS_SQL.NUMBER_TABLE IS
6911: l_groups DBMS_SQL.NUMBER_TABLE;
6912: l_count NUMBER := 0;
6913: i NUMBER;
6914: BEGIN

Line 6911: l_groups DBMS_SQL.NUMBER_TABLE;

6907: return l_groups;
6908: END;
6909:
6910: FUNCTION getGroupIds (levels IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations) RETURN DBMS_SQL.NUMBER_TABLE IS
6911: l_groups DBMS_SQL.NUMBER_TABLE;
6912: l_count NUMBER := 0;
6913: i NUMBER;
6914: BEGIN
6915: i := levels.first;

Line 7045: FUNCTION consolidateString (pTable IN DBMS_SQL.VARCHAR2_TABLE, pSeparator IN VARCHAR2) RETURN VARCHAR2 IS

7041:
7042: -- Opposite of decompose
7043: -- Given a VARCHAR2_TABLE, this API will return a comma separated string
7044:
7045: FUNCTION consolidateString (pTable IN DBMS_SQL.VARCHAR2_TABLE, pSeparator IN VARCHAR2) RETURN VARCHAR2 IS
7046: l_return VARCHAR2(32000) := null;
7047: l_count NUMBER;
7048: BEGIN
7049:

Line 7293: l_dropped_aw_objectives dbms_sql.varchar2_table;

7289:
7290: l_option_string varchar2(100);
7291: l_error_message varchar2(4000);
7292:
7293: l_dropped_aw_objectives dbms_sql.varchar2_table;
7294: l_rpt_lud date;
7295:
7296: cv CurTyp;
7297:

Line 7341: PROCEDURE implement_aws (p_objectives in dbms_Sql.varchar2_table) IS

7337: END LOOP;
7338: END IF;
7339: END;
7340:
7341: PROCEDURE implement_aws (p_objectives in dbms_Sql.varchar2_table) IS
7342:
7343: BEGIN
7344: bsc_aw_adapter.implement_kpi_aw(
7345: p_objectives,