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_COLLECT_LEVEL_TYPES dependencies on MSD_DEM_COMMON_UTILITIES

Line 35: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.check_data_model');

31: flag_dm_active NUMBER;
32:
33: BEGIN
34:
35: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.check_data_model');
36:
37: x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.DM_WIZ_DM_DEF '
38: || ' WHERE DATA_MODEL_TREE_ID = 15'
39: || ' AND IS_ACTIVE = 1' ;

Line 69: msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.2 ');

65: || ' AND MODEL_VERSION = 15';
66: EXECUTE IMMEDIATE x_sql INTO flag_720_ia ;
67:
68: IF (flag_720_ic = 1 and flag_720_id = 1 and flag_720_ia = 0 ) THEN
69: msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.2 ');
70: return 'OLD' ;
71: ELSE
72: msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.3 or might be customized. ');
73: return 'NEW';

Line 72: msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.3 or might be customized. ');

68: IF (flag_720_ic = 1 and flag_720_id = 1 and flag_720_ia = 0 ) THEN
69: msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.2 ');
70: return 'OLD' ;
71: ELSE
72: msd_dem_common_utilities.log_debug ('Data Model is equivalent to 7.3 or might be customized. ');
73: return 'NEW';
74: END IF;
75:
76: END IF;

Line 83: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.check_data_model');

79: EXCEPTION
80: WHEN OTHERS THEN
81: retcode := 1;
82: errbuf := substr(SQLERRM,1,150);
83: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.check_data_model');
84: msd_dem_common_utilities.log_message (errbuf);
85: RETURN 'NEW' ;
86:
87: END check_data_model;

Line 84: msd_dem_common_utilities.log_message (errbuf);

80: WHEN OTHERS THEN
81: retcode := 1;
82: errbuf := substr(SQLERRM,1,150);
83: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.check_data_model');
84: msd_dem_common_utilities.log_message (errbuf);
85: RETURN 'NEW' ;
86:
87: END check_data_model;
88:

Line 117: msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));

113: end loop;
114:
115: exception
116: when others then
117: msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
118: msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
119: retcode := -1;
120:
121: end;

Line 118: msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));

114:
115: exception
116: when others then
117: msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
118: msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
119: retcode := -1;
120:
121: end;
122:

Line 144: msd_dem_common_utilities.log_debug('In procedure populate_temporary_tables');

140: l_plan_type number;
141:
142: begin
143:
144: msd_dem_common_utilities.log_debug('In procedure populate_temporary_tables');
145: l_sales_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
146: if (l_sales_staging_table IS NULL)
147: then
148: retcode := -1;

Line 145: l_sales_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');

141:
142: begin
143:
144: msd_dem_common_utilities.log_debug('In procedure populate_temporary_tables');
145: l_sales_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
146: if (l_sales_staging_table IS NULL)
147: then
148: retcode := -1;
149: msd_dem_common_utilities.log_message ('Unable to find sales staging table name');

Line 149: msd_dem_common_utilities.log_message ('Unable to find sales staging table name');

145: l_sales_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
146: if (l_sales_staging_table IS NULL)
147: then
148: retcode := -1;
149: msd_dem_common_utilities.log_message ('Unable to find sales staging table name');
150: return;
151: end if;
152:
153: if p_plan_id <> -1 then

Line 193: msd_dem_common_utilities.log_debug('Insert statement for populating temporary table for ' ||

189: if p_plan_id <> -1 then
190: delete_duplicates(retcode);
191: end if;
192:
193: msd_dem_common_utilities.log_debug('Insert statement for populating temporary table for ' ||
194: v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10) ||
195: 'Instance: ' || p_instance_id || fnd_global.local_chr(10));
196:
197: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));

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

193: msd_dem_common_utilities.log_debug('Insert statement for populating temporary table for ' ||
194: v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10) ||
195: 'Instance: ' || p_instance_id || fnd_global.local_chr(10));
196:
197: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
198: execute immediate l_stmt;
199: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
200:
201: retcode := 0;

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

195: 'Instance: ' || p_instance_id || fnd_global.local_chr(10));
196:
197: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
198: execute immediate l_stmt;
199: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
200:
201: retcode := 0;
202:
203: exception

Line 207: msd_dem_common_utilities.log_message(errbuf);

203: exception
204: when others then
205: errbuf := substr(SQLERRM,1,150);
206: dbms_output.put_line(errbuf);
207: msd_dem_common_utilities.log_message(errbuf);
208: msd_dem_common_utilities.log_debug(errbuf);
209: retcode := -1;
210:
211: end;

Line 208: msd_dem_common_utilities.log_debug(errbuf);

204: when others then
205: errbuf := substr(SQLERRM,1,150);
206: dbms_output.put_line(errbuf);
207: msd_dem_common_utilities.log_message(errbuf);
208: msd_dem_common_utilities.log_debug(errbuf);
209: retcode := -1;
210:
211: end;
212:

Line 234: x_dem_version VARCHAR2(10) := msd_dem_common_utilities.get_demantra_version;

230: cursor get_instances is
231: select instance_id
232: from msc_apps_instances;
233:
234: x_dem_version VARCHAR2(10) := msd_dem_common_utilities.get_demantra_version;
235:
236: begin
237:
238: msd_dem_common_utilities.log_debug('In procedure populate_demantra_staging');

Line 238: msd_dem_common_utilities.log_debug('In procedure populate_demantra_staging');

234: x_dem_version VARCHAR2(10) := msd_dem_common_utilities.get_demantra_version;
235:
236: begin
237:
238: msd_dem_common_utilities.log_debug('In procedure populate_demantra_staging');
239:
240: if p_collect_level_type = 1 then
241: l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
242:

Line 241: l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');

237:
238: msd_dem_common_utilities.log_debug('In procedure populate_demantra_staging');
239:
240: if p_collect_level_type = 1 then
241: l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
242:
243: msd_dem_query_utilities.get_query(retcode, l_stmt, 'LOCATIONS', p_instance_id, l_staging_table);
244:
245: if retcode = -1 then

Line 251: l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');

247: end if;
248:
249: elsif p_collect_level_type = 2 then
250:
251: l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
252:
253: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
254: p_instance_id,
255: 'MSD_DEM_MASTER_ORG'));

Line 253: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (

249: elsif p_collect_level_type = 2 then
250:
251: l_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
252:
253: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
254: p_instance_id,
255: 'MSD_DEM_MASTER_ORG'));
256: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
257: p_instance_id,

Line 256: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (

252:
253: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
254: p_instance_id,
255: 'MSD_DEM_MASTER_ORG'));
256: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
257: p_instance_id,
258: 'MSD_DEM_CATEGORY_SET_NAME'));
259: IF ( l_master_org IS NULL)
260: THEN

Line 262: msd_dem_common_utilities.log_message ('Master organization not set.');

258: 'MSD_DEM_CATEGORY_SET_NAME'));
259: IF ( l_master_org IS NULL)
260: THEN
261: retcode := -1;
262: msd_dem_common_utilities.log_message ('Master organization not set.');
263: return;
264: END IF;
265:
266: -- bug 9341065 -- nallkuma

Line 282: msd_dem_common_utilities.log_debug('Insert statement for populating Demantra staging table for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));

278: end if;
279:
280: end if;
281:
282: msd_dem_common_utilities.log_debug('Insert statement for populating Demantra staging table for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));
283: IF (p_collect_level_type = 2)
284: THEN
285: msd_dem_common_utilities.log_debug('Bind Variables - ');
286: msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));

Line 285: msd_dem_common_utilities.log_debug('Bind Variables - ');

281:
282: msd_dem_common_utilities.log_debug('Insert statement for populating Demantra staging table for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));
283: IF (p_collect_level_type = 2)
284: THEN
285: msd_dem_common_utilities.log_debug('Bind Variables - ');
286: msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));
287: msd_dem_common_utilities.log_debug('Category Set Id - ' || to_char(l_category_set_id));
288: END IF;
289:

Line 286: msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));

282: msd_dem_common_utilities.log_debug('Insert statement for populating Demantra staging table for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));
283: IF (p_collect_level_type = 2)
284: THEN
285: msd_dem_common_utilities.log_debug('Bind Variables - ');
286: msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));
287: msd_dem_common_utilities.log_debug('Category Set Id - ' || to_char(l_category_set_id));
288: END IF;
289:
290: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));

Line 287: msd_dem_common_utilities.log_debug('Category Set Id - ' || to_char(l_category_set_id));

283: IF (p_collect_level_type = 2)
284: THEN
285: msd_dem_common_utilities.log_debug('Bind Variables - ');
286: msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));
287: msd_dem_common_utilities.log_debug('Category Set Id - ' || to_char(l_category_set_id));
288: END IF;
289:
290: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
291: IF (p_collect_level_type = 1)

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

286: msd_dem_common_utilities.log_debug('Master Organization Id - ' || to_char(l_master_org));
287: msd_dem_common_utilities.log_debug('Category Set Id - ' || to_char(l_category_set_id));
288: END IF;
289:
290: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
291: IF (p_collect_level_type = 1)
292: THEN
293: execute immediate l_stmt;
294: ELSIF (p_collect_level_type = 2)

Line 303: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (

299: l_master_string := 'decode(itt.sr_instance_id';
300: l_category_string := 'decode(item_master.sr_instance_id';
301:
302: for get_instances_rec in get_instances loop
303: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
304: get_instances_rec.instance_id,
305: 'MSD_DEM_MASTER_ORG'));
306: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
307: get_instances_rec.instance_id,

Line 306: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (

302: for get_instances_rec in get_instances loop
303: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
304: get_instances_rec.instance_id,
305: 'MSD_DEM_MASTER_ORG'));
306: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
307: get_instances_rec.instance_id,
308: 'MSD_DEM_CATEGORY_SET_NAME'));
309:
310: l_master_string := l_master_string || ', ' || get_instances_rec.instance_id || ', ''' || l_master_org || '''';

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

327: EXECUTE IMMEDIATE l_stmt;
328: end if;
329:
330: END IF;
331: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
332:
333: commit;
334:
335: retcode := 0;

Line 341: msd_dem_common_utilities.log_message(errbuf);

337: exception
338: when others then
339: errbuf := substr(SQLERRM,1,150);
340: dbms_output.put_line(errbuf);
341: msd_dem_common_utilities.log_message(errbuf);
342: msd_dem_common_utilities.log_debug(errbuf);
343: retcode := -1;
344:
345: end;

Line 342: msd_dem_common_utilities.log_debug(errbuf);

338: when others then
339: errbuf := substr(SQLERRM,1,150);
340: dbms_output.put_line(errbuf);
341: msd_dem_common_utilities.log_message(errbuf);
342: msd_dem_common_utilities.log_debug(errbuf);
343: retcode := -1;
344:
345: end;
346:

Line 384: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.populate_new_items');

380: x_inv_item_id_tab INV_ITEM_ID_TAB;
381:
382: BEGIN
383:
384: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.populate_new_items');
385:
386:
387: msd_dem_common_utilities.log_message (' Populate New Items ');
388: msd_dem_common_utilities.log_message (' --------------------');

Line 387: msd_dem_common_utilities.log_message (' Populate New Items ');

383:
384: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.populate_new_items');
385:
386:
387: msd_dem_common_utilities.log_message (' Populate New Items ');
388: msd_dem_common_utilities.log_message (' --------------------');
389:
390: /* Check if there are any yet to be processed NPIs */
391: OPEN c_check_new_items;

Line 388: msd_dem_common_utilities.log_message (' --------------------');

384: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_level_types.populate_new_items');
385:
386:
387: msd_dem_common_utilities.log_message (' Populate New Items ');
388: msd_dem_common_utilities.log_message (' --------------------');
389:
390: /* Check if there are any yet to be processed NPIs */
391: OPEN c_check_new_items;
392: FETCH c_check_new_items INTO x_new_items_present;

Line 397: msd_dem_common_utilities.log_message ('Found new items for processing');

393: CLOSE c_check_new_items;
394:
395: IF (x_new_items_present = 1)
396: THEN
397: msd_dem_common_utilities.log_message ('Found new items for processing');
398:
399: x_item_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
400: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
401: p_sr_instance_id,

Line 399: x_item_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');

395: IF (x_new_items_present = 1)
396: THEN
397: msd_dem_common_utilities.log_message ('Found new items for processing');
398:
399: x_item_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
400: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
401: p_sr_instance_id,
402: 'MSD_DEM_MASTER_ORG'));
403: x_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (

Line 400: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value (

396: THEN
397: msd_dem_common_utilities.log_message ('Found new items for processing');
398:
399: x_item_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
400: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
401: p_sr_instance_id,
402: 'MSD_DEM_MASTER_ORG'));
403: x_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
404: p_sr_instance_id,

Line 403: x_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (

399: x_item_staging_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
400: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
401: p_sr_instance_id,
402: 'MSD_DEM_MASTER_ORG'));
403: x_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
404: p_sr_instance_id,
405: 'MSD_DEM_CATEGORY_SET_NAME'));
406:
407: IF ( x_master_org IS NULL)

Line 411: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_level_types.populate_new_items');

407: IF ( x_master_org IS NULL)
408: THEN
409: retcode := 1;
410: errbuf := 'Master organization not set.';
411: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_level_types.populate_new_items');
412: msd_dem_common_utilities.log_message (errbuf);
413: RETURN;
414: END IF;
415:

Line 412: msd_dem_common_utilities.log_message (errbuf);

408: THEN
409: retcode := 1;
410: errbuf := 'Master organization not set.';
411: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_level_types.populate_new_items');
412: msd_dem_common_utilities.log_message (errbuf);
413: RETURN;
414: END IF;
415:
416: msd_dem_query_utilities.get_query(x_retcode, x_sql, 'NEW_ITEMS', p_sr_instance_id, x_item_staging_table);

Line 422: msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_level_types.populate_new_items');

418: IF (x_retcode = -1)
419: THEN
420: retcode := 1;
421: errbuf := 'Unable to get the query for populating new items into item staging table';
422: msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_level_types.populate_new_items');
423: msd_dem_common_utilities.log_message (errbuf);
424: RETURN;
425: END IF;
426:

Line 423: msd_dem_common_utilities.log_message (errbuf);

419: THEN
420: retcode := 1;
421: errbuf := 'Unable to get the query for populating new items into item staging table';
422: msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_level_types.populate_new_items');
423: msd_dem_common_utilities.log_message (errbuf);
424: RETURN;
425: END IF;
426:
427: msd_dem_common_utilities.log_debug ('Query - ');

Line 427: msd_dem_common_utilities.log_debug ('Query - ');

423: msd_dem_common_utilities.log_message (errbuf);
424: RETURN;
425: END IF;
426:
427: msd_dem_common_utilities.log_debug ('Query - ');
428: msd_dem_common_utilities.log_debug ('Bind Variables - ');
429: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
430: msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));

Line 428: msd_dem_common_utilities.log_debug ('Bind Variables - ');

424: RETURN;
425: END IF;
426:
427: msd_dem_common_utilities.log_debug ('Query - ');
428: msd_dem_common_utilities.log_debug ('Bind Variables - ');
429: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
430: msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
432:

Line 429: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));

425: END IF;
426:
427: msd_dem_common_utilities.log_debug ('Query - ');
428: msd_dem_common_utilities.log_debug ('Bind Variables - ');
429: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
430: msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
432:
433: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 430: msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));

426:
427: msd_dem_common_utilities.log_debug ('Query - ');
428: msd_dem_common_utilities.log_debug ('Bind Variables - ');
429: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
430: msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
432:
433: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
434: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id, x_master_org, x_category_set_id;

Line 431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));

427: msd_dem_common_utilities.log_debug ('Query - ');
428: msd_dem_common_utilities.log_debug ('Bind Variables - ');
429: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
430: msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
432:
433: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
434: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id, x_master_org, x_category_set_id;
435: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 433: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

429: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
430: msd_dem_common_utilities.log_debug ('Master Organization Id - ' || to_char(x_master_org));
431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
432:
433: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
434: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id, x_master_org, x_category_set_id;
435: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
436:
437: /* Set the process_flag */

Line 435: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

431: msd_dem_common_utilities.log_debug ('Category Set Id - ' || to_char(x_category_set_id));
432:
433: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
434: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id, x_master_org, x_category_set_id;
435: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
436:
437: /* Set the process_flag */
438: UPDATE msd_dem_new_items
439: SET process_flag = 1

Line 446: msd_dem_common_utilities.log_message ('No new items found for processing');

442:
443: COMMIT;
444:
445: ELSE
446: msd_dem_common_utilities.log_message ('No new items found for processing');
447: END IF;
448:
449: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_level_types.populate_new_items');
450:

Line 449: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_level_types.populate_new_items');

445: ELSE
446: msd_dem_common_utilities.log_message ('No new items found for processing');
447: END IF;
448:
449: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_level_types.populate_new_items');
450:
451: retcode := 0;
452:
453: EXCEPTION

Line 457: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.populate_new_items');

453: EXCEPTION
454: WHEN OTHERS THEN
455: retcode := 1;
456: errbuf := substr(SQLERRM,1,150);
457: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.populate_new_items');
458: msd_dem_common_utilities.log_message (errbuf);
459: RETURN;
460:
461: END POPULATE_NEW_ITEMS;

Line 458: msd_dem_common_utilities.log_message (errbuf);

454: WHEN OTHERS THEN
455: retcode := 1;
456: errbuf := substr(SQLERRM,1,150);
457: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_level_types.populate_new_items');
458: msd_dem_common_utilities.log_message (errbuf);
459: RETURN;
460:
461: END POPULATE_NEW_ITEMS;
462:

Line 474: msd_dem_common_utilities.log_debug('In procedure truncate_tables');

470: l_stmt varchar2(200);
471:
472: begin
473:
474: msd_dem_common_utilities.log_debug('In procedure truncate_tables');
475:
476: l_stmt := 'truncate table ';
477:
478: if p_collect_level_type = 1 then

Line 480: l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');

476: l_stmt := 'truncate table ';
477:
478: if p_collect_level_type = 1 then
479:
480: l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
481:
482: elsif p_collect_level_type = 2 then
483:
484: l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');

Line 484: l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');

480: l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE');
481:
482: elsif p_collect_level_type = 2 then
483:
484: l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
485:
486: end if;
487: if l_stmt = 'truncate table ' then
488: msd_dem_common_utilities.log_message('Staging table not found');

Line 488: msd_dem_common_utilities.log_message('Staging table not found');

484: l_stmt := l_stmt || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE');
485:
486: end if;
487: if l_stmt = 'truncate table ' then
488: msd_dem_common_utilities.log_message('Staging table not found');
489: retcode := -1;
490: return;
491: end if;
492:

Line 493: msd_dem_common_utilities.log_debug('Truncate Statement for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));

489: retcode := -1;
490: return;
491: end if;
492:
493: msd_dem_common_utilities.log_debug('Truncate Statement for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));
494:
495:
496: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
497: execute immediate l_stmt;

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

492:
493: msd_dem_common_utilities.log_debug('Truncate Statement for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));
494:
495:
496: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
497: execute immediate l_stmt;
498: execute immediate l_stmt || '_err'; --saravan Bug# 6357056
499: msd_dem_common_utilities.log_debug('Truncated'|| l_stmt || '_err'||'Table'); -- saravan
500: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));

Line 499: msd_dem_common_utilities.log_debug('Truncated'|| l_stmt || '_err'||'Table'); -- saravan

495:
496: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
497: execute immediate l_stmt;
498: execute immediate l_stmt || '_err'; --saravan Bug# 6357056
499: msd_dem_common_utilities.log_debug('Truncated'|| l_stmt || '_err'||'Table'); -- saravan
500: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
501:
502: retcode := 0;
503:

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

496: msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
497: execute immediate l_stmt;
498: execute immediate l_stmt || '_err'; --saravan Bug# 6357056
499: msd_dem_common_utilities.log_debug('Truncated'|| l_stmt || '_err'||'Table'); -- saravan
500: msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10));
501:
502: retcode := 0;
503:
504: exception

Line 507: msd_dem_common_utilities.log_message(errbuf);

503:
504: exception
505: when others then
506: errbuf := substr(SQLERRM,1,150);
507: msd_dem_common_utilities.log_message(errbuf);
508: msd_dem_common_utilities.log_debug(errbuf);
509: retcode := -1;
510:
511: end;

Line 508: msd_dem_common_utilities.log_debug(errbuf);

504: exception
505: when others then
506: errbuf := substr(SQLERRM,1,150);
507: msd_dem_common_utilities.log_message(errbuf);
508: msd_dem_common_utilities.log_debug(errbuf);
509: retcode := -1;
510:
511: end;
512:

Line 532: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_level_types.populate_organization_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

528: var_org_type_desc VARCHAR2(255) DEFAULT NULL;
529:
530: BEGIN
531:
532: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_level_types.populate_organization_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
533:
534: /* Check if the new columns for Org Type are available in T_SRC_LOC_TMPL */
535: SELECT count(*)
536: INTO var_count

Line 544: msd_dem_common_utilities.log_debug('Columns ORG_TYPE_CODE and ORG_TYPE_DESC not found in T_SRC_LOC_TMPL. Hence exiting without processing');

540: AND column_name in ('ORG_TYPE_CODE', 'ORG_TYPE_DESC');
541:
542: IF (var_count <> 2)
543: THEN
544: msd_dem_common_utilities.log_debug('Columns ORG_TYPE_CODE and ORG_TYPE_DESC not found in T_SRC_LOC_TMPL. Hence exiting without processing');
545: RETURN;
546: END IF;
547:
548: /* Check if the new table T_EP_ORG_TYPE is available */

Line 559: msd_dem_common_utilities.log_debug('Table T_EP_ORG_TYPE not found. Hence exiting without processing');

555: AND table_name = 'T_EP_ORG_TYPE';
556:
557: IF (var_count <> 1)
558: THEN
559: msd_dem_common_utilities.log_debug('Table T_EP_ORG_TYPE not found. Hence exiting without processing');
560: RETURN;
561: END IF;
562:
563: /* Get the org type code and description */

Line 582: msd_dem_common_utilities.log_debug('Error while trying to get org type code and desc. Hence exiting without processing');

578: var_org_type_desc := 'Demand Management';
579: END IF;
580:
581: WHEN OTHERS THEN
582: msd_dem_common_utilities.log_debug('Error while trying to get org type code and desc. Hence exiting without processing');
583: retcode := -1;
584: errbuf := substr(SQLERRM,1,150);
585: RETURN;
586:

Line 590: msd_dem_common_utilities.log_message ('Updating org type and code in location staging...');

586:
587: END;
588:
589: /* Update org type and code in t_src_loc_tmpl */
590: msd_dem_common_utilities.log_message ('Updating org type and code in location staging...');
591: EXECUTE IMMEDIATE 'UPDATE ' || var_schema || '.T_SRC_LOC_TMPL '
592: || ' SET org_type_code = ''' || var_org_type_code || ''', '
593: || ' org_type_desc = ''' || var_org_type_desc || ''' ';
594:

Line 597: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_organization_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

593: || ' org_type_desc = ''' || var_org_type_desc || ''' ';
594:
595: COMMIT;
596:
597: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_organization_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
598:
599: EXCEPTION
600: WHEN OTHERS THEN
601: retcode := -1;

Line 603: msd_dem_common_utilities.log_message ('msd_dem_collect_level_types.populate_organization_type - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

599: EXCEPTION
600: WHEN OTHERS THEN
601: retcode := -1;
602: errbuf := substr(SQLERRM,1,150);
603: msd_dem_common_utilities.log_message ('msd_dem_collect_level_types.populate_organization_type - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
604: msd_dem_common_utilities.log_message (errbuf);
605: RETURN;
606:
607: END POPULATE_ORGANIZATION_TYPE;

Line 604: msd_dem_common_utilities.log_message (errbuf);

600: WHEN OTHERS THEN
601: retcode := -1;
602: errbuf := substr(SQLERRM,1,150);
603: msd_dem_common_utilities.log_message ('msd_dem_collect_level_types.populate_organization_type - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
604: msd_dem_common_utilities.log_message (errbuf);
605: RETURN;
606:
607: END POPULATE_ORGANIZATION_TYPE;
608: /*

Line 630: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_level_types.populate_item_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

626:
627:
628: BEGIN
629:
630: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_level_types.populate_item_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
631:
632: x_sql := ' select count(nvl(1,0)) from all_tables where table_name = ''T_EP_I_ATT_10'' and owner = ''' || x_dem_schema || '''' ;
633: EXECUTE IMMEDIATE x_sql INTO x_count ;
634:

Line 636: msd_dem_common_utilities.log_debug('Warning: T_EP_I_ATT_10 table doesnot exist in demantra schema.' );

632: x_sql := ' select count(nvl(1,0)) from all_tables where table_name = ''T_EP_I_ATT_10'' and owner = ''' || x_dem_schema || '''' ;
633: EXECUTE IMMEDIATE x_sql INTO x_count ;
634:
635: IF (x_count = 0) THEN
636: msd_dem_common_utilities.log_debug('Warning: T_EP_I_ATT_10 table doesnot exist in demantra schema.' );
637: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...No Action Perfomed...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
638: retcode := 1;
639: errbuf := 'T_EP_I_ATT_10 table doesnot exist in demantra schema.';
640: return;

Line 637: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...No Action Perfomed...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

633: EXECUTE IMMEDIATE x_sql INTO x_count ;
634:
635: IF (x_count = 0) THEN
636: msd_dem_common_utilities.log_debug('Warning: T_EP_I_ATT_10 table doesnot exist in demantra schema.' );
637: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...No Action Perfomed...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
638: retcode := 1;
639: errbuf := 'T_EP_I_ATT_10 table doesnot exist in demantra schema.';
640: return;
641: END IF ;

Line 655: msd_dem_common_utilities.log_debug('Update sql for item_type level - ' || x_sql);

651:
652: EXECUTE IMMEDIATE x_sql ;
653: COMMIT;
654:
655: msd_dem_common_utilities.log_debug('Update sql for item_type level - ' || x_sql);
656: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
657:
658: EXCEPTION
659: WHEN OTHERS THEN

Line 656: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

652: EXECUTE IMMEDIATE x_sql ;
653: COMMIT;
654:
655: msd_dem_common_utilities.log_debug('Update sql for item_type level - ' || x_sql);
656: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_level_types.populate_item_type...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
657:
658: EXCEPTION
659: WHEN OTHERS THEN
660: errbuf := substr(SQLERRM,1,150);

Line 661: msd_dem_common_utilities.log_message(errbuf);

657:
658: EXCEPTION
659: WHEN OTHERS THEN
660: errbuf := substr(SQLERRM,1,150);
661: msd_dem_common_utilities.log_message(errbuf);
662: msd_dem_common_utilities.log_debug(errbuf);
663: RETCODE := -1;
664: END populate_item_type;
665:

Line 662: msd_dem_common_utilities.log_debug(errbuf);

658: EXCEPTION
659: WHEN OTHERS THEN
660: errbuf := substr(SQLERRM,1,150);
661: msd_dem_common_utilities.log_message(errbuf);
662: msd_dem_common_utilities.log_debug(errbuf);
663: RETCODE := -1;
664: END populate_item_type;
665:
666:

Line 680: l_schema_name := substr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE')

676: l_schema_name VARCHAR2(100);
677: begin
678:
679: -- BUG#8267074 nallkuma
680: l_schema_name := substr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE')
681: , 1
682: , instr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE'), '.')-1) ;
683:
684: IF (l_schema_name = 'MSD' ) then -- BUG#8267074 nallkuma

Line 682: , instr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE'), '.')-1) ;

678:
679: -- BUG#8267074 nallkuma
680: l_schema_name := substr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE')
681: , 1
682: , instr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE'), '.')-1) ;
683:
684: IF (l_schema_name = 'MSD' ) then -- BUG#8267074 nallkuma
685: msd_dem_common_utilities.log_message('NO ACTION performed related to levels since there is NO demantra schema found');
686: msd_dem_common_utilities.log_message('NO ACTION performed related to levels assuming its SRP collections');

Line 685: msd_dem_common_utilities.log_message('NO ACTION performed related to levels since there is NO demantra schema found');

681: , 1
682: , instr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE'), '.')-1) ;
683:
684: IF (l_schema_name = 'MSD' ) then -- BUG#8267074 nallkuma
685: msd_dem_common_utilities.log_message('NO ACTION performed related to levels since there is NO demantra schema found');
686: msd_dem_common_utilities.log_message('NO ACTION performed related to levels assuming its SRP collections');
687: return;
688: End if ;
689:

Line 686: msd_dem_common_utilities.log_message('NO ACTION performed related to levels assuming its SRP collections');

682: , instr(msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE'), '.')-1) ;
683:
684: IF (l_schema_name = 'MSD' ) then -- BUG#8267074 nallkuma
685: msd_dem_common_utilities.log_message('NO ACTION performed related to levels since there is NO demantra schema found');
686: msd_dem_common_utilities.log_message('NO ACTION performed related to levels assuming its SRP collections');
687: return;
688: End if ;
689:
690: select decode(p_collect_level_type, 1 , 'Locations', 2, 'Items') into v_collect_level_type from dual;

Line 692: msd_dem_common_utilities.log_message('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));

688: End if ;
689:
690: select decode(p_collect_level_type, 1 , 'Locations', 2, 'Items') into v_collect_level_type from dual;
691:
692: msd_dem_common_utilities.log_message('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
693: msd_dem_common_utilities.log_debug('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
694:
695: msd_dem_common_utilities.log_message('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
696: msd_dem_common_utilities.log_debug('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));

Line 693: msd_dem_common_utilities.log_debug('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));

689:
690: select decode(p_collect_level_type, 1 , 'Locations', 2, 'Items') into v_collect_level_type from dual;
691:
692: msd_dem_common_utilities.log_message('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
693: msd_dem_common_utilities.log_debug('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
694:
695: msd_dem_common_utilities.log_message('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
696: msd_dem_common_utilities.log_debug('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
697: truncate_tables(errbuf,retcode,p_collect_level_type);

Line 695: msd_dem_common_utilities.log_message('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));

691:
692: msd_dem_common_utilities.log_message('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
693: msd_dem_common_utilities.log_debug('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
694:
695: msd_dem_common_utilities.log_message('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
696: msd_dem_common_utilities.log_debug('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
697: truncate_tables(errbuf,retcode,p_collect_level_type);
698:
699: if retcode = -1 then

Line 696: msd_dem_common_utilities.log_debug('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));

692: msd_dem_common_utilities.log_message('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
693: msd_dem_common_utilities.log_debug('Collecting '|| v_collect_level_type || fnd_global.local_chr(10));
694:
695: msd_dem_common_utilities.log_message('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
696: msd_dem_common_utilities.log_debug('Step 1: Cleaning up staging tables for ' || v_collect_level_type || fnd_global.local_chr(10));
697: truncate_tables(errbuf,retcode,p_collect_level_type);
698:
699: if retcode = -1 then
700: return;

Line 703: msd_dem_common_utilities.log_message('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));

699: if retcode = -1 then
700: return;
701: end if;
702:
703: msd_dem_common_utilities.log_message('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
704: msd_dem_common_utilities.log_debug('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
705: populate_temporary_tables(errbuf,retcode,p_instance_id,p_collect_level_type, p_plan_id);
706:
707: if retcode = -1 then

Line 704: msd_dem_common_utilities.log_debug('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));

700: return;
701: end if;
702:
703: msd_dem_common_utilities.log_message('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
704: msd_dem_common_utilities.log_debug('Step 2: Populating Temporary Tables ' || v_collect_level_type || fnd_global.local_chr(10));
705: populate_temporary_tables(errbuf,retcode,p_instance_id,p_collect_level_type, p_plan_id);
706:
707: if retcode = -1 then
708: return;

Line 711: msd_dem_common_utilities.log_message('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));

707: if retcode = -1 then
708: return;
709: end if;
710:
711: msd_dem_common_utilities.log_message('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
712: msd_dem_common_utilities.log_debug('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
713: populate_demantra_staging(errbuf,retcode, p_instance_id, p_collect_level_type, p_plan_id);
714:
715: if retcode = -1 then

Line 712: msd_dem_common_utilities.log_debug('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));

708: return;
709: end if;
710:
711: msd_dem_common_utilities.log_message('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
712: msd_dem_common_utilities.log_debug('Step 3: Populating Demantra Staging Tables ' || v_collect_level_type || fnd_global.local_chr(10));
713: populate_demantra_staging(errbuf,retcode, p_instance_id, p_collect_level_type, p_plan_id);
714:
715: if retcode = -1 then
716: return;

Line 726: msd_dem_common_utilities.log_message ('Error in call to populate_organization_type');

722:
723: populate_organization_type (errbuf, retcode, p_for_spf);
724: IF (retcode = -1)
725: THEN
726: msd_dem_common_utilities.log_message ('Error in call to populate_organization_type');
727: RETURN;
728: END IF;
729:
730: END IF;

Line 736: msd_dem_common_utilities.log_message('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));

732: /* Call Custom Hook for Item/Location */
733: IF (p_collect_level_type = 2) /* ITEM */
734: THEN
735:
736: msd_dem_common_utilities.log_message('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));
737: msd_dem_common_utilities.log_debug('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));
738: populate_item_type(errbuf,retcode);
739:
740: IF retcode = -1 THEN

Line 737: msd_dem_common_utilities.log_debug('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));

733: IF (p_collect_level_type = 2) /* ITEM */
734: THEN
735:
736: msd_dem_common_utilities.log_message('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));
737: msd_dem_common_utilities.log_debug('Step 4: Updating Demantra Item Staging Table for item_type level' || fnd_global.local_chr(10));
738: populate_item_type(errbuf,retcode);
739:
740: IF retcode = -1 THEN
741: RETURN;

Line 742: msd_dem_common_utilities.log_message ('Error in Updating Demantra Item Staging Table for item_type level');

738: populate_item_type(errbuf,retcode);
739:
740: IF retcode = -1 THEN
741: RETURN;
742: msd_dem_common_utilities.log_message ('Error in Updating Demantra Item Staging Table for item_type level');
743: END IF;
744:
745: msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
746:

Line 745: msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

741: RETURN;
742: msd_dem_common_utilities.log_message ('Error in Updating Demantra Item Staging Table for item_type level');
743: END IF;
744:
745: msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
746:
747: msd_dem_custom_hooks.item_hook (
748: errbuf,
749: retcode);

Line 751: msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

747: msd_dem_custom_hooks.item_hook (
748: errbuf,
749: retcode);
750:
751: msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
752:
753: IF (retcode = -1)
754: THEN
755: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 755: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

751: msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.item_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
752:
753: IF (retcode = -1)
754: THEN
755: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
756: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.item_hook ');
757: RETURN;
758: END IF;
759:

Line 756: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.item_hook ');

752:
753: IF (retcode = -1)
754: THEN
755: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
756: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.item_hook ');
757: RETURN;
758: END IF;
759:
760: /* Analyze Item Staging Table */

Line 761: msd_dem_common_utilities.log_debug ('Begin Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

757: RETURN;
758: END IF;
759:
760: /* Analyze Item Staging Table */
761: msd_dem_common_utilities.log_debug ('Begin Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
762:
763: msd_dem_collect_history_data.analyze_table (
764: errbuf,
765: retcode,

Line 766: msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE'));

762:
763: msd_dem_collect_history_data.analyze_table (
764: errbuf,
765: retcode,
766: msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE'));
767:
768: msd_dem_common_utilities.log_debug ('End Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
769:
770: IF (retcode = 1)

Line 768: msd_dem_common_utilities.log_debug ('End Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

764: errbuf,
765: retcode,
766: msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','ITEM_STAGING_TABLE'));
767:
768: msd_dem_common_utilities.log_debug ('End Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
769:
770: IF (retcode = 1)
771: THEN
772: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 772: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

768: msd_dem_common_utilities.log_debug ('End Analyze item staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
769:
770: IF (retcode = 1)
771: THEN
772: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
773: msd_dem_common_utilities.log_message ('Error while analyzing item staging table. ');
774: END IF;
775:
776: ELSIF (p_collect_level_type = 1) /* LOCATION */

Line 773: msd_dem_common_utilities.log_message ('Error while analyzing item staging table. ');

769:
770: IF (retcode = 1)
771: THEN
772: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
773: msd_dem_common_utilities.log_message ('Error while analyzing item staging table. ');
774: END IF;
775:
776: ELSIF (p_collect_level_type = 1) /* LOCATION */
777: THEN

Line 779: msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.location_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

775:
776: ELSIF (p_collect_level_type = 1) /* LOCATION */
777: THEN
778:
779: msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.location_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
780:
781: msd_dem_custom_hooks.location_hook (
782: errbuf,
783: retcode);

Line 785: msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.location_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

781: msd_dem_custom_hooks.location_hook (
782: errbuf,
783: retcode);
784:
785: msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.location_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
786:
787: IF (retcode = -1)
788: THEN
789: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 789: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

785: msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.location_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
786:
787: IF (retcode = -1)
788: THEN
789: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
790: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.location_hook ');
791: RETURN;
792: END IF;
793:

Line 790: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.location_hook ');

786:
787: IF (retcode = -1)
788: THEN
789: msd_dem_common_utilities.log_message ('Error: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
790: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.location_hook ');
791: RETURN;
792: END IF;
793:
794: /* Analyze Location Staging Table */

Line 795: msd_dem_common_utilities.log_debug ('Begin Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

791: RETURN;
792: END IF;
793:
794: /* Analyze Location Staging Table */
795: msd_dem_common_utilities.log_debug ('Begin Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
796:
797: msd_dem_collect_history_data.analyze_table (
798: errbuf,
799: retcode,

Line 800: msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE'));

796:
797: msd_dem_collect_history_data.analyze_table (
798: errbuf,
799: retcode,
800: msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE'));
801:
802: msd_dem_common_utilities.log_debug ('End Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
803:
804: IF (retcode = 1)

Line 802: msd_dem_common_utilities.log_debug ('End Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

798: errbuf,
799: retcode,
800: msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','LOCATION_STAGING_TABLE'));
801:
802: msd_dem_common_utilities.log_debug ('End Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
803:
804: IF (retcode = 1)
805: THEN
806: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 806: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

802: msd_dem_common_utilities.log_debug ('End Analyze location staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
803:
804: IF (retcode = 1)
805: THEN
806: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807: msd_dem_common_utilities.log_message ('Error while analyzing location staging table. ');
808: END IF;
809:
810: END IF;

Line 807: msd_dem_common_utilities.log_message ('Error while analyzing location staging table. ');

803:
804: IF (retcode = 1)
805: THEN
806: msd_dem_common_utilities.log_message ('Warning: msd_dem_collect_level_types.collect_levels - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807: msd_dem_common_utilities.log_message ('Error while analyzing location staging table. ');
808: END IF;
809:
810: END IF;
811:

Line 815: msd_dem_common_utilities.log_message(errbuf);

811:
812: exception
813: when others then
814: errbuf := substr(SQLERRM,1,150);
815: msd_dem_common_utilities.log_message(errbuf);
816: msd_dem_common_utilities.log_debug(errbuf);
817: retcode := -1;
818:
819: end collect_levels;

Line 816: msd_dem_common_utilities.log_debug(errbuf);

812: exception
813: when others then
814: errbuf := substr(SQLERRM,1,150);
815: msd_dem_common_utilities.log_message(errbuf);
816: msd_dem_common_utilities.log_debug(errbuf);
817: retcode := -1;
818:
819: end collect_levels;
820: