[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 )
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';
* This function will insert base_level_id values
* into the TRANSFER_QUERY_INTERSECTIONS table against the given data profile.
* The TYPE column is always defaulted to value "1".
* The function returns -
* 0 : in case of success
* -1 : in case of error
* -3 : Unable to set demantra schema name
*/
FUNCTION ADD_INTERSECT_TO_PROFILE (
p_data_profile_id IN NUMBER,
p_base_level_id IN VARCHAR2)
RETURN NUMBER
IS
x_sql VARCHAR2(4000) := NULL;
x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_INTERSECTIONS ( ID, BASE_LEVEL_ID, TYPE ) '
|| ' VALUES ( :1, :2, 1 ) ';
* Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
* to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
* if Demantra is installed.
* Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
* The checks if the table MDP_MATRIX exists in the Demantra Schema
*/
PROCEDURE UPDATE_SYNONYMS (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_demantra_schema IN VARCHAR2 DEFAULT NULL)
IS
CURSOR c_get_dm_schema
IS
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created desc;
SELECT count(1)
FROM dba_tab_columns
WHERE owner = p_owner
AND table_name = p_table_name
AND column_name = p_column_name
AND data_type = p_data_type;
SELECT count(1)
FROM dba_tables
WHERE owner = p_owner
AND table_name = p_table_name;
/* Update synonym MSD_DEM_TRANSFER_LIST to point to Demantra table TRANSFER_LIST */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_LIST FOR ' ||
x_dem_schema || '.TRANSFER_LIST';
log_message ('Updated synonym MSD_DEM_TRANSFER_LIST');
/* Update synonym MSD_DEM_TRANSFER_QUERY to point to Demantra table TRANSFER_QUERY */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY FOR ' ||
x_dem_schema || '.TRANSFER_QUERY';
log_message ('Updated synonym MSD_DEM_TRANSFER_QUERY');
/* Update synonym MSD_DEM_TRANSFER_QUERY_LEVELS to point to Demantra table TRANSFER_QUERY_LEVELS */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY_LEVELS FOR ' ||
x_dem_schema || '.TRANSFER_QUERY_LEVELS';
log_message ('Updated synonym MSD_DEM_TRANSFER_QUERY_LEVELS');
/* Update synonym MSD_DEM_GROUP_TABLES to point to Demantra table GROUP_TABLES */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_GROUP_TABLES FOR ' ||
x_dem_schema || '.GROUP_TABLES';
log_message ('Updated synonym MSD_DEM_GROUP_TABLES');
/* Update synonym T_SRC_SALES_TMPL to point to Demantra table T_SRC_SALES_TMPL */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM T_SRC_SALES_TMPL FOR '||
x_dem_schema || '.T_SRC_SALES_TMPL';
log_message ('Updated synonym T_SRC_SALES_TMPL');
/* Update synonym MSD_DEM_RETURN_HISTORY to point to Demantra table MSD_DEM_RETURN_HISTORY */
x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_RETURN_HISTORY FOR ' ||
x_dem_schema || '.MSD_DEM_RETURN_HISTORY';
log_message ('Updated synonym MSD_DEM_RETURN_HISTORY');
/* grant select on required tables */
log_message ('Granting SELECT privilege on following tables to ' || x_dem_schema || ' schema.');
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLANS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_PUBLISH_DATA TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_SUPPORTED_API TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_BIS_INV_DETAIL TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_SYSTEM_ITEMS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLAN_ORGANIZATIONS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNERS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_ID_LID TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNER_SITES TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_SITE_ID_LID TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_REGIONS TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_TIME TO ' || x_dem_schema;
x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_CTO_BOM TO ' || x_dem_schema;
x_get_dem_ver_sql := 'select version from ' || x_dem_schema || '.version_details' ;
x_ext_logout_url_sql := ' Update '|| x_dem_schema || '.sys_params' ||
' Set pval = ''' || x_appl_home_page_url ||
''' Where pname like ''ExternalLogoutUrl'' ' ;
log_message ('Updated ExternalLogoutUrl parameter in sys_params table to :- ' ||x_appl_home_page_url);
x_sql := 'select count(1) from ' || x_dem_schema || '.plan_type_lookup where type_id = 2';
x_sql := 'INSERT INTO ' || x_dem_schema || '.plan_type_lookup (type_id, type_desc) values (2, ''Rapid Plan'')';
EXECUTE IMMEDIATE ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || x_msd_schema || '.MSD_DEM_TIME'' ' ||
' where pname = ''Integration1CalendarLoad''';
log_message ('Updated Integration1CalendarLoad parameter in sys_params table to :- ' || x_msd_schema || '.MSD_DEM_TIME');
SELECT count(1)
INTO x_count3
FROM dba_tables
WHERE owner = x_dem_schema
AND table_name = 'EP_T_SRC_SALES_TMPL_LD';
x_sql := 'SELECT COUNT(1) FROM ( SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''T_SRC_SALES_TMPL'' '
|| ' MINUS '
|| ' SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''EP_T_SRC_SALES_TMPL_LD'' ) ';
|| ' SELECT tsst.*, TRUNC(tsst.sales_date) AGGRE_SD '
|| ' FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
|| ' WHERE 1 = 2 ';
SELECT count(1)
INTO x_count4
FROM dba_tables
WHERE owner = x_dem_schema
AND table_name = 'T_SRC_SALES_TMPL_TMP';
msd_dem_common_utilities.update_dem_apcc_synonym(errbuf,retcode);
END UPDATE_SYNONYMS;