[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
EXECUTE IMMEDIATE 'SELECT SYS_CONTEXT (''USERENV'', ''SESSION_USER'') FROM DUAL ' INTO x_session_user;
EXECUTE IMMEDIATE 'SELECT count(1) FROM ALL_OBJECTS WHERE OWNER = :1 AND OBJECT_TYPE = ''TABLE'' AND OBJECT_NAME = ''MDP_MATRIX'' '
INTO x_count
USING x_session_user;
EXECUTE IMMEDIATE 'SELECT FND_PROFILE.VALUE (''MSD_DEM_SCHEMA'') FROM DUAL ' INTO g_demantra_schema_name;
EXECUTE IMMEDIATE 'SELECT count(1) FROM ' || g_demantra_schema_name || '.MDP_MATRIX WHERE rownum <2'
INTO x_count;
* DELETE_SERIES
* ADD_SERIES_TO_COMPONENT
* CREATE_INTEGRATION_INTERFACE
* DELETE_INTEGRATION_INTERFACE
* CREATE_DATA_PROFILE
* ADD_SERIES_TO_PROFILE
* ADD_LEVEL_TO_PROFILE
* CREATE_WORKFLOW_SCHEMA
* DELETE_WORKFLOW_SCHEMA
* GET_DEMANTRA_SCHEMA
* CREATE_DEMANTRA_DB_OBJECT
* DROP_DEMANTRA_DB_OBJECT
* CREATE_SYNONYM_IN_EBS
*/
/*
* This function calls the GET_SEQ_NEXTVAL procedure in the Demantra schema.
* The function returns -
* n : next value for the given sequence
* -1 : If table is not present
* -2 : If column is not present
* -3 : Unable to set demantra schema name
* -4 : Any other error
*/
FUNCTION GET_SEQUENCE_NEXTVAL (
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_seq_name IN VARCHAR2)
RETURN NUMBER
IS
x_sequence_nextval NUMBER := NULL;
x_sql := 'SELECT max(disp_order) + 1 FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS ';
x_sql := 'SELECT count(1) FROM ALL_TAB_COLUMNS WHERE '
|| ' owner = upper( :1 ) '
|| ' AND table_name = ''SALES_DATA'' '
|| ' AND column_name = upper (:2 ) ';
log_debug ('Build insert statement for creating record for the new series in the computed_fields table');
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.COMPUTED_FIELDS ( '
|| 'FORECAST_TYPE_ID, COMPUTED_NAME, EXP_TEMPLATE, DISP_COLOR, DISP_LSTYLE, '
|| 'DISP_LSYMBOL, PRINT_COLOR, PRINT_LSTYLE, PRINT_LSYMBOL, DISP_ORDER, '
|| 'INFO_TYPE, TABLE_FORMAT, DO_HAVING, COMPUTED_TITLE, FIELD_TYPE, '
|| 'SUM_FUNC, SCALEBLE, MODULE_TYPE, EDITABLE, IS_PROPORTION, '
|| 'NULL_AS_ZERO, DBNAME, IS_DDLB, IS_CHECK, SERIES_WIDTH, '
|| 'IS_DEFAULT, HINT_MESSAGE, HIST_PRED_TYPE, DATA_TABLE_NAME, LOOKUP_TYPE, '
|| 'COL_SERIES_WIDTH, PROP_CALC_SERIES, BASE_LEVEL, EXPRESSION_TYPE, INT_AGGR_FUNC, '
|| 'AGGR_BY, PRESERVATION_TYPE, IS_EDITABLE_SUMMARY, MOVE_PRESERVATION_TYPE, DATA_TYPE, '
|| 'SAME_VAL_UPDATE )'
|| ' VALUES ( '
|| ' :1, :2, :3, 255, 1, '
|| ' 1, 255, 1, 1, :4, '
|| ' 1, ''###,###'', 0, :5, 1, '
|| ' :6, :7, 0, :8, :9, '
|| ' 0, :10, 0, 0, 250, '
|| ' 0, :11, :12, :13, 0, '
|| ' 10, :14, :15, :16, :17, '
|| ' :18, :19, 0, :20, :21, '
|| ' 0 )';
log_debug ('Insert statement executed successfully');
* This function deletes the series given in Demantra Schema.
* The function returns -
* n : The series id in case of success
* -1 : in case of error
* -2 : if series is not present
* -3 : Unable to set demantra schema name
*/
FUNCTION DELETE_SERIES ( p_computed_name IN VARCHAR2 )
RETURN NUMBER
IS
x_series_id NUMBER := NULL;
log_debug ('Entering msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
x_sql := 'SELECT forecast_type_id, dbname '
|| ' FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS '
|| ' WHERE computed_name = :1 ';
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.SERIES_GROUPS_M WHERE series_id = :1 ';
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.USER_SECURITY_SERIES WHERE series_id = :1 ';
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES WHERE series_id = :1 ';
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.TRANSFER_QUERY_SERIES WHERE series_id = :1 ';
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS WHERE forecast_type_id = :1 ';
log_debug ('Series deleted successfully');
x_sql := 'SELECT count(1) FROM ALL_TAB_COLUMNS WHERE '
|| ' owner = upper( :1 ) '
|| ' AND table_name = ''SALES_DATA'' '
|| ' AND column_name = upper (:2 ) ';
log_debug ('Exiting msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
log_message ('Exception(1) in function msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
log_message ('Exception(2) in function msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
END DELETE_SERIES;
x_sql := ' SELECT computed_title FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS WHERE forecast_type_id = :1 ';
x_sql := 'SELECT product_name, user_id FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS WHERE dcm_product_id = :1 ';
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES WHERE DCM_PRODUCT_ID = :1 AND SERIES_ID = :2 ';
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES ( DCM_PRODUCT_ID, SERIES_ID ) '
|| ' VALUES ( :1, :2 ) ';
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.USER_SECURITY_SERIES WHERE USER_ID = :1 AND SERIES_ID= :2 ';
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.USER_SECURITY_SERIES ( USER_ID, SERIES_ID ) '
|| ' VALUES ( :1, :2 ) ';
x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.TRANSFER_LIST WHERE name = :1 ';
log_debug ('Insert a row into TRANSFER_LIST for the integration interface');
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_LIST '
|| ' ( ID, NAME, DESCRIPTION, USER_ID, USE_EXTERNAL_SCHEMA ) '
|| ' VALUES ( '
|| ' :1, :2, :3, :4, 0 ) ';
FUNCTION DELETE_INTEGRATION_INTERFACE (p_name IN VARCHAR2)
RETURN NUMBER
IS
x_sql VARCHAR2(4000) := NULL;
log_debug ('Entering msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.TRANSFER_LIST WHERE name = :1 ';
log_debug ('Integration Interface deleted successfully');
log_debug ('Exiting msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
log_message ('Exception in function msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
END DELETE_INTEGRATION_INTERFACE;
p_insertnewcombinations IN NUMBER,
p_insertforecasthorizon IN NUMBER,
p_query_name IN VARCHAR2,
p_description IN VARCHAR2,
p_time_res_id IN NUMBER,
p_from_date IN DATE,
p_until_date IN DATE,
p_relative_date IN NUMBER,
p_relative_from_date IN NUMBER,
p_relative_until_date IN NUMBER,
p_integration_type IN NUMBER,
p_export_type IN NUMBER,
p_base_level_id IN NUMBER)
RETURN NUMBER
IS
x_return_value NUMBER := NULL;
x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.TRANSFER_QUERY WHERE query_name = :1 ';
log_debug ('Insert a row for the data profile into the table TRANSFER_QUERY');
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY ( '
|| ' ID, TRANSFER_ID, PRESENTATION_TYPE, VIEW_NAME, TABLE_NAME, '
|| ' FILE_NAME, DELIMITER, IS_FIXED_WIDTH, VIEW_TYPE, USE_REAL_PROPORTION, '
|| ' INSERTNEWCOMBINATIONS, INSERTFORECASTHORIZON, QUERY_NAME, DESCRIPTION, TIME_RES_ID, '
|| ' FROM_DATE, UNTIL_DATE, RELATIVE_DATE, RELATIVE_FROM_DATE, RELATIVE_UNTIL_DATE, '
|| ' UNIT_ID, INDEX_ID, DATA_SCALE, DM_ID, SCHEMA_ID, '
|| ' QUERY_ID, INTEGRATION_TYPE, EXPORT_TYPE, BATCH_FILE, IMPORT_FROM_FILE, '
|| ' LAST_EXPORT_DATE, FILTER_SD_BY_GL ) '
|| ' VALUES ( '
|| ' :1, :2, 1, :3, :4, '
|| ' null, null, null, :5, :6, '
|| ' :7, :8, :9, :10, :11, '
|| ' :12, :13, :14, :15, :16, '
|| ' 1, null, 1, null, null, '
|| ' null, :17, :18, null, 0, '
|| ' null, 0 ) ';
p_insertnewcombinations, p_insertforecasthorizon, p_query_name, p_description, p_time_res_id,
p_from_date, p_until_date, p_relative_date, p_relative_from_date, p_relative_until_date,
p_integration_type, p_export_type;
log_debug ('Insert a row for the data profile into the table TRANSFER_QUERY_INTERSECTIONS');
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_INTERSECTIONS ( '
|| ' ID, BASE_LEVEL_ID, TYPE ) '
|| ' VALUES ( '
|| ' :1, :2, 1 ) ';
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_SERIES ( ID, SERIES_ID, LOAD_OPTION, PURGE_OPTION ) '
|| ' VALUES ( :1, :2, 0, 0 ) ';
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_LEVELS ( ID, LEVEL_ID, LORDER ) '
|| ' VALUES ( :1, :2, :3 ) ';
x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.WF_SCHEMAS WHERE schema_name = :1 ';
log_debug ('Insert a row for the workflow schema into the table WF_SCHEMAS');
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.WF_SCHEMAS ( '
|| ' SCHEMA_ID, SCHEMA_NAME, SCHEMA_DATA, STATUS, OWNER_ID, '
|| ' CREATION_DATE, MODIFIED_DATE, TEMPORARY, SCHEMA_TYPE ) '
|| ' VALUES ( '
|| ' :1, :2, :3, 1, :4, '
|| ' :5, :6, 0, :7 ) ';
* This function deletes the workflow schema given.
* The function returns -
* 0 : in case of success
* -1 : in case of error
* -3 : Unable to set demantra schema name
*/
FUNCTION DELETE_WORKFLOW_SCHEMA ( p_schema_name IN VARCHAR2 )
RETURN NUMBER
IS
x_sql VARCHAR2(4000) := NULL;
log_debug ('Entering msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.WF_SCHEMAS WHERE schema_name = :1 ';
log_debug ('Workflow Schema deleted successfully.');
log_debug ('Exiting msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
log_message ('Exception in function msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
END DELETE_WORKFLOW_SCHEMA;
x_sql := 'SELECT 1 FROM all_objects WHERE owner = :1 AND object_type = :2 AND object_name = :3';