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.MSD_DEM_UPDATE_LEVEL_CODES dependencies on MSD_DEM_COMMON_UTILITIES

Line 19: msd_dem_common_utilities.log_message('Updating Memeber Codes in Sales Data for ' || p_level );

15: l_stmt varchar2(4000);
16:
17: begin
18:
19: msd_dem_common_utilities.log_message('Updating Memeber Codes in Sales Data for ' || p_level );
20: msd_dem_common_utilities.log_debug('Updating Memeber Codes in Sales Data' || p_level );
21:
22: l_stmt := null;
23:

Line 20: msd_dem_common_utilities.log_debug('Updating Memeber Codes in Sales Data' || p_level );

16:
17: begin
18:
19: msd_dem_common_utilities.log_message('Updating Memeber Codes in Sales Data for ' || p_level );
20: msd_dem_common_utilities.log_debug('Updating Memeber Codes in Sales Data' || p_level );
21:
22: l_stmt := null;
23:
24: dbms_output.put_line(p_dest_table_name || p_dest_column_name || p_src_coulmn_name || p_instance_id);

Line 36: msd_dem_common_utilities.log_debug('Executed Statement: ');

32: l_stmt := replace(l_stmt, 'SRC_COLUMN', p_src_coulmn_name);
33:
34: end if;
35:
36: msd_dem_common_utilities.log_debug('Executed Statement: ');
37: msd_dem_common_utilities.log_debug(l_stmt);
38:
39: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
40: execute immediate l_stmt using p_instance_id;

Line 37: msd_dem_common_utilities.log_debug(l_stmt);

33:
34: end if;
35:
36: msd_dem_common_utilities.log_debug('Executed Statement: ');
37: msd_dem_common_utilities.log_debug(l_stmt);
38:
39: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
40: execute immediate l_stmt using p_instance_id;
41: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

Line 39: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

35:
36: msd_dem_common_utilities.log_debug('Executed Statement: ');
37: msd_dem_common_utilities.log_debug(l_stmt);
38:
39: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
40: execute immediate l_stmt using p_instance_id;
41: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
42:
43: commit;

Line 41: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

37: msd_dem_common_utilities.log_debug(l_stmt);
38:
39: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
40: execute immediate l_stmt using p_instance_id;
41: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
42:
43: commit;
44:
45: retcode := 0;

Line 50: msd_dem_common_utilities.log_message(errbuf);

46:
47: exception
48: when others then
49: errbuf := substr(SQLERRM,1,150);
50: msd_dem_common_utilities.log_message(errbuf);
51: msd_dem_common_utilities.log_debug(errbuf);
52: retcode := -1;
53:
54: end;

Line 51: msd_dem_common_utilities.log_debug(errbuf);

47: exception
48: when others then
49: errbuf := substr(SQLERRM,1,150);
50: msd_dem_common_utilities.log_message(errbuf);
51: msd_dem_common_utilities.log_debug(errbuf);
52: retcode := -1;
53:
54: end;
55:

Line 79: msd_dem_common_utilities.log_debug('Entering msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));

75: x_instance_type NUMBER := NULL;
76:
77: BEGIN
78:
79: msd_dem_common_utilities.log_debug('Entering msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
80:
81: msd_dem_common_utilities.log_message ('Logging Parameters received - ');
82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));
83: msd_dem_common_utilities.log_message ('Level - ' || p_level);

Line 81: msd_dem_common_utilities.log_message ('Logging Parameters received - ');

77: BEGIN
78:
79: msd_dem_common_utilities.log_debug('Entering msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
80:
81: msd_dem_common_utilities.log_message ('Logging Parameters received - ');
82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));
83: msd_dem_common_utilities.log_message ('Level - ' || p_level);
84: msd_dem_common_utilities.log_message ('Table Name - ' || p_dest_table_name);
85: msd_dem_common_utilities.log_message ('Column Name - ' || p_dest_column_name);

Line 82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));

78:
79: msd_dem_common_utilities.log_debug('Entering msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
80:
81: msd_dem_common_utilities.log_message ('Logging Parameters received - ');
82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));
83: msd_dem_common_utilities.log_message ('Level - ' || p_level);
84: msd_dem_common_utilities.log_message ('Table Name - ' || p_dest_table_name);
85: msd_dem_common_utilities.log_message ('Column Name - ' || p_dest_column_name);
86: msd_dem_common_utilities.log_message ('p_convert_type (1=new to old, 2 - old to new) - ' || to_char(p_convert_type));

Line 83: msd_dem_common_utilities.log_message ('Level - ' || p_level);

79: msd_dem_common_utilities.log_debug('Entering msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
80:
81: msd_dem_common_utilities.log_message ('Logging Parameters received - ');
82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));
83: msd_dem_common_utilities.log_message ('Level - ' || p_level);
84: msd_dem_common_utilities.log_message ('Table Name - ' || p_dest_table_name);
85: msd_dem_common_utilities.log_message ('Column Name - ' || p_dest_column_name);
86: msd_dem_common_utilities.log_message ('p_convert_type (1=new to old, 2 - old to new) - ' || to_char(p_convert_type));
87:

Line 84: msd_dem_common_utilities.log_message ('Table Name - ' || p_dest_table_name);

80:
81: msd_dem_common_utilities.log_message ('Logging Parameters received - ');
82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));
83: msd_dem_common_utilities.log_message ('Level - ' || p_level);
84: msd_dem_common_utilities.log_message ('Table Name - ' || p_dest_table_name);
85: msd_dem_common_utilities.log_message ('Column Name - ' || p_dest_column_name);
86: msd_dem_common_utilities.log_message ('p_convert_type (1=new to old, 2 - old to new) - ' || to_char(p_convert_type));
87:
88: EXECUTE IMMEDIATE 'SELECT instance_type FROM msc_apps_instances WHERE instance_id = ' || to_char(p_sr_instance_id)

Line 85: msd_dem_common_utilities.log_message ('Column Name - ' || p_dest_column_name);

81: msd_dem_common_utilities.log_message ('Logging Parameters received - ');
82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));
83: msd_dem_common_utilities.log_message ('Level - ' || p_level);
84: msd_dem_common_utilities.log_message ('Table Name - ' || p_dest_table_name);
85: msd_dem_common_utilities.log_message ('Column Name - ' || p_dest_column_name);
86: msd_dem_common_utilities.log_message ('p_convert_type (1=new to old, 2 - old to new) - ' || to_char(p_convert_type));
87:
88: EXECUTE IMMEDIATE 'SELECT instance_type FROM msc_apps_instances WHERE instance_id = ' || to_char(p_sr_instance_id)
89: INTO x_instance_type;

Line 86: msd_dem_common_utilities.log_message ('p_convert_type (1=new to old, 2 - old to new) - ' || to_char(p_convert_type));

82: msd_dem_common_utilities.log_message ('Instance - ' || to_char(p_sr_instance_id));
83: msd_dem_common_utilities.log_message ('Level - ' || p_level);
84: msd_dem_common_utilities.log_message ('Table Name - ' || p_dest_table_name);
85: msd_dem_common_utilities.log_message ('Column Name - ' || p_dest_column_name);
86: msd_dem_common_utilities.log_message ('p_convert_type (1=new to old, 2 - old to new) - ' || to_char(p_convert_type));
87:
88: EXECUTE IMMEDIATE 'SELECT instance_type FROM msc_apps_instances WHERE instance_id = ' || to_char(p_sr_instance_id)
89: INTO x_instance_type;
90:

Line 94: msd_dem_common_utilities.log_debug ('Entering SITE level');

90:
91: IF (p_level = 'SITE')
92: THEN
93:
94: msd_dem_common_utilities.log_debug ('Entering SITE level');
95:
96: IF (p_convert_type = 1)
97: THEN
98:

Line 99: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');

95:
96: IF (p_convert_type = 1)
97: THEN
98:
99: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
100:
101: x_sql := 'UPDATE ' || p_dest_table_name || ' t '
102: || ' SET t.' || p_dest_column_name || ' = ('
103: || ' SELECT substrb(mtp_site.partner_name, 1, 50) '

Line 108: || ' || 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) '

104: || ' || '':'' || mtil.sr_cust_account_number '
105: || ' || '':'' || mtps_site.location '
106: || ' || '':'' || mtps_site.operating_unit_name '
107: --|| ' || decode( mtp_site.customer_type, ''I'', decode(mtp_app_org.partner_name, NULL, NULL, '':'' || mtp_app_org.partner_name), NULL) '
108: || ' || 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) '
109: || ' FROM msc_tp_site_id_lid mtsil, '
110: || ' msc_tp_id_lid mtil, '
111: || ' msc_trading_partner_sites mtps_site, '
112: || ' msc_trading_partners mtp_site '

Line 135: msd_dem_common_utilities.log_debug ('The query is - ');

131: */
132: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
133: || ' AND t.' || p_dest_column_name || ' LIKE ''' || to_char(p_sr_instance_id) || '::%''';
134:
135: msd_dem_common_utilities.log_debug ('The query is - ');
136: msd_dem_common_utilities.log_debug (x_sql);
137:
138: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
139: EXECUTE IMMEDIATE x_sql;

Line 136: msd_dem_common_utilities.log_debug (x_sql);

132: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
133: || ' AND t.' || p_dest_column_name || ' LIKE ''' || to_char(p_sr_instance_id) || '::%''';
134:
135: msd_dem_common_utilities.log_debug ('The query is - ');
136: msd_dem_common_utilities.log_debug (x_sql);
137:
138: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
139: EXECUTE IMMEDIATE x_sql;
140: x_num_rows := SQL%ROWCOUNT;

Line 138: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

134:
135: msd_dem_common_utilities.log_debug ('The query is - ');
136: msd_dem_common_utilities.log_debug (x_sql);
137:
138: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
139: EXECUTE IMMEDIATE x_sql;
140: x_num_rows := SQL%ROWCOUNT;
141: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
142: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

Line 141: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));

137:
138: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
139: EXECUTE IMMEDIATE x_sql;
140: x_num_rows := SQL%ROWCOUNT;
141: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
142: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
143: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
144:
145: COMMIT;

Line 142: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

138: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
139: EXECUTE IMMEDIATE x_sql;
140: x_num_rows := SQL%ROWCOUNT;
141: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
142: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
143: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
144:
145: COMMIT;
146:

Line 143: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

139: EXECUTE IMMEDIATE x_sql;
140: x_num_rows := SQL%ROWCOUNT;
141: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
142: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
143: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
144:
145: COMMIT;
146:
147: ELSIF (p_convert_type = 2)

Line 150: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');

146:
147: ELSIF (p_convert_type = 2)
148: THEN
149:
150: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
151:
152: x_sql := 'UPDATE ' || p_dest_table_name || ' t '
153: || ' SET t.' || p_dest_column_name || ' = nvl(('
154: || ' SELECT ''' || to_char(p_sr_instance_id) || ''' || ''::'' || mtsil.sr_tp_site_id '

Line 186: msd_dem_common_utilities.log_debug ('The query is - ');

182: || ' AND nvl(mtsil.sr_cust_acct_id, -1) = nvl(mtil.sr_tp_id, -1) AND rownum < 2), t.' || p_dest_column_name || ' ) '
183: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
184: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%:%:%''';
185:
186: msd_dem_common_utilities.log_debug ('The query is - ');
187: msd_dem_common_utilities.log_debug (x_sql);
188:
189: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
190: EXECUTE IMMEDIATE x_sql;

Line 187: msd_dem_common_utilities.log_debug (x_sql);

183: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
184: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%:%:%''';
185:
186: msd_dem_common_utilities.log_debug ('The query is - ');
187: msd_dem_common_utilities.log_debug (x_sql);
188:
189: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
190: EXECUTE IMMEDIATE x_sql;
191: x_num_rows := SQL%ROWCOUNT;

Line 189: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

185:
186: msd_dem_common_utilities.log_debug ('The query is - ');
187: msd_dem_common_utilities.log_debug (x_sql);
188:
189: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
190: EXECUTE IMMEDIATE x_sql;
191: x_num_rows := SQL%ROWCOUNT;
192: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
193: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

Line 192: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));

188:
189: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
190: EXECUTE IMMEDIATE x_sql;
191: x_num_rows := SQL%ROWCOUNT;
192: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
193: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
194: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
195:
196: COMMIT;

Line 193: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

189: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
190: EXECUTE IMMEDIATE x_sql;
191: x_num_rows := SQL%ROWCOUNT;
192: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
193: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
194: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
195:
196: COMMIT;
197:

Line 194: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

190: EXECUTE IMMEDIATE x_sql;
191: x_num_rows := SQL%ROWCOUNT;
192: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
193: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
194: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
195:
196: COMMIT;
197:
198: /* For Supplier Sites */

Line 199: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');

195:
196: COMMIT;
197:
198: /* For Supplier Sites */
199: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
200: x_sql := 'UPDATE ' || p_dest_table_name || ' t '
201: || ' SET t.' || p_dest_column_name || ' = nvl(('
202: || ' SELECT to_char(mtps.partner_site_id) '
203: || ' FROM msc_trading_partners mtp, '

Line 215: msd_dem_common_utilities.log_debug ('The query is - ');

211: || ' AND rownum < 2), t.' || p_dest_column_name || ' ) '
212: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
213: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%''';
214:
215: msd_dem_common_utilities.log_debug ('The query is - ');
216: msd_dem_common_utilities.log_debug (x_sql);
217:
218: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
219: EXECUTE IMMEDIATE x_sql;

Line 216: msd_dem_common_utilities.log_debug (x_sql);

212: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
213: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%''';
214:
215: msd_dem_common_utilities.log_debug ('The query is - ');
216: msd_dem_common_utilities.log_debug (x_sql);
217:
218: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
219: EXECUTE IMMEDIATE x_sql;
220: x_num_rows := SQL%ROWCOUNT;

Line 218: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

214:
215: msd_dem_common_utilities.log_debug ('The query is - ');
216: msd_dem_common_utilities.log_debug (x_sql);
217:
218: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
219: EXECUTE IMMEDIATE x_sql;
220: x_num_rows := SQL%ROWCOUNT;
221: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
222: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

Line 221: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));

217:
218: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
219: EXECUTE IMMEDIATE x_sql;
220: x_num_rows := SQL%ROWCOUNT;
221: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
222: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
223: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
224:
225: COMMIT;

Line 222: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

218: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
219: EXECUTE IMMEDIATE x_sql;
220: x_num_rows := SQL%ROWCOUNT;
221: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
222: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
223: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
224:
225: COMMIT;
226:

Line 223: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

219: EXECUTE IMMEDIATE x_sql;
220: x_num_rows := SQL%ROWCOUNT;
221: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
222: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
223: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
224:
225: COMMIT;
226:
227: END IF;

Line 229: msd_dem_common_utilities.log_debug ('Exiting SITE level');

225: COMMIT;
226:
227: END IF;
228:
229: msd_dem_common_utilities.log_debug ('Exiting SITE level');
230:
231:
232: ELSIF (p_level = 'ACCOUNT')
233: THEN

Line 236: msd_dem_common_utilities.log_debug ('Entering ACCOUNT level');

232: ELSIF (p_level = 'ACCOUNT')
233: THEN
234:
235:
236: msd_dem_common_utilities.log_debug ('Entering ACCOUNT level');
237:
238: IF (p_convert_type = 1)
239: THEN
240:

Line 241: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');

237:
238: IF (p_convert_type = 1)
239: THEN
240:
241: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
242:
243: ELSIF (p_convert_type = 2)
244: THEN
245:

Line 246: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');

242:
243: ELSIF (p_convert_type = 2)
244: THEN
245:
246: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
247:
248: x_sql := 'UPDATE ' || p_dest_table_name || ' t '
249: || ' SET t.' || p_dest_column_name || ' = nvl(('
250: || ' SELECT to_char(mtp.partner_id) || ''::'' || replace(mtil.sr_cust_account_number, '''''''', '''') '

Line 263: msd_dem_common_utilities.log_debug ('The query is - ');

259: || ' AND rownum < 2), t.' || p_dest_column_name || ' ) '
260: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
261: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%''';
262:
263: msd_dem_common_utilities.log_debug ('The query is - ');
264: msd_dem_common_utilities.log_debug (x_sql);
265:
266: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
267: EXECUTE IMMEDIATE x_sql;

Line 264: msd_dem_common_utilities.log_debug (x_sql);

260: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
261: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%''';
262:
263: msd_dem_common_utilities.log_debug ('The query is - ');
264: msd_dem_common_utilities.log_debug (x_sql);
265:
266: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
267: EXECUTE IMMEDIATE x_sql;
268: x_num_rows := SQL%ROWCOUNT;

Line 266: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

262:
263: msd_dem_common_utilities.log_debug ('The query is - ');
264: msd_dem_common_utilities.log_debug (x_sql);
265:
266: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
267: EXECUTE IMMEDIATE x_sql;
268: x_num_rows := SQL%ROWCOUNT;
269: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
270: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

Line 269: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));

265:
266: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
267: EXECUTE IMMEDIATE x_sql;
268: x_num_rows := SQL%ROWCOUNT;
269: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
270: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
271: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
272:
273: COMMIT;

Line 270: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

266: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
267: EXECUTE IMMEDIATE x_sql;
268: x_num_rows := SQL%ROWCOUNT;
269: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
270: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
271: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
272:
273: COMMIT;
274:

Line 271: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

267: EXECUTE IMMEDIATE x_sql;
268: x_num_rows := SQL%ROWCOUNT;
269: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
270: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
271: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
272:
273: COMMIT;
274:
275: END IF;

Line 277: msd_dem_common_utilities.log_debug ('Exiting ACCOUNT level');

273: COMMIT;
274:
275: END IF;
276:
277: msd_dem_common_utilities.log_debug ('Exiting ACCOUNT level');
278:
279:
280: ELSIF (p_level = 'CUSTOMER')
281: THEN

Line 283: msd_dem_common_utilities.log_debug ('Entering CUSTOMER level');

279:
280: ELSIF (p_level = 'CUSTOMER')
281: THEN
282:
283: msd_dem_common_utilities.log_debug ('Entering CUSTOMER level');
284:
285: IF (p_convert_type = 1)
286: THEN
287:

Line 288: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');

284:
285: IF (p_convert_type = 1)
286: THEN
287:
288: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
289:
290: ELSIF (p_convert_type = 2)
291: THEN
292:

Line 293: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');

289:
290: ELSIF (p_convert_type = 2)
291: THEN
292:
293: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
294:
295: x_sql := 'UPDATE ' || p_dest_table_name || ' t '
296: || ' SET t.' || p_dest_column_name || ' = nvl(('
297: || ' SELECT to_char(mtp.partner_id) '

Line 303: msd_dem_common_utilities.log_debug ('The query is - ');

299: || ' WHERE mtp.partner_type = 2 '
300: || ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = t.' || p_dest_column_name || ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
301: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || '''';
302:
303: msd_dem_common_utilities.log_debug ('The query is - ');
304: msd_dem_common_utilities.log_debug (x_sql);
305:
306: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
307: EXECUTE IMMEDIATE x_sql;

Line 304: msd_dem_common_utilities.log_debug (x_sql);

300: || ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = t.' || p_dest_column_name || ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
301: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || '''';
302:
303: msd_dem_common_utilities.log_debug ('The query is - ');
304: msd_dem_common_utilities.log_debug (x_sql);
305:
306: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
307: EXECUTE IMMEDIATE x_sql;
308: x_num_rows := SQL%ROWCOUNT;

Line 306: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

302:
303: msd_dem_common_utilities.log_debug ('The query is - ');
304: msd_dem_common_utilities.log_debug (x_sql);
305:
306: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
307: EXECUTE IMMEDIATE x_sql;
308: x_num_rows := SQL%ROWCOUNT;
309: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
310: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

Line 309: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));

305:
306: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
307: EXECUTE IMMEDIATE x_sql;
308: x_num_rows := SQL%ROWCOUNT;
309: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
310: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
311: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
312:
313: COMMIT;

Line 310: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

306: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
307: EXECUTE IMMEDIATE x_sql;
308: x_num_rows := SQL%ROWCOUNT;
309: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
310: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
311: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
312:
313: COMMIT;
314:

Line 311: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

307: EXECUTE IMMEDIATE x_sql;
308: x_num_rows := SQL%ROWCOUNT;
309: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
310: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
311: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
312:
313: COMMIT;
314:
315: END IF;

Line 317: msd_dem_common_utilities.log_debug ('Exiting CUSTOMER level');

313: COMMIT;
314:
315: END IF;
316:
317: msd_dem_common_utilities.log_debug ('Exiting CUSTOMER level');
318:
319: ELSIF (p_level = 'SUPPLIER')
320: THEN
321:

Line 322: msd_dem_common_utilities.log_debug ('Entering SUPPLIER level');

318:
319: ELSIF (p_level = 'SUPPLIER')
320: THEN
321:
322: msd_dem_common_utilities.log_debug ('Entering SUPPLIER level');
323:
324: IF (p_convert_type = 1)
325: THEN
326:

Line 327: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');

323:
324: IF (p_convert_type = 1)
325: THEN
326:
327: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
328:
329: ELSIF (p_convert_type = 2)
330: THEN
331:

Line 332: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');

328:
329: ELSIF (p_convert_type = 2)
330: THEN
331:
332: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
333:
334: x_sql := 'UPDATE ' || p_dest_table_name || ' t '
335: || ' SET t.' || p_dest_column_name || ' = nvl(('
336: || ' SELECT to_char(mtp.partner_id) '

Line 342: msd_dem_common_utilities.log_debug ('The query is - ');

338: || ' WHERE mtp.partner_type = 1 '
339: || ' AND replace(mtp.partner_name, '''''''', '''') = t.' || p_dest_column_name || ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
340: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || '''';
341:
342: msd_dem_common_utilities.log_debug ('The query is - ');
343: msd_dem_common_utilities.log_debug (x_sql);
344:
345: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
346: EXECUTE IMMEDIATE x_sql;

Line 343: msd_dem_common_utilities.log_debug (x_sql);

339: || ' AND replace(mtp.partner_name, '''''''', '''') = t.' || p_dest_column_name || ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
340: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || '''';
341:
342: msd_dem_common_utilities.log_debug ('The query is - ');
343: msd_dem_common_utilities.log_debug (x_sql);
344:
345: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
346: EXECUTE IMMEDIATE x_sql;
347: x_num_rows := SQL%ROWCOUNT;

Line 345: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

341:
342: msd_dem_common_utilities.log_debug ('The query is - ');
343: msd_dem_common_utilities.log_debug (x_sql);
344:
345: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
346: EXECUTE IMMEDIATE x_sql;
347: x_num_rows := SQL%ROWCOUNT;
348: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
349: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

Line 348: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));

344:
345: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
346: EXECUTE IMMEDIATE x_sql;
347: x_num_rows := SQL%ROWCOUNT;
348: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
349: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
350: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
351:
352: COMMIT;

Line 349: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

345: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
346: EXECUTE IMMEDIATE x_sql;
347: x_num_rows := SQL%ROWCOUNT;
348: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
349: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
350: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
351:
352: COMMIT;
353:

Line 350: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

346: EXECUTE IMMEDIATE x_sql;
347: x_num_rows := SQL%ROWCOUNT;
348: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
349: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
350: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
351:
352: COMMIT;
353:
354: END IF;

Line 356: msd_dem_common_utilities.log_debug ('Exiting SUPPLIER level');

352: COMMIT;
353:
354: END IF;
355:
356: msd_dem_common_utilities.log_debug ('Exiting SUPPLIER level');
357:
358: ELSIF (p_level = 'TRADING PARTNER ZONE')
359: THEN
360:

Line 361: msd_dem_common_utilities.log_debug ('Entering TRADING PARTNER ZONE level');

357:
358: ELSIF (p_level = 'TRADING PARTNER ZONE')
359: THEN
360:
361: msd_dem_common_utilities.log_debug ('Entering TRADING PARTNER ZONE level');
362:
363: IF (p_convert_type = 1)
364: THEN
365:

Line 366: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');

362:
363: IF (p_convert_type = 1)
364: THEN
365:
366: msd_dem_common_utilities.log_debug ('Convert from new to old - Building update query ');
367:
368: ELSIF (p_convert_type = 2)
369: THEN
370:

Line 371: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');

367:
368: ELSIF (p_convert_type = 2)
369: THEN
370:
371: msd_dem_common_utilities.log_debug ('Convert from old to new - Building update query ');
372:
373: x_sql := 'UPDATE ' || p_dest_table_name || ' t '
374: || ' SET t.' || p_dest_column_name || ' = nvl(('
375: || ' SELECT to_char(mtp.partner_id) || replace(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':''), '

Line 384: msd_dem_common_utilities.log_debug ('The query is - ');

380: || ' AND rownum < 2 ), t.' || p_dest_column_name || ' ) '
381: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
382: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%:%''';
383:
384: msd_dem_common_utilities.log_debug ('The query is - ');
385: msd_dem_common_utilities.log_debug (x_sql);
386:
387: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
388: EXECUTE IMMEDIATE x_sql;

Line 385: msd_dem_common_utilities.log_debug (x_sql);

381: || ' WHERE t.' || p_dest_column_name || ' <> ''' || msd_dem_sr_util.get_null_code || ''''
382: || ' AND t.' || p_dest_column_name || ' LIKE ''%:%:%''';
383:
384: msd_dem_common_utilities.log_debug ('The query is - ');
385: msd_dem_common_utilities.log_debug (x_sql);
386:
387: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
388: EXECUTE IMMEDIATE x_sql;
389: x_num_rows := SQL%ROWCOUNT;

Line 387: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

383:
384: msd_dem_common_utilities.log_debug ('The query is - ');
385: msd_dem_common_utilities.log_debug (x_sql);
386:
387: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
388: EXECUTE IMMEDIATE x_sql;
389: x_num_rows := SQL%ROWCOUNT;
390: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
391: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

Line 390: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));

386:
387: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
388: EXECUTE IMMEDIATE x_sql;
389: x_num_rows := SQL%ROWCOUNT;
390: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
391: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
392: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
393:
394: COMMIT;

Line 391: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));

387: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
388: EXECUTE IMMEDIATE x_sql;
389: x_num_rows := SQL%ROWCOUNT;
390: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
391: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
392: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
393:
394: COMMIT;
395:

Line 392: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

388: EXECUTE IMMEDIATE x_sql;
389: x_num_rows := SQL%ROWCOUNT;
390: msd_dem_common_utilities.log_message ('Number of rows updated - ' || to_char(x_num_rows));
391: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
392: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
393:
394: COMMIT;
395:
396: END IF;

Line 398: msd_dem_common_utilities.log_debug ('Exiting TRADING PARTNER ZONE level');

394: COMMIT;
395:
396: END IF;
397:
398: msd_dem_common_utilities.log_debug ('Exiting TRADING PARTNER ZONE level');
399:
400: END IF;
401:
402:

Line 403: msd_dem_common_utilities.log_debug('Exiting msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));

399:
400: END IF;
401:
402:
403: msd_dem_common_utilities.log_debug('Exiting msd_dem_update_level_codes.convert_site_code' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
404:
405: EXCEPTION
406: WHEN OTHERS THEN
407: errbuf := substr(SQLERRM,1,150);

Line 410: 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'));

406: WHEN OTHERS THEN
407: errbuf := substr(SQLERRM,1,150);
408: retcode := -1;
409:
410: 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'));
411: msd_dem_common_utilities.log_message (errbuf);
412: RETURN;
413:
414: END CONVERT_SITE_CODE;

Line 411: msd_dem_common_utilities.log_message (errbuf);

407: errbuf := substr(SQLERRM,1,150);
408: retcode := -1;
409:
410: 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'));
411: msd_dem_common_utilities.log_message (errbuf);
412: RETURN;
413:
414: END CONVERT_SITE_CODE;
415:

Line 455: 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'));

451: x_retcode VARCHAR2(1000) := NULL;
452:
453: BEGIN
454:
455: 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'));
456:
457: /*** Validation - START ***/
458:
459: /* Exit with Warning if profile MSD_DEM: Debug Mode is set to no */

Line 464: 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'));

460: IF (nvl(fnd_profile.value('MSD_DEM_DEBUG_MODE'), 'N') <> 'Y')
461: THEN
462: errbuf := 'Profile MSD_DEM: Debug Mode is set to No. Please set this profile to Yes. Exiting normally without any processing.';
463: retcode := 1;
464: 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'));
465: msd_dem_common_utilities.log_message ('Profile MSD_DEM: Debug Mode is set to No. Please set this profile to Yes. Exiting normally without any processing.');
466: RETURN;
467: ELSE
468: msd_dem_common_utilities.log_debug ('Debug Profile is set to Yes');

Line 465: msd_dem_common_utilities.log_message ('Profile MSD_DEM: Debug Mode is set to No. Please set this profile to Yes. Exiting normally without any processing.');

461: THEN
462: errbuf := 'Profile MSD_DEM: Debug Mode is set to No. Please set this profile to Yes. Exiting normally without any processing.';
463: retcode := 1;
464: 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'));
465: msd_dem_common_utilities.log_message ('Profile MSD_DEM: Debug Mode is set to No. Please set this profile to Yes. Exiting normally without any processing.');
466: RETURN;
467: ELSE
468: msd_dem_common_utilities.log_debug ('Debug Profile is set to Yes');
469: END IF;

Line 468: msd_dem_common_utilities.log_debug ('Debug Profile is set to Yes');

464: 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'));
465: msd_dem_common_utilities.log_message ('Profile MSD_DEM: Debug Mode is set to No. Please set this profile to Yes. Exiting normally without any processing.');
466: RETURN;
467: ELSE
468: msd_dem_common_utilities.log_debug ('Debug Profile is set to Yes');
469: END IF;
470:
471: /* Get the Demantra Schema Name and verify that the five tables are present or not */
472: x_dem_schema := upper(fnd_profile.value('MSD_DEM_SCHEMA'));

Line 473: msd_dem_common_utilities.log_message ('The Demantra schema is - ' || x_dem_schema);

469: END IF;
470:
471: /* Get the Demantra Schema Name and verify that the five tables are present or not */
472: x_dem_schema := upper(fnd_profile.value('MSD_DEM_SCHEMA'));
473: msd_dem_common_utilities.log_message ('The Demantra schema is - ' || x_dem_schema);
474:
475: 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'') '
476: || ' AND object_type = ''TABLE'''
477: INTO x_is_present;

Line 484: 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'));

480: THEN
481:
482: errbuf := 'One or more of the following are missing ''T_EP_SITE'', ''T_EP_EBS_ACCOUNT'', ''T_EP_EBS_CUSTOMER'', ''T_EP_EBS_TP_ZONE'', ''T_EP_EBS_SUPPLIER''';
483: retcode := -1;
484: 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'));
485: msd_dem_common_utilities.log_message ('One or more of the following are missing ''T_EP_SITE'', ''T_EP_EBS_ACCOUNT'', ''T_EP_EBS_CUSTOMER'', ''T_EP_EBS_TP_ZONE'', ''T_EP_EBS_SUPPLIER''');
486: RETURN;
487:
488: END IF;

Line 485: msd_dem_common_utilities.log_message ('One or more of the following are missing ''T_EP_SITE'', ''T_EP_EBS_ACCOUNT'', ''T_EP_EBS_CUSTOMER'', ''T_EP_EBS_TP_ZONE'', ''T_EP_EBS_SUPPLIER''');

481:
482: errbuf := 'One or more of the following are missing ''T_EP_SITE'', ''T_EP_EBS_ACCOUNT'', ''T_EP_EBS_CUSTOMER'', ''T_EP_EBS_TP_ZONE'', ''T_EP_EBS_SUPPLIER''';
483: retcode := -1;
484: 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'));
485: msd_dem_common_utilities.log_message ('One or more of the following are missing ''T_EP_SITE'', ''T_EP_EBS_ACCOUNT'', ''T_EP_EBS_CUSTOMER'', ''T_EP_EBS_TP_ZONE'', ''T_EP_EBS_SUPPLIER''');
486: RETURN;
487:
488: END IF;
489:

Line 496: 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'));

492: THEN
493:
494: errbuf := 'The profile MSD_DEM: Use new Site code format is already set to Yes. This procedure should not be run.';
495: retcode := -1;
496: 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'));
497: msd_dem_common_utilities.log_message ('The profile MSD_DEM: Use new Site code format is already set to Yes. This procedure should not be run.');
498: RETURN;
499:
500: END IF;

Line 497: msd_dem_common_utilities.log_message ('The profile MSD_DEM: Use new Site code format is already set to Yes. This procedure should not be run.');

493:
494: errbuf := 'The profile MSD_DEM: Use new Site code format is already set to Yes. This procedure should not be run.';
495: retcode := -1;
496: 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'));
497: msd_dem_common_utilities.log_message ('The profile MSD_DEM: Use new Site code format is already set to Yes. This procedure should not be run.');
498: RETURN;
499:
500: END IF;
501:

Line 509: msd_dem_common_utilities.log_message ('Backup table for SITE is - ' || x_bk_table_name);

505: /*** Create Backup Tables - START ***/
506:
507: /* SITE */
508: x_bk_table_name := 'MSD_BK_SITE_' || to_char(sysdate, 'ddmmyyhh24miss');
509: msd_dem_common_utilities.log_message ('Backup table for SITE is - ' || x_bk_table_name);
510:
511: x_sql := 'CREATE TABLE ' || x_bk_table_name
512: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_SITE';
513:

Line 514: msd_dem_common_utilities.log_debug ('The query is - ');

510:
511: x_sql := 'CREATE TABLE ' || x_bk_table_name
512: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_SITE';
513:
514: msd_dem_common_utilities.log_debug ('The query is - ');
515: msd_dem_common_utilities.log_debug (x_sql);
516:
517: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
518: EXECUTE IMMEDIATE x_sql;

Line 515: msd_dem_common_utilities.log_debug (x_sql);

511: x_sql := 'CREATE TABLE ' || x_bk_table_name
512: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_SITE';
513:
514: msd_dem_common_utilities.log_debug ('The query is - ');
515: msd_dem_common_utilities.log_debug (x_sql);
516:
517: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
518: EXECUTE IMMEDIATE x_sql;
519: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

Line 517: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

513:
514: msd_dem_common_utilities.log_debug ('The query is - ');
515: msd_dem_common_utilities.log_debug (x_sql);
516:
517: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
518: EXECUTE IMMEDIATE x_sql;
519: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
520: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
521:

Line 519: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

515: msd_dem_common_utilities.log_debug (x_sql);
516:
517: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
518: EXECUTE IMMEDIATE x_sql;
519: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
520: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
521:
522:
523: /* ACCOUNT */

Line 520: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);

516:
517: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
518: EXECUTE IMMEDIATE x_sql;
519: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
520: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
521:
522:
523: /* ACCOUNT */
524: x_bk_table_name := 'MSD_BK_ACCOUNT_' || to_char(sysdate, 'ddmmyyhh24miss');

Line 525: msd_dem_common_utilities.log_message ('Backup table for ACCOUNT is - ' || x_bk_table_name);

521:
522:
523: /* ACCOUNT */
524: x_bk_table_name := 'MSD_BK_ACCOUNT_' || to_char(sysdate, 'ddmmyyhh24miss');
525: msd_dem_common_utilities.log_message ('Backup table for ACCOUNT is - ' || x_bk_table_name);
526:
527: x_sql := 'CREATE TABLE ' || x_bk_table_name
528: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_ACCOUNT';
529:

Line 530: msd_dem_common_utilities.log_debug ('The query is - ');

526:
527: x_sql := 'CREATE TABLE ' || x_bk_table_name
528: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_ACCOUNT';
529:
530: msd_dem_common_utilities.log_debug ('The query is - ');
531: msd_dem_common_utilities.log_debug (x_sql);
532:
533: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
534: EXECUTE IMMEDIATE x_sql;

Line 531: msd_dem_common_utilities.log_debug (x_sql);

527: x_sql := 'CREATE TABLE ' || x_bk_table_name
528: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_ACCOUNT';
529:
530: msd_dem_common_utilities.log_debug ('The query is - ');
531: msd_dem_common_utilities.log_debug (x_sql);
532:
533: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
534: EXECUTE IMMEDIATE x_sql;
535: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

Line 533: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

529:
530: msd_dem_common_utilities.log_debug ('The query is - ');
531: msd_dem_common_utilities.log_debug (x_sql);
532:
533: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
534: EXECUTE IMMEDIATE x_sql;
535: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
536: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
537:

Line 535: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

531: msd_dem_common_utilities.log_debug (x_sql);
532:
533: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
534: EXECUTE IMMEDIATE x_sql;
535: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
536: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
537:
538:
539: /* CUSTOMER */

Line 536: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);

532:
533: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
534: EXECUTE IMMEDIATE x_sql;
535: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
536: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
537:
538:
539: /* CUSTOMER */
540: x_bk_table_name := 'MSD_BK_CUSTOMER_' || to_char(sysdate, 'ddmmyyhh24miss');

Line 541: msd_dem_common_utilities.log_message ('Backup table for CUSTOMER is - ' || x_bk_table_name);

537:
538:
539: /* CUSTOMER */
540: x_bk_table_name := 'MSD_BK_CUSTOMER_' || to_char(sysdate, 'ddmmyyhh24miss');
541: msd_dem_common_utilities.log_message ('Backup table for CUSTOMER is - ' || x_bk_table_name);
542:
543: x_sql := 'CREATE TABLE ' || x_bk_table_name
544: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_CUSTOMER';
545:

Line 546: msd_dem_common_utilities.log_debug ('The query is - ');

542:
543: x_sql := 'CREATE TABLE ' || x_bk_table_name
544: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_CUSTOMER';
545:
546: msd_dem_common_utilities.log_debug ('The query is - ');
547: msd_dem_common_utilities.log_debug (x_sql);
548:
549: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
550: EXECUTE IMMEDIATE x_sql;

Line 547: msd_dem_common_utilities.log_debug (x_sql);

543: x_sql := 'CREATE TABLE ' || x_bk_table_name
544: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_CUSTOMER';
545:
546: msd_dem_common_utilities.log_debug ('The query is - ');
547: msd_dem_common_utilities.log_debug (x_sql);
548:
549: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
550: EXECUTE IMMEDIATE x_sql;
551: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

Line 549: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

545:
546: msd_dem_common_utilities.log_debug ('The query is - ');
547: msd_dem_common_utilities.log_debug (x_sql);
548:
549: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
550: EXECUTE IMMEDIATE x_sql;
551: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
552: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
553:

Line 551: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

547: msd_dem_common_utilities.log_debug (x_sql);
548:
549: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
550: EXECUTE IMMEDIATE x_sql;
551: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
552: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
553:
554:
555: /* TRADING PARTNER ZONE */

Line 552: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);

548:
549: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
550: EXECUTE IMMEDIATE x_sql;
551: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
552: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
553:
554:
555: /* TRADING PARTNER ZONE */
556: x_bk_table_name := 'MSD_BK_TPZONE_' || to_char(sysdate, 'ddmmyyhh24miss');

Line 557: msd_dem_common_utilities.log_message ('Backup table for TRADING PARTNER ZONE is - ' || x_bk_table_name);

553:
554:
555: /* TRADING PARTNER ZONE */
556: x_bk_table_name := 'MSD_BK_TPZONE_' || to_char(sysdate, 'ddmmyyhh24miss');
557: msd_dem_common_utilities.log_message ('Backup table for TRADING PARTNER ZONE is - ' || x_bk_table_name);
558:
559: x_sql := 'CREATE TABLE ' || x_bk_table_name
560: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_TP_ZONE';
561:

Line 562: msd_dem_common_utilities.log_debug ('The query is - ');

558:
559: x_sql := 'CREATE TABLE ' || x_bk_table_name
560: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_TP_ZONE';
561:
562: msd_dem_common_utilities.log_debug ('The query is - ');
563: msd_dem_common_utilities.log_debug (x_sql);
564:
565: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
566: EXECUTE IMMEDIATE x_sql;

Line 563: msd_dem_common_utilities.log_debug (x_sql);

559: x_sql := 'CREATE TABLE ' || x_bk_table_name
560: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_TP_ZONE';
561:
562: msd_dem_common_utilities.log_debug ('The query is - ');
563: msd_dem_common_utilities.log_debug (x_sql);
564:
565: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
566: EXECUTE IMMEDIATE x_sql;
567: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

Line 565: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

561:
562: msd_dem_common_utilities.log_debug ('The query is - ');
563: msd_dem_common_utilities.log_debug (x_sql);
564:
565: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
566: EXECUTE IMMEDIATE x_sql;
567: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
568: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
569:

Line 567: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

563: msd_dem_common_utilities.log_debug (x_sql);
564:
565: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
566: EXECUTE IMMEDIATE x_sql;
567: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
568: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
569:
570:
571: /* SUPPLIER */

Line 568: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);

564:
565: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
566: EXECUTE IMMEDIATE x_sql;
567: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
568: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
569:
570:
571: /* SUPPLIER */
572: x_bk_table_name := 'MSD_BK_SUPPLIER_' || to_char(sysdate, 'ddmmyyhh24miss');

Line 573: msd_dem_common_utilities.log_message ('Backup table for SUPPLIER is - ' || x_bk_table_name);

569:
570:
571: /* SUPPLIER */
572: x_bk_table_name := 'MSD_BK_SUPPLIER_' || to_char(sysdate, 'ddmmyyhh24miss');
573: msd_dem_common_utilities.log_message ('Backup table for SUPPLIER is - ' || x_bk_table_name);
574:
575: x_sql := 'CREATE TABLE ' || x_bk_table_name
576: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_SUPPLIER';
577:

Line 578: msd_dem_common_utilities.log_debug ('The query is - ');

574:
575: x_sql := 'CREATE TABLE ' || x_bk_table_name
576: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_SUPPLIER';
577:
578: msd_dem_common_utilities.log_debug ('The query is - ');
579: msd_dem_common_utilities.log_debug (x_sql);
580:
581: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
582: EXECUTE IMMEDIATE x_sql;

Line 579: msd_dem_common_utilities.log_debug (x_sql);

575: x_sql := 'CREATE TABLE ' || x_bk_table_name
576: || ' AS SELECT * FROM ' || x_dem_schema || '.T_EP_EBS_SUPPLIER';
577:
578: msd_dem_common_utilities.log_debug ('The query is - ');
579: msd_dem_common_utilities.log_debug (x_sql);
580:
581: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
582: EXECUTE IMMEDIATE x_sql;
583: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

Line 581: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

577:
578: msd_dem_common_utilities.log_debug ('The query is - ');
579: msd_dem_common_utilities.log_debug (x_sql);
580:
581: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
582: EXECUTE IMMEDIATE x_sql;
583: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
584: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
585:

Line 583: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );

579: msd_dem_common_utilities.log_debug (x_sql);
580:
581: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
582: EXECUTE IMMEDIATE x_sql;
583: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
584: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
585:
586:
587: /*** Create Backup Tables - END ***/

Line 584: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);

580:
581: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
582: EXECUTE IMMEDIATE x_sql;
583: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
584: msd_dem_common_utilities.log_message('Created table ' || x_bk_table_name);
585:
586:
587: /*** Create Backup Tables - END ***/
588:

Line 606: 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'));

602: THEN
603:
604: errbuf := x_errbuf;
605: retcode := -1;
606: 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'));
607: msd_dem_common_utilities.log_message ('Error while updating SITE level code');
608: RETURN;
609:
610: END IF;

Line 607: msd_dem_common_utilities.log_message ('Error while updating SITE level code');

603:
604: errbuf := x_errbuf;
605: retcode := -1;
606: 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'));
607: msd_dem_common_utilities.log_message ('Error while updating SITE level code');
608: RETURN;
609:
610: END IF;
611:

Line 627: 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'));

623: THEN
624:
625: errbuf := x_errbuf;
626: retcode := -1;
627: 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'));
628: msd_dem_common_utilities.log_message ('Error while updating ACCOUNT level code');
629: RETURN;
630:
631: END IF;

Line 628: msd_dem_common_utilities.log_message ('Error while updating ACCOUNT level code');

624:
625: errbuf := x_errbuf;
626: retcode := -1;
627: 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'));
628: msd_dem_common_utilities.log_message ('Error while updating ACCOUNT level code');
629: RETURN;
630:
631: END IF;
632:

Line 648: 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'));

644: THEN
645:
646: errbuf := x_errbuf;
647: retcode := -1;
648: 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'));
649: msd_dem_common_utilities.log_message ('Error while updating CUSTOMER level code');
650: RETURN;
651:
652: END IF;

Line 649: msd_dem_common_utilities.log_message ('Error while updating CUSTOMER level code');

645:
646: errbuf := x_errbuf;
647: retcode := -1;
648: 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'));
649: msd_dem_common_utilities.log_message ('Error while updating CUSTOMER level code');
650: RETURN;
651:
652: END IF;
653:

Line 669: 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'));

665: THEN
666:
667: errbuf := x_errbuf;
668: retcode := -1;
669: 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'));
670: msd_dem_common_utilities.log_message ('Error while updating TRADING PARTNER ZONE level code');
671: RETURN;
672:
673: END IF;

Line 670: msd_dem_common_utilities.log_message ('Error while updating TRADING PARTNER ZONE level code');

666:
667: errbuf := x_errbuf;
668: retcode := -1;
669: 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'));
670: msd_dem_common_utilities.log_message ('Error while updating TRADING PARTNER ZONE level code');
671: RETURN;
672:
673: END IF;
674:

Line 690: 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'));

686: THEN
687:
688: errbuf := x_errbuf;
689: retcode := -1;
690: 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'));
691: msd_dem_common_utilities.log_message ('Error while updating SUPPLIER level code');
692: RETURN;
693:
694: END IF;

Line 691: msd_dem_common_utilities.log_message ('Error while updating SUPPLIER level code');

687:
688: errbuf := x_errbuf;
689: retcode := -1;
690: 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'));
691: msd_dem_common_utilities.log_message ('Error while updating SUPPLIER level code');
692: RETURN;
693:
694: END IF;
695:

Line 703: msd_dem_common_utilities.log_message ('Profile MSD_DEM_SITE_CODE_FORMAT has been set to Yes at the SITE level.');

699:
700: /* Set Profile MSD_DEM_SITE_CODE_FORMAT to Yes */
701: x_ret_val := fnd_profile.save('MSD_DEM_SITE_CODE_FORMAT', 1, 'SITE');
702: COMMIT;
703: msd_dem_common_utilities.log_message ('Profile MSD_DEM_SITE_CODE_FORMAT has been set to Yes at the SITE level.');
704:
705: 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'));
706:
707: EXCEPTION

Line 705: 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'));

701: x_ret_val := fnd_profile.save('MSD_DEM_SITE_CODE_FORMAT', 1, 'SITE');
702: COMMIT;
703: msd_dem_common_utilities.log_message ('Profile MSD_DEM_SITE_CODE_FORMAT has been set to Yes at the SITE level.');
704:
705: 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'));
706:
707: EXCEPTION
708: WHEN OTHERS THEN
709: errbuf := substr(SQLERRM,1,150);

Line 712: 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'));

708: WHEN OTHERS THEN
709: errbuf := substr(SQLERRM,1,150);
710: retcode := -1;
711:
712: 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'));
713: msd_dem_common_utilities.log_message (errbuf);
714:
715: END UPGRADE_GEO_LEVEL_CODES;
716:

Line 713: msd_dem_common_utilities.log_message (errbuf);

709: errbuf := substr(SQLERRM,1,150);
710: retcode := -1;
711:
712: 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'));
713: msd_dem_common_utilities.log_message (errbuf);
714:
715: END UPGRADE_GEO_LEVEL_CODES;
716:
717: END MSD_DEM_UPDATE_LEVEL_CODES;