[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_UPDATE_LEVEL_CODES
Source
1 PACKAGE BODY MSD_DEM_UPDATE_LEVEL_CODES AS
2 /* $Header: msddemupdlvb.pls 120.3.12020000.3 2012/10/01 11:11:02 kkhatri ship $ */
3
4
5 procedure update_code(errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY VARCHAR2,
7 p_instance_id IN NUMBER,
8 p_level IN VARCHAR2,
9 p_dest_table_name IN VARCHAR2,
10 p_dest_column_name IN VARCHAR2,
11 p_src_coulmn_name IN VARCHAR2)
12
13 as
14
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
24 dbms_output.put_line(p_dest_table_name || p_dest_column_name || p_src_coulmn_name || p_instance_id);
25
26 if p_level = 'SITE' then
27
28 msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_LEVEL_UPDATE', p_instance_id);
29
30 l_stmt := replace(l_stmt, 'DEST_TABLE', p_dest_table_name);
31 l_stmt := replace(l_stmt, 'DEST_COLUMN', p_dest_column_name);
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;
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;
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;
55
56
57 /*
58 * This procedure converts the level code format from descriptive to integer format based upon the
59 * given parameters.
60 * If p_convert_type = 1, then change from new to old (descriptive)
61 * If p_convert_type = 2, then change from old to new
62 */
63 PROCEDURE CONVERT_SITE_CODE (
64 errbuf OUT NOCOPY VARCHAR2,
65 retcode OUT NOCOPY VARCHAR2,
66 p_sr_instance_id IN NUMBER,
67 p_level IN VARCHAR2,
68 p_dest_table_name IN VARCHAR2,
69 p_dest_column_name IN VARCHAR2,
70 p_convert_type IN NUMBER)
71 IS
72
73 x_sql VARCHAR2(4000) := NULL;
74 x_num_rows NUMBER := 0;
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);
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
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
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) '
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 '
113 --|| ' msc_location_associations mla, ' --bug#13604468
114 --|| ' msc_trading_partners mtp_app_org ' -- bug#13604468
115 || ' WHERE mtsil.sr_instance_id = ' || to_char(p_sr_instance_id)
116 || ' AND mtsil.sr_tp_site_id = to_number(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', ''::'') + 2)) '
117 || ' AND mtsil.partner_type = 2 '
118 || ' AND mtsil.tp_site_id = mtps_site.partner_site_id '
119 || ' AND mtsil.sr_instance_id = mtil.sr_instance_id '
120 || ' AND mtsil.sr_cust_acct_id = mtil.sr_tp_id '
121 || ' AND mtil.partner_type = 2 '
122 || ' AND mtps_site.partner_id = mtp_site.partner_id '
123 || ' AND mtps_site.partner_type = 2 '
124 || ' AND mtp_site.partner_type = 2 )'
125 /* -- commented bug#13604468 --12.3--Bug 14696269--12.2.1--kkhatri
126 || ' AND mla.sr_instance_id(+) = mtsil.sr_instance_id '
127 || ' AND mla.partner_site_id(+) = mtsil.tp_site_id '
128 || ' AND mtp_app_org.sr_tp_id(+) = mla.organization_id '
129 || ' AND mtp_app_org.sr_instance_id(+) = mla.sr_instance_id '
130 || ' AND mtp_app_org.partner_type(+) = 3 )'
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;
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)
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 '
155 || ' FROM msc_trading_partners mtp, '
156 || ' msc_tp_id_lid mtil, '
157 || ' msc_trading_partner_sites mtps, '
158 || ' msc_tp_site_id_lid mtsil '
159 || ' WHERE mtp.partner_type = 2 '
160 || ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
161 || ' AND mtil.partner_type = mtp.partner_type '
162 || ' AND mtil.sr_instance_id = ' || to_char(p_sr_instance_id)
163 || ' AND mtil.tp_id = mtp.partner_id '
164 || ' AND nvl(mtil.sr_cust_account_number, ''###'') = nvl(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'') +1, instr(t.'
165 || p_dest_column_name || ', '':'', 1, 2) - instr(t.' || p_dest_column_name || ', '':'', 1, 1) -1), ''###'') '
166 || ' AND mtps.partner_type = mtp.partner_type '
167 || ' AND mtps.partner_id = mtp.partner_id '
168 || ' AND replace(mtps.location, '''''''', '''') = substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'', 1, 2) +1, instr(t.'
169 || p_dest_column_name || ', '':'', 1, 3) - instr(t.' || p_dest_column_name || ', '':'', 1, 2) -1) ';
170
171 IF (x_instance_type IN (1,2,4))
172 THEN
173 x_sql := x_sql || ' AND mtps.tp_site_code = ''SHIP_TO''';
174 END IF;
175
176 x_sql := x_sql || ' AND replace(nvl(mtps.operating_unit_name, ''###''), '''''''', '''') = nvl(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'', 1, 3) + 1, '
177 || ' decode (instr(t.' || p_dest_column_name || ', '':'', 1, 4), 0, length(t.' || p_dest_column_name || ') + 1, instr(t.' || p_dest_column_name || ', '':'', 1, 4)) - '
178 || ' instr(t.' || p_dest_column_name || ', '':'', 1, 3) - 1), ''###'') '
179 || ' AND mtsil.partner_type = mtps.partner_type '
180 || ' AND mtsil.sr_instance_id = ' || to_char(p_sr_instance_id)
181 || ' AND mtsil.tp_site_id = mtps.partner_site_id '
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;
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 */
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, '
204 || ' msc_trading_partner_sites mtps '
205 || ' WHERE mtp.partner_type = 1 '
206 || ' AND replace(mtp.partner_name, '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
207 || ' AND mtps.partner_type = 1 '
208 || ' AND mtps.partner_id = mtp.partner_id '
209 || ' AND replace(mtps.tp_site_code, '''''''', '''') = substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'') + 1, '
210 || 'length(t.' || p_dest_column_name || ') - instr(t.' || p_dest_column_name || ', '':'')) '
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;
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;
228
229 msd_dem_common_utilities.log_debug ('Exiting SITE level');
230
231
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
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
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, '''''''', '''') '
251 || ' FROM msc_trading_partners mtp, '
252 || ' msc_tp_id_lid mtil '
253 || ' WHERE mtp.partner_type = 2 '
254 || ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
255 || ' AND mtil.sr_instance_id = ' || to_char(p_sr_instance_id)
256 || ' AND mtil.tp_id = mtp.partner_id '
257 || ' AND replace(nvl(mtil.sr_cust_account_number, ''###''), '''''''', '''') = nvl(substr(t.' || p_dest_column_name || ', instr(t.' || p_dest_column_name || ', '':'') + 1, '
258 || 'length(t.' || p_dest_column_name || ') - instr(t.' || p_dest_column_name || ', '':'')), ''###'') '
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;
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;
276
277 msd_dem_common_utilities.log_debug ('Exiting ACCOUNT level');
278
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
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
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) '
298 || ' FROM msc_trading_partners mtp '
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;
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;
316
317 msd_dem_common_utilities.log_debug ('Exiting CUSTOMER 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
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
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) '
337 || ' FROM msc_trading_partners mtp '
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;
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;
355
356 msd_dem_common_utilities.log_debug ('Exiting SUPPLIER 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
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
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 || ', '':''), '
376 || ' length(t.' || p_dest_column_name || ') - instr(t.' || p_dest_column_name || ', '':'') + 1 ), '':'', ''::'') '
377 || ' FROM msc_trading_partners mtp '
378 || ' WHERE mtp.partner_type = 2 '
379 || ' AND replace(substrb(mtp.partner_name, 1, 50), '''''''', '''') = substr(t.' || p_dest_column_name || ',1,instr(t.' || p_dest_column_name ||','':'') - 1 ) '
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;
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;
397
398 msd_dem_common_utilities.log_debug ('Exiting TRADING PARTNER ZONE level');
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);
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
416
417 /*
418 * This procedure updates the level codes from descriptive to id format. The levels are -
419 * SITE, ACCOUNT, CUSTOMER, SUPPLIER, TRADING PARTNER ZONE
420 *
421 * This is an upgrade procedure hence proper backup of the Demantra Schema must be taken
422 * before running this procedure.
423 *
424 * This procedure must be run once for each instance for which data is available inside
425 * Demantra.
426 *
427 * This procedure creates a backup copy of the tables before updating them.
428 *
429 * The Demantra Application Server should be down when the procedure is run.
430 *
431 * Once the procedure has finished, bring up the Demantra Application Server and verify data.
432 *
433 * Run Data Load and verify data.
434 *
435 * This procedure should be run with MSD_DEM: Debug Mode set to Yes.
436 *
437 */
438 PROCEDURE UPGRADE_GEO_LEVEL_CODES (
439 errbuf OUT NOCOPY VARCHAR2,
440 retcode OUT NOCOPY VARCHAR2,
441 p_sr_instance_id IN NUMBER)
442 IS
443
444 x_sql VARCHAR2(4000) := NULL;
445 x_dem_schema VARCHAR2(100) := NULL;
446 x_is_present NUMBER := NULL;
447 x_bk_table_name VARCHAR2(100) := NULL;
448 x_ret_val BOOLEAN := NULL;
449
450 x_errbuf VARCHAR2(4000) := NULL;
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 */
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');
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;
478
479 IF (x_is_present <> 5)
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;
489
490 /* Verify that the profile MSD_DEM: Use new Site code format is set to No */
491 IF (nvl(fnd_profile.value('MSD_DEM_SITE_CODE_FORMAT'), 2) = 1)
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;
501
502 /*** Validation - END ***/
503
504
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
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
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
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
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
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
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
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
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
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
586
587 /*** Create Backup Tables - END ***/
588
589
590 /*** Update Level codes - START ***/
591
592 /* SITE */
593 convert_site_code (
594 x_errbuf,
595 x_retcode,
596 p_sr_instance_id,
597 'SITE',
598 x_dem_schema || '.T_EP_SITE',
599 'SITE',
600 2);
601 IF(x_retcode = -1)
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;
611
612
613 /* ACCOUNT */
614 convert_site_code (
615 x_errbuf,
616 x_retcode,
617 p_sr_instance_id,
618 'ACCOUNT',
619 x_dem_schema || '.T_EP_EBS_ACCOUNT',
620 'EBS_ACCOUNT',
621 2);
622 IF(x_retcode = -1)
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;
632
633
634 /* CUSTOMER */
635 convert_site_code (
636 x_errbuf,
637 x_retcode,
638 p_sr_instance_id,
639 'CUSTOMER',
640 x_dem_schema || '.T_EP_EBS_CUSTOMER',
641 'EBS_CUSTOMER',
642 2);
643 IF(x_retcode = -1)
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;
653
654
655 /* TRADING PARTNER ZONE */
656 convert_site_code (
657 x_errbuf,
658 x_retcode,
659 p_sr_instance_id,
660 'TRADING PARTNER ZONE',
661 x_dem_schema || '.T_EP_EBS_TP_ZONE',
662 'EBS_TP_ZONE',
663 2);
664 IF(x_retcode = -1)
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;
674
675
676 /* SUPPLIER */
677 convert_site_code (
678 x_errbuf,
679 x_retcode,
680 p_sr_instance_id,
681 'SUPPLIER',
682 x_dem_schema || '.T_EP_EBS_SUPPLIER',
683 'EBS_SUPPLIER',
684 2);
685 IF(x_retcode = -1)
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;
695
696
697 /*** Update Level codes - END ***/
698
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
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
717 END MSD_DEM_UPDATE_LEVEL_CODES;
718