[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_stmt := 'select max(datet), min(datet) from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'INPUTS');
SELECT 1
FROM msd_dem_entities_inuse
WHERE internal_name = p_currency_code
and ebs_entity = 'Currency';
l_stmt_get_seeded_unit := 'select real_value from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') ||
' where real_value = ''' || p_currency_code || '''';
l_stmt_old_currency_real_value := 'select real_value_id, real_table from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || ' where real_value = ''' || p_currency_code || '''';
l_stmt_new_currency_real_value := 'select real_value_id, real_table from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') ||
' where real_value_id in ' ||
' (select distinct real_value_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') ||
' minus ' ||
' select distinct real_value_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || ') ' ||
' and real_table in ' ||
' (select real_table from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || ' where real_table like ''EBSCURRENCY%''' ||
' minus ' ||
' select table_name from msd_dem_entities_inuse where ebs_entity = ''Currency'')' ||
' and rownum < 2';
l_stmt := 'insert into msd_dem_entities_inuse(
ebs_entity
,demantra_entity
,internal_name
,table_name
,column_name
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
) values
(
''Currency''
,''INDEX''
,:1
,:2
,NULL
,:4
,:5
,:6
,:7
,:8
)';
l_stmt := 'update ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || '
set real_value = :1
where real_value_id = :2';
/*l_stmt_get_component := 'select dcm_product_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = '''
|| msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';
l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || '
(dcm_product_id
,real_value_id
)
(
select :1, :2 from dual
)';
/*l_stmt_get_component_sop := 'select dcm_product_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = '''
|| msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';
l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || '
(dcm_product_id
,real_value_id
)
(
select :1, :2 from dual
)';
l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
' (display_units_id, real_value_id) ' ||
' (select display_units_id, :1 from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' du, msd_dem_entities_inuse mdei ' ||
' where mdei.ebs_entity = ''PRL'' and mdei.internal_name = du.display_units)';
l_stmt := 'delete from ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' where index_date between :1 and :2 ';
msd_dem_common_utilities.log_debug('Delete statement for currency: ' || l_stmt);
l_stmt := 'insert into ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' (index_date, index_value) ' || '(select conversion_date, round(conversion_rate, 5) from
gl_daily_ratesDBLINK
where from_currency = ' || '''' || p_base_currency_code || '''' || ' and to_currency = ' || '''' || p_currency_code || '''' || ' and conversion_date between :1 and :2 ' ||
' and conversion_type = fnd_profile.valueDBLINK(''MSD_DEM_CONVERSION_TYPE'') )';
l_stmt := 'insert into ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' (index_date, index_value) ' || '(select conversion_date, round((1/conversion_rate), 5) from
gl_daily_ratesDBLINK
where from_currency = ' || '''' || p_currency_code || '''' || ' and to_currency = ' || '''' || p_base_currency_code || '''' || ' and conversion_date between :1 and :2 ' ||
' and conversion_type = fnd_profile.valueDBLINK(''MSD_DEM_CONVERSION_TYPE'') )';
l_stmt := 'select count(*) from ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' where rownum < 5';
l_stmt := 'select distinct from_currency from_currency ' || ' from gl_daily_rates' || g_dblink || ' where to_currency = ''' || l_base_curr || '''' ||
' and conversion_type = fnd_profile.value' || g_dblink || '(''MSD_DEM_CONVERSION_TYPE'') ';
l_stmt := 'select from_currency from (' || l_stmt;
msd_dem_common_utilities.log_message('Warning: Base Currency is selected for collection');
msd_dem_common_utilities.log_message('Warning: Base Currency ' || l_base_curr || ' is selected. Base Currency cannot be collected.');