[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_LEVEL_UPDATE', p_instance_id);
msd_dem_common_utilities.log_debug('Entering msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
EXECUTE IMMEDIATE 'SELECT instance_type FROM msc_apps_instances WHERE instance_id = ' || to_char(p_sr_instance_id)
INTO x_instance_type;
msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
x_sql := 'UPDATE ' || p_dest_table_name || ' t '
|| ' SET t.' || p_dest_column_name || ' = ('
|| ' SELECT substrb(mtp_site.partner_name, 1, 50) '
|| ' || '':'' || mtil.sr_cust_account_number '
|| ' || '':'' || mtps_site.location '
|| ' || '':'' || mtps_site.operating_unit_name '
--|| ' || decode( mtp_site.customer_type, ''I'', decode(mtp_app_org.partner_name, NULL, NULL, '':'' || mtp_app_org.partner_name), NULL) '
|| ' || decode( mtp_site.customer_type, ''I'', msd_dem_common_utilities.get_org_for_internal_site(mtsil.sr_instance_id, mtsil.tp_site_id,mtps_site.partner_id,mtsil.location_id), NULL) '
|| ' FROM msc_tp_site_id_lid mtsil, '
|| ' msc_tp_id_lid mtil, '
|| ' msc_trading_partner_sites mtps_site, '
|| ' msc_trading_partners mtp_site '
--|| ' msc_location_associations mla, ' --bug#13604468
--|| ' msc_trading_partners mtp_app_org ' -- bug#13604468
|| ' WHERE mtsil.sr_instance_id = ' || to_char(p_sr_instance_id)
|| ' AND mtsil.sr_tp_site_id = to_number(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', ''::'') + 2)) '
|| ' AND mtsil.partner_type = 2 '
|| ' AND mtsil.tp_site_id = mtps_site.partner_site_id '
|| ' AND mtsil.sr_instance_id = mtil.sr_instance_id '
|| ' AND mtsil.sr_cust_acct_id = mtil.sr_tp_id '
|| ' AND mtil.partner_type = 2 '
|| ' AND mtps_site.partner_id = mtp_site.partner_id '
|| ' AND mtps_site.partner_type = 2 '
|| ' AND mtp_site.partner_type = 2 )'
/* -- commented bug#13604468 --12.3--Bug 14696269--12.2.1--kkhatri
|| ' AND mla.sr_instance_id(+) = mtsil.sr_instance_id '
|| ' AND mla.partner_site_id(+) = mtsil.tp_site_id '
|| ' AND mtp_app_org.sr_tp_id(+) = mla.organization_id '
|| ' AND mtp_app_org.sr_instance_id(+) = mla.sr_instance_id '
|| ' AND mtp_app_org.partner_type(+) = 3 )'
*/
|| ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
|| ' AND t.' || p_dest_column_name || ' LIKE ''' || to_char(p_sr_instance_id) || '::%''';
msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
x_sql := 'UPDATE ' || p_dest_table_name || ' t '
|| ' SET t.' || p_dest_column_name || ' = nvl(('
|| ' SELECT ''' || to_char(p_sr_instance_id) || ''' || ''::'' || mtsil.sr_tp_site_id '
|| ' FROM msc_trading_partners mtp, '
|| ' msc_tp_id_lid mtil, '
|| ' msc_trading_partner_sites mtps, '
|| ' msc_tp_site_id_lid mtsil '
|| ' WHERE mtp.partner_type = 2 '
|| ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
|| ' AND mtil.partner_type = mtp.partner_type '
|| ' AND mtil.sr_instance_id = ' || to_char(p_sr_instance_id)
|| ' AND mtil.tp_id = mtp.partner_id '
|| ' AND nvl(mtil.sr_cust_account_number, ''###'') = nvl(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'') +1, instr(t.'
|| p_dest_column_name || ', '':'', 1, 2) - instr(t.' || p_dest_column_name || ', '':'', 1, 1) -1), ''###'') '
|| ' AND mtps.partner_type = mtp.partner_type '
|| ' AND mtps.partner_id = mtp.partner_id '
|| ' AND replace(mtps.location, '''''''', '''') = substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'', 1, 2) +1, instr(t.'
|| p_dest_column_name || ', '':'', 1, 3) - instr(t.' || p_dest_column_name || ', '':'', 1, 2) -1) ';
msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
x_sql := 'UPDATE ' || p_dest_table_name || ' t '
|| ' SET t.' || p_dest_column_name || ' = nvl(('
|| ' SELECT to_char(mtps.partner_site_id) '
|| ' FROM msc_trading_partners mtp, '
|| ' msc_trading_partner_sites mtps '
|| ' WHERE mtp.partner_type = 1 '
|| ' AND replace(mtp.partner_name, '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
|| ' AND mtps.partner_type = 1 '
|| ' AND mtps.partner_id = mtp.partner_id '
|| ' AND replace(mtps.tp_site_code, '''''''', '''') = substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'') + 1, '
|| 'length(t.' || p_dest_column_name || ') - instr(t.' || p_dest_column_name || ', '':'')) '
|| ' AND rownum < 2), t.' || p_dest_column_name || ' ) '
|| ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
|| ' AND t.' || p_dest_column_name || ' LIKE ''%:%''';
msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
x_sql := 'UPDATE ' || p_dest_table_name || ' t '
|| ' SET t.' || p_dest_column_name || ' = nvl(('
|| ' SELECT to_char(mtp.partner_id) || ''::'' || replace(mtil.sr_cust_account_number, '''''''', '''') '
|| ' FROM msc_trading_partners mtp, '
|| ' msc_tp_id_lid mtil '
|| ' WHERE mtp.partner_type = 2 '
|| ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
|| ' AND mtil.sr_instance_id = ' || to_char(p_sr_instance_id)
|| ' AND mtil.tp_id = mtp.partner_id '
|| ' AND replace(nvl(mtil.sr_cust_account_number, ''###''), '''''''', '''') = nvl(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'') + 1, '
|| 'length(t.' || p_dest_column_name || ') - instr(t.' || p_dest_column_name || ', '':'')), ''###'') '
|| ' AND rownum < 2), t.' || p_dest_column_name || ' ) '
|| ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
|| ' AND t.' || p_dest_column_name || ' LIKE ''%:%''';
msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
x_sql := 'UPDATE ' || p_dest_table_name || ' t '
|| ' SET t.' || p_dest_column_name || ' = nvl(('
|| ' SELECT to_char(mtp.partner_id) '
|| ' FROM msc_trading_partners mtp '
|| ' WHERE mtp.partner_type = 2 '
|| ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = t.' || p_dest_column_name || ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
|| ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || '''';
msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
x_sql := 'UPDATE ' || p_dest_table_name || ' t '
|| ' SET t.' || p_dest_column_name || ' = nvl(('
|| ' SELECT to_char(mtp.partner_id) '
|| ' FROM msc_trading_partners mtp '
|| ' WHERE mtp.partner_type = 1 '
|| ' AND replace(mtp.partner_name, '''''''', '''') = t.' || p_dest_column_name || ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
|| ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || '''';
msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
x_sql := 'UPDATE ' || p_dest_table_name || ' t '
|| ' SET t.' || p_dest_column_name || ' = nvl(('
|| ' SELECT to_char(mtp.partner_id) || replace(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':''), '
|| ' length(t.' || p_dest_column_name || ') - instr(t.' || p_dest_column_name || ', '':'') + 1 ), '':'', ''::'') '
|| ' FROM msc_trading_partners mtp '
|| ' WHERE mtp.partner_type = 2 '
|| ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
|| ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
|| ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
|| ' AND t.' || p_dest_column_name || ' LIKE ''%:%:%''';
msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
msd_dem_common_utilities.log_debug('Exiting msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception(1): msd_dem_update_level_codes.convert_site_code - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
* This procedure updates the level codes from descriptive to id format. The levels are -
* SITE, ACCOUNT, CUSTOMER, SUPPLIER, TRADING PARTNER ZONE
*
* This is an upgrade procedure hence proper backup of the Demantra Schema must be taken
* before running this procedure.
*
* This procedure must be run once for each instance for which data is available inside
* Demantra.
*
* This procedure creates a backup copy of the tables before updating them.
*
* The Demantra Application Server should be down when the procedure is run.
*
* Once the procedure has finished, bring up the Demantra Application Server and verify data.
*
* Run Data Load and verify data.
*
* This procedure should be run with MSD_DEM: Debug Mode set to Yes.
*
*/
PROCEDURE UPGRADE_GEO_LEVEL_CODES (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_sr_instance_id IN NUMBER)
IS
x_sql VARCHAR2(4000) := NULL;
msd_dem_common_utilities.log_debug('Exiting msd_dem_update_level_codes.upgrade_geo_level_codes' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Warning(1): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
EXECUTE IMMEDIATE ' SELECT count(1) FROM dba_objects WHERE owner = ''' || x_dem_schema || ''' and object_name IN (''T_EP_SITE'', ''T_EP_EBS_ACCOUNT'', ''T_EP_EBS_CUSTOMER'', ''T_EP_EBS_TP_ZONE'', ''T_EP_EBS_SUPPLIER'') '
|| ' AND object_type = ''TABLE'''
INTO x_is_present;
msd_dem_common_utilities.log_message ('Error(1): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Error(2): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
|| ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_SITE';
|| ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_ACCOUNT';
|| ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_CUSTOMER';
|| ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_TP_ZONE';
|| ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_SUPPLIER';
/*** Update Level codes - START ***/
/* SITE */
convert_site_code (
x_errbuf,
x_retcode,
p_sr_instance_id,
'SITE',
x_dem_schema || '.T_EP_SITE',
'SITE',
2);
msd_dem_common_utilities.log_message ('Error(3): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Error(3): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Error(3): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Error(3): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Error(3): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
/*** Update Level codes - END ***/
/* Set Profile MSD_DEM_SITE_CODE_FORMAT to Yes */
x_ret_val := fnd_profile.save('MSD_DEM_SITE_CODE_FORMAT', 1, 'SITE');
msd_dem_common_utilities.log_debug('Exiting msd_dem_update_level_codes.upgrade_geo_level_codes' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception(1): msd_dem_update_level_codes.upgrade_geo_level_codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
END MSD_DEM_UPDATE_LEVEL_CODES;