[Home] [Help]
80: OR p_exclude_order_types IS NOT NULL)
81: THEN
82: retcode := 1;
83: errbuf := 'The parameters Include Order Types and Exclude Order Types are ignored, if Collect All Order Types is Yes.';
84: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
85: msd_dem_common_utilities.log_message (errbuf);
86: END IF;
87:
88: p_order_type_flag := C_ALL;
81: THEN
82: retcode := 1;
83: errbuf := 'The parameters Include Order Types and Exclude Order Types are ignored, if Collect All Order Types is Yes.';
84: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
85: msd_dem_common_utilities.log_message (errbuf);
86: END IF;
87:
88: p_order_type_flag := C_ALL;
89: p_order_type_ids := '';
114: IF (l_order_type_table.COUNT = 0)
115: THEN
116: retcode := -1;
117: errbuf := 'No order types found in the source';
118: msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
119: msd_dem_common_utilities.log_message (errbuf);
120: RETURN -1;
121: END IF;
122:
115: THEN
116: retcode := -1;
117: errbuf := 'No order types found in the source';
118: msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
119: msd_dem_common_utilities.log_message (errbuf);
120: RETURN -1;
121: END IF;
122:
123: IF (p_collect_all_order_types = G_NO)
127: AND p_exclude_order_types IS NULL)
128: THEN
129: retcode := -1;
130: errbuf := 'Exactly one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
131: msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
132: msd_dem_common_utilities.log_message (errbuf);
133: RETURN -1;
134: ELSIF ( p_include_order_types IS NOT NULL
135: AND p_exclude_order_types IS NOT NULL)
128: THEN
129: retcode := -1;
130: errbuf := 'Exactly one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
131: msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
132: msd_dem_common_utilities.log_message (errbuf);
133: RETURN -1;
134: ELSIF ( p_include_order_types IS NOT NULL
135: AND p_exclude_order_types IS NOT NULL)
136: THEN
135: AND p_exclude_order_types IS NOT NULL)
136: THEN
137: retcode := -1;
138: errbuf := 'Only one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
139: msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
140: msd_dem_common_utilities.log_message (errbuf);
141: RETURN -1;
142: ELSIF (p_include_order_types IS NOT NULL)
143: THEN
136: THEN
137: retcode := -1;
138: errbuf := 'Only one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
139: msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
140: msd_dem_common_utilities.log_message (errbuf);
141: RETURN -1;
142: ELSIF (p_include_order_types IS NOT NULL)
143: THEN
144: l_order_type_flag := C_INCLUDE;
220: l_start := l_position + 1;
221:
222: END LOOP;
223:
224: msd_dem_common_utilities.log_message (' Order Types');
225: msd_dem_common_utilities.log_message (' -------------');
226:
227: msd_dem_common_utilities.log_message (' Valid Order Types');
228: msd_dem_common_utilities.log_message (' -------------------');
221:
222: END LOOP;
223:
224: msd_dem_common_utilities.log_message (' Order Types');
225: msd_dem_common_utilities.log_message (' -------------');
226:
227: msd_dem_common_utilities.log_message (' Valid Order Types');
228: msd_dem_common_utilities.log_message (' -------------------');
229:
223:
224: msd_dem_common_utilities.log_message (' Order Types');
225: msd_dem_common_utilities.log_message (' -------------');
226:
227: msd_dem_common_utilities.log_message (' Valid Order Types');
228: msd_dem_common_utilities.log_message (' -------------------');
229:
230: IF (l_valid_count <> 0)
231: THEN
224: msd_dem_common_utilities.log_message (' Order Types');
225: msd_dem_common_utilities.log_message (' -------------');
226:
227: msd_dem_common_utilities.log_message (' Valid Order Types');
228: msd_dem_common_utilities.log_message (' -------------------');
229:
230: IF (l_valid_count <> 0)
231: THEN
232: FOR i in l_valid_order_type_table.FIRST..l_valid_order_type_table.LAST
230: IF (l_valid_count <> 0)
231: THEN
232: FOR i in l_valid_order_type_table.FIRST..l_valid_order_type_table.LAST
233: LOOP
234: msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_valid_order_type_table(i));
235: END LOOP;
236: ELSE
237: msd_dem_common_utilities.log_message ('No valid order types found in user input');
238: END IF;
233: LOOP
234: msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_valid_order_type_table(i));
235: END LOOP;
236: ELSE
237: msd_dem_common_utilities.log_message ('No valid order types found in user input');
238: END IF;
239:
240: msd_dem_common_utilities.log_message (' ');
241: msd_dem_common_utilities.log_message (' Invalid Order Types');
236: ELSE
237: msd_dem_common_utilities.log_message ('No valid order types found in user input');
238: END IF;
239:
240: msd_dem_common_utilities.log_message (' ');
241: msd_dem_common_utilities.log_message (' Invalid Order Types');
242: msd_dem_common_utilities.log_message (' ---------------------');
243:
244: IF (l_invalid_count <> 0)
237: msd_dem_common_utilities.log_message ('No valid order types found in user input');
238: END IF;
239:
240: msd_dem_common_utilities.log_message (' ');
241: msd_dem_common_utilities.log_message (' Invalid Order Types');
242: msd_dem_common_utilities.log_message (' ---------------------');
243:
244: IF (l_invalid_count <> 0)
245: THEN
238: END IF;
239:
240: msd_dem_common_utilities.log_message (' ');
241: msd_dem_common_utilities.log_message (' Invalid Order Types');
242: msd_dem_common_utilities.log_message (' ---------------------');
243:
244: IF (l_invalid_count <> 0)
245: THEN
246: FOR i in l_invalid_order_type_table.FIRST..l_invalid_order_type_table.LAST
244: IF (l_invalid_count <> 0)
245: THEN
246: FOR i in l_invalid_order_type_table.FIRST..l_invalid_order_type_table.LAST
247: LOOP
248: msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_invalid_order_type_table(i));
249: END LOOP;
250: END IF;
251:
252: IF (l_valid_count = 0)
252: IF (l_valid_count = 0)
253: THEN
254: retcode := -1;
255: errbuf := 'No valid order types found in user input';
256: msd_dem_common_utilities.log_message ('Error(4): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
257: RETURN -1;
258: END IF;
259:
260: IF (l_invalid_count <> 0)
260: IF (l_invalid_count <> 0)
261: THEN
262: retcode := 1;
263: errbuf := 'Invalid order types found in user input';
264: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
265: END IF;
266:
267: END IF;
268:
338: l_gmp_custom_view_name varchar2(100) := NULL;
339:
340: BEGIN
341:
342: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
343:
344: OPEN c_get_series_info;
345: FETCH c_get_series_info INTO l_identifier, l_STG_SERIES_COL_NAME, l_MSD_SR_ITEM_PK_COL, l_MSD_SOURCE_DATE_COL,l_GMP_SR_ITEM_PK_COL, l_GMP_SOURCE_DATE_COL, l_custom_view_name, l_gmp_custom_view_name;
346: CLOSE c_get_series_info;
348: IF (l_identifier IS NULL)
349: THEN
350: retcode := -1;
351: errbuf := 'Unable to get the query identifier.';
352: msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
353: msd_dem_common_utilities.log_message (errbuf);
354: RETURN;
355: END IF;
356:
349: THEN
350: retcode := -1;
351: errbuf := 'Unable to get the query identifier.';
352: msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
353: msd_dem_common_utilities.log_message (errbuf);
354: RETURN;
355: END IF;
356:
357: /* Check if custom view for Discrete */
375: IF ( p_instance_type <> 2
376: OR p_apps_ver = 4)
377: THEN
378:
379: msd_dem_common_utilities.log_debug ('Begin collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
380:
381: x_add_where_clause := ' 1 = 1 ';
382:
383: /* If p_collect_iso = No, then include an additional condition to filter out Internal Sales Orders */
406: || '$C_SOURCE_DATE#' || nvl(substr(l_msd_source_date_col, 0, instr(upper(l_msd_source_date_col), 'SDATE')-1), to_date('01/01/1000', 'DD/MM/YYYY')) || ' SDATE'
407: || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
408: || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
409: || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
410: || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
411:
412: IF (g_collection_method <> 1) THEN
413: x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' WHERE SDATE BETWEEN '
414: || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
430: || '$C_SOURCE_DATE#' || SUBSTR(L_MSD_SOURCE_DATE_COL, 1, instr(L_MSD_SOURCE_DATE_COL, 'SDATE')-1) ||' PDATE'
431: || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
432: || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
433: || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
434: || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
435:
436: IF (g_collection_method <> 1) THEN
437: x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN '
438: || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
461: OR x_large_sql IS NULL)
462: THEN
463: retcode := -1;
464: errbuf := 'Unable to get the query.';
465: msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
466: msd_dem_common_utilities.log_message (errbuf);
467: RETURN;
468: END IF;
469:
462: THEN
463: retcode := -1;
464: errbuf := 'Unable to get the query.';
465: msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
466: msd_dem_common_utilities.log_message (errbuf);
467: RETURN;
468: END IF;
469:
470:
467: RETURN;
468: END IF;
469:
470:
471: msd_dem_common_utilities.log_debug ('Query - ');
472: msd_dem_common_utilities.log_debug (x_large_sql);
473:
474: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
475:
468: END IF;
469:
470:
471: msd_dem_common_utilities.log_debug ('Query - ');
472: msd_dem_common_utilities.log_debug (x_large_sql);
473:
474: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
475:
476: BEGIN
470:
471: msd_dem_common_utilities.log_debug ('Query - ');
472: msd_dem_common_utilities.log_debug (x_large_sql);
473:
474: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
475:
476: BEGIN
477: /* Upsert history data into sales staging table */
478: EXECUTE IMMEDIATE x_large_sql;
480: EXCEPTION
481: WHEN OTHERS THEN
482: retcode := -1 ;
483: errbuf := substr(SQLERRM,1,150);
484: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
485: msd_dem_common_utilities.log_message (errbuf);
486: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
487: RETURN;
488: END;
481: WHEN OTHERS THEN
482: retcode := -1 ;
483: errbuf := substr(SQLERRM,1,150);
484: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
485: msd_dem_common_utilities.log_message (errbuf);
486: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
487: RETURN;
488: END;
489:
482: retcode := -1 ;
483: errbuf := substr(SQLERRM,1,150);
484: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
485: msd_dem_common_utilities.log_message (errbuf);
486: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
487: RETURN;
488: END;
489:
490: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
486: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
487: RETURN;
488: END;
489:
490: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
491:
492: msd_dem_common_utilities.log_debug ('End collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
493:
494: END IF;
488: END;
489:
490: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
491:
492: msd_dem_common_utilities.log_debug ('End collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
493:
494: END IF;
495:
496: x_large_sql := NULL;
500: IF ( p_instance_type IN (2, 4)
501: AND p_apps_ver = 3)
502: THEN
503:
504: msd_dem_common_utilities.log_debug ('Begin collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
505:
506: x_pquery_identifier := replace(l_identifier , 'MSD','GMP') ;
507:
508: x_add_where_clause := ' 1 = 1 ';
573: OR x_large_sql IS NULL)
574: THEN
575: retcode := -1;
576: errbuf := 'Unable to get the query.';
577: msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
578: msd_dem_common_utilities.log_message (errbuf);
579: RETURN;
580: END IF;
581:
574: THEN
575: retcode := -1;
576: errbuf := 'Unable to get the query.';
577: msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
578: msd_dem_common_utilities.log_message (errbuf);
579: RETURN;
580: END IF;
581:
582:
579: RETURN;
580: END IF;
581:
582:
583: msd_dem_common_utilities.log_debug ('Query - ');
584: msd_dem_common_utilities.log_debug (x_large_sql);
585:
586: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
587:
580: END IF;
581:
582:
583: msd_dem_common_utilities.log_debug ('Query - ');
584: msd_dem_common_utilities.log_debug (x_large_sql);
585:
586: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
587:
588: BEGIN
582:
583: msd_dem_common_utilities.log_debug ('Query - ');
584: msd_dem_common_utilities.log_debug (x_large_sql);
585:
586: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
587:
588: BEGIN
589: /* Upsert history data into sales staging table */
590: EXECUTE IMMEDIATE x_large_sql;
592: EXCEPTION
593: WHEN OTHERS THEN
594: retcode := -1 ;
595: errbuf := substr(SQLERRM,1,150);
596: msd_dem_common_utilities.log_message ('Exception(2): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
597: msd_dem_common_utilities.log_message (errbuf);
598: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
599: RETURN;
600: END;
593: WHEN OTHERS THEN
594: retcode := -1 ;
595: errbuf := substr(SQLERRM,1,150);
596: msd_dem_common_utilities.log_message ('Exception(2): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
597: msd_dem_common_utilities.log_message (errbuf);
598: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
599: RETURN;
600: END;
601:
594: retcode := -1 ;
595: errbuf := substr(SQLERRM,1,150);
596: msd_dem_common_utilities.log_message ('Exception(2): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
597: msd_dem_common_utilities.log_message (errbuf);
598: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
599: RETURN;
600: END;
601:
602: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
598: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
599: RETURN;
600: END;
601:
602: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
603: msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
604:
605: END IF;
606:
599: RETURN;
600: END;
601:
602: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
603: msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
604:
605: END IF;
606:
607: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
603: msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
604:
605: END IF;
606:
607: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
608:
609: EXCEPTION
610: WHEN OTHERS THEN
611: retcode := -1 ;
609: EXCEPTION
610: WHEN OTHERS THEN
611: retcode := -1 ;
612: errbuf := substr(SQLERRM,1,150);
613: msd_dem_common_utilities.log_message ('Exception(3): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
614: msd_dem_common_utilities.log_message (errbuf);
615: RETURN;
616:
617: END COLLECT_SERIES_DATA;
610: WHEN OTHERS THEN
611: retcode := -1 ;
612: errbuf := substr(SQLERRM,1,150);
613: msd_dem_common_utilities.log_message ('Exception(3): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
614: msd_dem_common_utilities.log_message (errbuf);
615: RETURN;
616:
617: END COLLECT_SERIES_DATA;
618:
645: x_new_items_present NUMBER := NULL;
646: x_sql VARCHAR2(32000) := NULL;
647: BEGIN
648:
649: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
650:
651: /* Check if there are any yet to be processed NPIs */
652: OPEN c_check_new_items;
653: FETCH c_check_new_items INTO x_new_items_present;
654: CLOSE c_check_new_items;
655:
656: IF (x_new_items_present = 1)
657: THEN
658: msd_dem_common_utilities.log_message ('Found new items for processing');
659:
660: msd_dem_query_utilities.get_query(
661: x_retcode,
662: x_sql,
667: IF (x_retcode = -1)
668: THEN
669: retcode := 1;
670: errbuf := 'Unable to get the query for inserting dummy rows for new items into sales staging table';
671: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.insert_dummy_rows');
672: msd_dem_common_utilities.log_message (errbuf);
673: RETURN;
674: END IF;
675:
668: THEN
669: retcode := 1;
670: errbuf := 'Unable to get the query for inserting dummy rows for new items into sales staging table';
671: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.insert_dummy_rows');
672: msd_dem_common_utilities.log_message (errbuf);
673: RETURN;
674: END IF;
675:
676: msd_dem_common_utilities.log_debug ('Query - ');
672: msd_dem_common_utilities.log_message (errbuf);
673: RETURN;
674: END IF;
675:
676: msd_dem_common_utilities.log_debug ('Query - ');
677: msd_dem_common_utilities.log_debug ('Bind Variables - ');
678: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
679: msd_dem_common_utilities.log_debug (x_sql);
680:
673: RETURN;
674: END IF;
675:
676: msd_dem_common_utilities.log_debug ('Query - ');
677: msd_dem_common_utilities.log_debug ('Bind Variables - ');
678: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
679: msd_dem_common_utilities.log_debug (x_sql);
680:
681: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
674: END IF;
675:
676: msd_dem_common_utilities.log_debug ('Query - ');
677: msd_dem_common_utilities.log_debug ('Bind Variables - ');
678: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
679: msd_dem_common_utilities.log_debug (x_sql);
680:
681: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id;
675:
676: msd_dem_common_utilities.log_debug ('Query - ');
677: msd_dem_common_utilities.log_debug ('Bind Variables - ');
678: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
679: msd_dem_common_utilities.log_debug (x_sql);
680:
681: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id;
683: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
677: msd_dem_common_utilities.log_debug ('Bind Variables - ');
678: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
679: msd_dem_common_utilities.log_debug (x_sql);
680:
681: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id;
683: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
684:
685: /* Set the process_flag */
679: msd_dem_common_utilities.log_debug (x_sql);
680:
681: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id;
683: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
684:
685: /* Set the process_flag */
686: UPDATE msd_dem_new_items
687: SET process_flag = 1
691: COMMIT;
692:
693: END IF;
694:
695: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
696:
697: EXCEPTION
698: WHEN OTHERS THEN
699: retcode := 1 ;
697: EXCEPTION
698: WHEN OTHERS THEN
699: retcode := 1 ;
700: errbuf := substr(SQLERRM,1,150);
701: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
702: msd_dem_common_utilities.log_message (errbuf);
703: RETURN;
704: END INSERT_DUMMY_ROWS;
705:
698: WHEN OTHERS THEN
699: retcode := 1 ;
700: errbuf := substr(SQLERRM,1,150);
701: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
702: msd_dem_common_utilities.log_message (errbuf);
703: RETURN;
704: END INSERT_DUMMY_ROWS;
705:
706:
721: x_pos NUMBER := NULL;
722:
723: BEGIN
724:
725: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
726:
727: x_pos := instr( p_table_name, '.', 1, 1);
728:
729: IF (x_pos = 0)
734: x_schema_name := substr (p_table_name, 1, x_pos - 1);
735: x_table_name := substr (p_table_name, x_pos +1);
736: END IF;
737:
738: msd_dem_common_utilities.log_message ('Analyzing Table - ' || x_schema_name || '.' || x_table_name);
739: fnd_stats.gather_table_stats(x_schema_name, x_table_name, 10, 4);
740:
741: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
742:
737:
738: msd_dem_common_utilities.log_message ('Analyzing Table - ' || x_schema_name || '.' || x_table_name);
739: fnd_stats.gather_table_stats(x_schema_name, x_table_name, 10, 4);
740:
741: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
742:
743:
744: EXCEPTION
745: WHEN OTHERS THEN
744: EXCEPTION
745: WHEN OTHERS THEN
746: retcode := 1 ;
747: errbuf := substr(SQLERRM,1,150);
748: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
749: msd_dem_common_utilities.log_message (errbuf);
750: RETURN;
751: END ANALYZE_TABLE;
752:
745: WHEN OTHERS THEN
746: retcode := 1 ;
747: errbuf := substr(SQLERRM,1,150);
748: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
749: msd_dem_common_utilities.log_message (errbuf);
750: RETURN;
751: END ANALYZE_TABLE;
752:
753: /*** PUBLIC PROCEDURES ***/
831: ORDER BY created desc;
832:
833: BEGIN
834:
835: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
836:
837: /* Get the db link to the source instance */
838: msd_dem_common_utilities.get_dblink (
839: x_errbuf,
834:
835: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
836:
837: /* Get the db link to the source instance */
838: msd_dem_common_utilities.get_dblink (
839: x_errbuf,
840: x_retcode,
841: p_sr_instance_id,
842: g_dblink);
844: IF (x_retcode = '-1')
845: THEN
846: retcode := -1;
847: errbuf := x_errbuf;
848: msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
849: RETURN;
850: END IF;
851:
852: g_collection_method := p_collection_method;
852: g_collection_method := p_collection_method;
853:
854: /* VALIDATION OF INPUT PARAMETERS - BEGIN */
855:
856: msd_dem_common_utilities.log_debug ('Begin validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
857:
858: /* Atleast one parameter must be selected */
859: IF ( p_bh_bi_bd = G_NO
860: AND p_bh_bi_rd = G_NO
866: AND p_sh_ri_rd = G_NO)
867: THEN
868: retcode := -1;
869: errbuf := 'No series selected for collection';
870: msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
871: msd_dem_common_utilities.log_message (errbuf);
872: RETURN;
873: END IF;
874:
867: THEN
868: retcode := -1;
869: errbuf := 'No series selected for collection';
870: msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
871: msd_dem_common_utilities.log_message (errbuf);
872: RETURN;
873: END IF;
874:
875:
880: OR p_collection_window IS NOT NULL))
881: THEN
882: x_retcode := 1;
883: x_errbuf := 'Date Range filters are ignored in ''Refresh'' collections';
884: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
885: msd_dem_common_utilities.log_message (x_errbuf);
886: END IF;
887:
888:
881: THEN
882: x_retcode := 1;
883: x_errbuf := 'Date Range filters are ignored in ''Refresh'' collections';
884: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
885: msd_dem_common_utilities.log_message (x_errbuf);
886: END IF;
887:
888:
889: /* Show Warning if collection method is net change, date range type is Rolling and from date and to date are specified */
893: OR p_to_date IS NOT NULL))
894: THEN
895: x_retcode := 1;
896: x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date range type is selected.';
897: msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
898: msd_dem_common_utilities.log_message (x_errbuf);
899: END IF;
900:
901:
894: THEN
895: x_retcode := 1;
896: x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date range type is selected.';
897: msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
898: msd_dem_common_utilities.log_message (x_errbuf);
899: END IF;
900:
901:
902: /* Show Warning if collection method is net change, date range type is Absolute and history collection window is specified */
905: AND p_collection_window IS NOT NULL)
906: THEN
907: x_retcode := 1;
908: x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
909: msd_dem_common_utilities.log_message ('Warning(3): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
910: msd_dem_common_utilities.log_message (x_errbuf);
911: END IF;
912:
913:
906: THEN
907: x_retcode := 1;
908: x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
909: msd_dem_common_utilities.log_message ('Warning(3): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
910: msd_dem_common_utilities.log_message (x_errbuf);
911: END IF;
912:
913:
914: /* Error if collection method is net change, date range type is Rolling and history collection window is not specified */
917: AND p_collection_window IS NULL)
918: THEN
919: retcode := -1;
920: errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
921: msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
922: msd_dem_common_utilities.log_message (errbuf);
923: RETURN;
924: END IF;
925:
918: THEN
919: retcode := -1;
920: errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
921: msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
922: msd_dem_common_utilities.log_message (errbuf);
923: RETURN;
924: END IF;
925:
926:
931: OR p_to_date IS NULL))
932: THEN
933: retcode := -1;
934: errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
935: msd_dem_common_utilities.log_message ('Error(4): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
936: msd_dem_common_utilities.log_message (errbuf);
937: RETURN;
938: END IF;
939:
932: THEN
933: retcode := -1;
934: errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
935: msd_dem_common_utilities.log_message ('Error(4): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
936: msd_dem_common_utilities.log_message (errbuf);
937: RETURN;
938: END IF;
939:
940: /* Validate the order types specified by the user */
949: IF (x_retcode1 = -1)
950: THEN
951: retcode := -1;
952: errbuf := 'No valid order types found';
953: msd_dem_common_utilities.log_message ('Error(5): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
954: msd_dem_common_utilities.log_message (errbuf);
955: RETURN;
956: ELSIF (x_invalid_count > 0)
957: THEN
950: THEN
951: retcode := -1;
952: errbuf := 'No valid order types found';
953: msd_dem_common_utilities.log_message ('Error(5): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
954: msd_dem_common_utilities.log_message (errbuf);
955: RETURN;
956: ELSIF (x_invalid_count > 0)
957: THEN
958: x_retcode := 1;
956: ELSIF (x_invalid_count > 0)
957: THEN
958: x_retcode := 1;
959: x_errbuf := 'Invalid order types found';
960: msd_dem_common_utilities.log_message ('Warning(4): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
961: msd_dem_common_utilities.log_message (x_errbuf);
962: ELSIF (x_retcode1 = 1)
963: THEN
964: x_retcode := 1;
957: THEN
958: x_retcode := 1;
959: x_errbuf := 'Invalid order types found';
960: msd_dem_common_utilities.log_message ('Warning(4): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
961: msd_dem_common_utilities.log_message (x_errbuf);
962: ELSIF (x_retcode1 = 1)
963: THEN
964: x_retcode := 1;
965: x_errbuf := x_errbuf1;
962: ELSIF (x_retcode1 = 1)
963: THEN
964: x_retcode := 1;
965: x_errbuf := x_errbuf1;
966: msd_dem_common_utilities.log_message ('Warning(5): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
967: END IF;
968:
969: msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
970:
965: x_errbuf := x_errbuf1;
966: msd_dem_common_utilities.log_message ('Warning(5): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
967: END IF;
968:
969: msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
970:
971: /* VALIDATION OF INPUT PARAMETERS - END */
972:
973:
972:
973:
974: /* Get the start date and end dates for collection */
975:
976: msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
977:
978: IF (p_collection_method = 1) /* Refresh*/
979: THEN
980: x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
1001: IF (x_from_date > x_to_date)
1002: THEN
1003: retcode := -1;
1004: errbuf := 'From Date should not be greater than To Date.';
1005: msd_dem_common_utilities.log_message ('Error(6): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1006: msd_dem_common_utilities.log_message (errbuf);
1007: RETURN;
1008: END IF;
1009:
1002: THEN
1003: retcode := -1;
1004: errbuf := 'From Date should not be greater than To Date.';
1005: msd_dem_common_utilities.log_message ('Error(6): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1006: msd_dem_common_utilities.log_message (errbuf);
1007: RETURN;
1008: END IF;
1009:
1010: ELSE /* Rolling */
1012: IF (p_collection_window < 0)
1013: THEN
1014: retcode := -1;
1015: errbuf := 'History Collection Window must be a positive number.';
1016: msd_dem_common_utilities.log_message ('Error(7): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1017: msd_dem_common_utilities.log_message (errbuf);
1018: RETURN;
1019: ELSE
1020: x_to_date := trunc(sysdate);
1013: THEN
1014: retcode := -1;
1015: errbuf := 'History Collection Window must be a positive number.';
1016: msd_dem_common_utilities.log_message ('Error(7): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1017: msd_dem_common_utilities.log_message (errbuf);
1018: RETURN;
1019: ELSE
1020: x_to_date := trunc(sysdate);
1021: x_from_date := x_to_date - p_collection_window + 1;
1022: END IF;
1023: END IF;
1024: END IF;
1025:
1026: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1027: msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1028:
1029: /* Get the instance info */
1030: msd_dem_common_utilities.get_instance_info (
1023: END IF;
1024: END IF;
1025:
1026: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1027: msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1028:
1029: /* Get the instance info */
1030: msd_dem_common_utilities.get_instance_info (
1031: x_errbuf1,
1026: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1027: msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1028:
1029: /* Get the instance info */
1030: msd_dem_common_utilities.get_instance_info (
1031: x_errbuf1,
1032: x_retcode1,
1033: x_instance_code,
1034: x_apps_ver,
1039: IF (x_retcode1 = '-1')
1040: THEN
1041: retcode := -1;
1042: errbuf := x_errbuf1;
1043: msd_dem_common_utilities.log_message ('Error(8): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1044: msd_dem_common_utilities.log_message ('Unable to get instance info.');
1045: RETURN;
1046: END IF;
1047:
1040: THEN
1041: retcode := -1;
1042: errbuf := x_errbuf1;
1043: msd_dem_common_utilities.log_message ('Error(8): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1044: msd_dem_common_utilities.log_message ('Unable to get instance info.');
1045: RETURN;
1046: END IF;
1047:
1048: msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1044: msd_dem_common_utilities.log_message ('Unable to get instance info.');
1045: RETURN;
1046: END IF;
1047:
1048: msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1049:
1050: /* Get the sales staging table name */
1051: x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
1052:
1047:
1048: msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1049:
1050: /* Get the sales staging table name */
1051: x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
1052:
1053: IF (x_dest_table is NULL)
1054: THEN
1055: retcode := -1;
1053: IF (x_dest_table is NULL)
1054: THEN
1055: retcode := -1;
1056: errbuf := 'Unable to find the sales staging tables.';
1057: msd_dem_common_utilities.log_message ('Error(9): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1058: msd_dem_common_utilities.log_message (errbuf);
1059: RETURN;
1060: END IF;
1061:
1054: THEN
1055: retcode := -1;
1056: errbuf := 'Unable to find the sales staging tables.';
1057: msd_dem_common_utilities.log_message ('Error(9): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1058: msd_dem_common_utilities.log_message (errbuf);
1059: RETURN;
1060: END IF;
1061:
1062: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1058: msd_dem_common_utilities.log_message (errbuf);
1059: RETURN;
1060: END IF;
1061:
1062: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1063: msd_dem_common_utilities.log_message ('--------------------------------');
1064: msd_dem_common_utilities.log_message (' ');
1065:
1066: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1059: RETURN;
1060: END IF;
1061:
1062: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1063: msd_dem_common_utilities.log_message ('--------------------------------');
1064: msd_dem_common_utilities.log_message (' ');
1065:
1066: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1067: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1060: END IF;
1061:
1062: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1063: msd_dem_common_utilities.log_message ('--------------------------------');
1064: msd_dem_common_utilities.log_message (' ');
1065:
1066: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1067: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1068:
1062: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1063: msd_dem_common_utilities.log_message ('--------------------------------');
1064: msd_dem_common_utilities.log_message (' ');
1065:
1066: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1067: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1068:
1069: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1070:
1063: msd_dem_common_utilities.log_message ('--------------------------------');
1064: msd_dem_common_utilities.log_message (' ');
1065:
1066: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1067: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1068:
1069: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1070:
1071: /* Truncate the sales staging table */
1065:
1066: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1067: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1068:
1069: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1070:
1071: /* Truncate the sales staging table */
1072: msd_dem_common_utilities.log_message ('Deleting data from sales staging table - ' || x_dest_table);
1073:
1068:
1069: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1070:
1071: /* Truncate the sales staging table */
1072: msd_dem_common_utilities.log_message ('Deleting data from sales staging table - ' || x_dest_table);
1073:
1074: if p_collection_method = 1 then
1075: x_sql := 'TRUNCATE TABLE ' || x_dest_table;
1076: else
1078: end if;
1079:
1080: EXECUTE IMMEDIATE x_sql;
1081:
1082: msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1083:
1084: msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1085:
1086: /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1080: EXECUTE IMMEDIATE x_sql;
1081:
1082: msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1083:
1084: msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1085:
1086: /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1087: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1088: x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1083:
1084: msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1085:
1086: /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1087: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1088: x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1089: EXECUTE IMMEDIATE x_sql;
1090:
1091: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1087: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1088: x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1089: EXECUTE IMMEDIATE x_sql;
1090:
1091: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1092: --saravan
1093: msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1094:
1095: OPEN c_get_dm_schema; --jarora
1089: EXECUTE IMMEDIATE x_sql;
1090:
1091: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1092: --saravan
1093: msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1094:
1095: OPEN c_get_dm_schema; --jarora
1096: FETCH c_get_dm_schema INTO g_schema;
1097: CLOSE c_get_dm_schema;
1099: /* Get the lowest time bucket */
1100: /* Demantra is Installed */
1101: IF (g_schema IS NOT NULL) --jarora
1102: THEN
1103: x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
1104: ELSE
1105: x_dm_time_bucket := 'DAY';
1106: END IF;
1107:
1108: IF (x_dm_time_bucket IS NULL)
1109: THEN
1110: retcode := -1;
1111: errbuf := 'Unable to get lowest time bucket';
1112: msd_dem_common_utilities.log_message ('Error(10): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1113: msd_dem_common_utilities.log_message (errbuf);
1114: RETURN;
1115: ELSIF (upper(x_dm_time_bucket) = 'DAY')
1116: THEN
1109: THEN
1110: retcode := -1;
1111: errbuf := 'Unable to get lowest time bucket';
1112: msd_dem_common_utilities.log_message ('Error(10): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1113: msd_dem_common_utilities.log_message (errbuf);
1114: RETURN;
1115: ELSIF (upper(x_dm_time_bucket) = 'DAY')
1116: THEN
1117: x_dm_time_level := 1;
1118: ELSE
1119: x_dm_time_level := 2;
1120: END IF;
1121:
1122: msd_dem_common_utilities.log_debug ('End get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1123:
1124: /* Collect each series selected by the user */
1125:
1126: /* Booking History - Booked Items - Booked Date */
1123:
1124: /* Collect each series selected by the user */
1125:
1126: /* Booking History - Booked Items - Booked Date */
1127: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1128: IF (p_bh_bi_bd = G_YES)
1129: THEN
1130: collect_series_data (
1131: x_errbuf1,
1129: THEN
1130: collect_series_data (
1131: x_errbuf1,
1132: x_retcode1,
1133: MSD_DEM_COMMON_UTILITIES.C_BH_BI_BD,
1134: x_dest_table,
1135: x_dm_time_level,
1136: p_sr_instance_id,
1137: x_apps_ver,
1147: IF (x_retcode1 = -1)
1148: THEN
1149: retcode := -1;
1150: errbuf := x_errbuf1;
1151: msd_dem_common_utilities.log_message ('Error(11): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1152: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1153: RETURN;
1154: END IF;
1155: END IF;
1148: THEN
1149: retcode := -1;
1150: errbuf := x_errbuf1;
1151: msd_dem_common_utilities.log_message ('Error(11): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1152: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1153: RETURN;
1154: END IF;
1155: END IF;
1156: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1152: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1153: RETURN;
1154: END IF;
1155: END IF;
1156: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1157:
1158:
1159: /* Booking History - Booked Items - Requested Date */
1160: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1156: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1157:
1158:
1159: /* Booking History - Booked Items - Requested Date */
1160: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1161: IF (p_bh_bi_rd = G_YES)
1162: THEN
1163: collect_series_data (
1164: x_errbuf1,
1162: THEN
1163: collect_series_data (
1164: x_errbuf1,
1165: x_retcode1,
1166: MSD_DEM_COMMON_UTILITIES.C_BH_BI_RD,
1167: x_dest_table,
1168: x_dm_time_level,
1169: p_sr_instance_id,
1170: x_apps_ver,
1180: IF (x_retcode1 = -1)
1181: THEN
1182: retcode := -1;
1183: errbuf := x_errbuf1;
1184: msd_dem_common_utilities.log_message ('Error(12): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1185: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1186: RETURN;
1187: END IF;
1188: END IF;
1181: THEN
1182: retcode := -1;
1183: errbuf := x_errbuf1;
1184: msd_dem_common_utilities.log_message ('Error(12): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1185: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1186: RETURN;
1187: END IF;
1188: END IF;
1189: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1185: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1186: RETURN;
1187: END IF;
1188: END IF;
1189: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1190:
1191: /* Booking History - Requested Items - Booked Date */
1192: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1193: IF (p_bh_ri_bd = G_YES)
1188: END IF;
1189: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1190:
1191: /* Booking History - Requested Items - Booked Date */
1192: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1193: IF (p_bh_ri_bd = G_YES)
1194: THEN
1195: collect_series_data (
1196: x_errbuf1,
1194: THEN
1195: collect_series_data (
1196: x_errbuf1,
1197: x_retcode1,
1198: MSD_DEM_COMMON_UTILITIES.C_BH_RI_BD,
1199: x_dest_table,
1200: x_dm_time_level,
1201: p_sr_instance_id,
1202: x_apps_ver,
1212: IF (x_retcode1 = -1)
1213: THEN
1214: retcode := -1;
1215: errbuf := x_errbuf1;
1216: msd_dem_common_utilities.log_message ('Error(13): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1217: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1218: RETURN;
1219: END IF;
1220: END IF;
1213: THEN
1214: retcode := -1;
1215: errbuf := x_errbuf1;
1216: msd_dem_common_utilities.log_message ('Error(13): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1217: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1218: RETURN;
1219: END IF;
1220: END IF;
1221: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1217: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1218: RETURN;
1219: END IF;
1220: END IF;
1221: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1222:
1223: /* Booking History - Requested Items - Requested Date */
1224: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1225: IF (p_bh_ri_rd = G_YES)
1220: END IF;
1221: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1222:
1223: /* Booking History - Requested Items - Requested Date */
1224: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1225: IF (p_bh_ri_rd = G_YES)
1226: THEN
1227: collect_series_data (
1228: x_errbuf1,
1226: THEN
1227: collect_series_data (
1228: x_errbuf1,
1229: x_retcode1,
1230: MSD_DEM_COMMON_UTILITIES.C_BH_RI_RD,
1231: x_dest_table,
1232: x_dm_time_level,
1233: p_sr_instance_id,
1234: x_apps_ver,
1244: IF (x_retcode1 = -1)
1245: THEN
1246: retcode := -1;
1247: errbuf := x_errbuf1;
1248: msd_dem_common_utilities.log_message ('Error(14): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1249: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1250: RETURN;
1251: END IF;
1252: END IF;
1245: THEN
1246: retcode := -1;
1247: errbuf := x_errbuf1;
1248: msd_dem_common_utilities.log_message ('Error(14): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1249: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1250: RETURN;
1251: END IF;
1252: END IF;
1253: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1249: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1250: RETURN;
1251: END IF;
1252: END IF;
1253: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1254:
1255: /* Shipment History - Shipped Items - Shipped Date */
1256: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1257: IF (p_sh_si_sd = G_YES)
1252: END IF;
1253: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1254:
1255: /* Shipment History - Shipped Items - Shipped Date */
1256: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1257: IF (p_sh_si_sd = G_YES)
1258: THEN
1259: collect_series_data (
1260: x_errbuf1,
1258: THEN
1259: collect_series_data (
1260: x_errbuf1,
1261: x_retcode1,
1262: MSD_DEM_COMMON_UTILITIES.C_SH_SI_SD,
1263: x_dest_table,
1264: x_dm_time_level,
1265: p_sr_instance_id,
1266: x_apps_ver,
1276: IF (x_retcode1 = -1)
1277: THEN
1278: retcode := -1;
1279: errbuf := x_errbuf1;
1280: msd_dem_common_utilities.log_message ('Error(15): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1281: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1282: RETURN;
1283: END IF;
1284: END IF;
1277: THEN
1278: retcode := -1;
1279: errbuf := x_errbuf1;
1280: msd_dem_common_utilities.log_message ('Error(15): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1281: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1282: RETURN;
1283: END IF;
1284: END IF;
1285: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1281: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1282: RETURN;
1283: END IF;
1284: END IF;
1285: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1286:
1287: /* Shipment History - Shipped Items - Requested Date */
1288: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1289: IF (p_sh_si_rd = G_YES)
1284: END IF;
1285: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1286:
1287: /* Shipment History - Shipped Items - Requested Date */
1288: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1289: IF (p_sh_si_rd = G_YES)
1290: THEN
1291: collect_series_data (
1292: x_errbuf1,
1290: THEN
1291: collect_series_data (
1292: x_errbuf1,
1293: x_retcode1,
1294: MSD_DEM_COMMON_UTILITIES.C_SH_SI_RD,
1295: x_dest_table,
1296: x_dm_time_level,
1297: p_sr_instance_id,
1298: x_apps_ver,
1308: IF (x_retcode1 = -1)
1309: THEN
1310: retcode := -1;
1311: errbuf := x_errbuf1;
1312: msd_dem_common_utilities.log_message ('Error(16): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1313: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1314: RETURN;
1315: END IF;
1316: END IF;
1309: THEN
1310: retcode := -1;
1311: errbuf := x_errbuf1;
1312: msd_dem_common_utilities.log_message ('Error(16): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1313: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1314: RETURN;
1315: END IF;
1316: END IF;
1317: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1313: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1314: RETURN;
1315: END IF;
1316: END IF;
1317: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1318:
1319: /* Shipment History - Requested Items - Shipped Date */
1320: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1321: IF (p_sh_ri_sd = G_YES)
1316: END IF;
1317: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1318:
1319: /* Shipment History - Requested Items - Shipped Date */
1320: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1321: IF (p_sh_ri_sd = G_YES)
1322: THEN
1323: collect_series_data (
1324: x_errbuf1,
1322: THEN
1323: collect_series_data (
1324: x_errbuf1,
1325: x_retcode1,
1326: MSD_DEM_COMMON_UTILITIES.C_SH_RI_SD,
1327: x_dest_table,
1328: x_dm_time_level,
1329: p_sr_instance_id,
1330: x_apps_ver,
1340: IF (x_retcode1 = -1)
1341: THEN
1342: retcode := -1;
1343: errbuf := x_errbuf1;
1344: msd_dem_common_utilities.log_message ('Error(17): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1345: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1346: RETURN;
1347: END IF;
1348: END IF;
1341: THEN
1342: retcode := -1;
1343: errbuf := x_errbuf1;
1344: msd_dem_common_utilities.log_message ('Error(17): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1345: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1346: RETURN;
1347: END IF;
1348: END IF;
1349: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1345: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1346: RETURN;
1347: END IF;
1348: END IF;
1349: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1350:
1351: /* Shipment History - Requested Items - Requested Date */
1352: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1353: IF (p_sh_ri_rd = G_YES)
1348: END IF;
1349: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1350:
1351: /* Shipment History - Requested Items - Requested Date */
1352: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1353: IF (p_sh_ri_rd = G_YES)
1354: THEN
1355: collect_series_data (
1356: x_errbuf1,
1354: THEN
1355: collect_series_data (
1356: x_errbuf1,
1357: x_retcode1,
1358: MSD_DEM_COMMON_UTILITIES.C_SH_RI_RD,
1359: x_dest_table,
1360: x_dm_time_level,
1361: p_sr_instance_id,
1362: x_apps_ver,
1374: IF (x_retcode1 = -1)
1375: THEN
1376: retcode := -1;
1377: errbuf := x_errbuf1;
1378: msd_dem_common_utilities.log_message ('Error(18): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1379: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1380: RETURN;
1381: END IF;
1382: END IF;
1375: THEN
1376: retcode := -1;
1377: errbuf := x_errbuf1;
1378: msd_dem_common_utilities.log_message ('Error(18): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1379: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1380: RETURN;
1381: END IF;
1382: END IF;
1383: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1379: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1380: RETURN;
1381: END IF;
1382: END IF;
1383: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1384:
1385: /* Bug# 5869314 - Insert dummy rows in the staging table for new items */
1386: msd_dem_common_utilities.log_debug ('Begin Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1387: insert_dummy_rows (
1382: END IF;
1383: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1384:
1385: /* Bug# 5869314 - Insert dummy rows in the staging table for new items */
1386: msd_dem_common_utilities.log_debug ('Begin Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1387: insert_dummy_rows (
1388: x_errbuf1,
1389: x_retcode1,
1390: x_dest_table,
1393: IF (x_retcode1 = 1)
1394: THEN
1395: retcode := 1;
1396: errbuf := x_errbuf1;
1397: msd_dem_common_utilities.log_message ('Warning(6): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1398: msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1399: END IF;
1400: msd_dem_common_utilities.log_debug ('End Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1401:
1394: THEN
1395: retcode := 1;
1396: errbuf := x_errbuf1;
1397: msd_dem_common_utilities.log_message ('Warning(6): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1398: msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1399: END IF;
1400: msd_dem_common_utilities.log_debug ('End Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1401:
1402: COMMIT;
1396: errbuf := x_errbuf1;
1397: msd_dem_common_utilities.log_message ('Warning(6): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1398: msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1399: END IF;
1400: msd_dem_common_utilities.log_debug ('End Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1401:
1402: COMMIT;
1403:
1404: /* Call Custom Hook for History */
1402: COMMIT;
1403:
1404: /* Call Custom Hook for History */
1405:
1406: msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.history_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1407:
1408: msd_dem_custom_hooks.history_hook (
1409: x_errbuf1,
1410: x_retcode1);
1408: msd_dem_custom_hooks.history_hook (
1409: x_errbuf1,
1410: x_retcode1);
1411:
1412: msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.history_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1413:
1414: IF (x_retcode1 = -1)
1415: THEN
1416: retcode := -1;
1414: IF (x_retcode1 = -1)
1415: THEN
1416: retcode := -1;
1417: errbuf := x_errbuf1;
1418: msd_dem_common_utilities.log_message ('Error(19): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1419: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.history_hook ');
1420: RETURN;
1421: END IF;
1422:
1415: THEN
1416: retcode := -1;
1417: errbuf := x_errbuf1;
1418: msd_dem_common_utilities.log_message ('Error(19): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1419: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.history_hook ');
1420: RETURN;
1421: END IF;
1422:
1423: /* Analyze Sales Staging Table */
1421: END IF;
1422:
1423: /* Analyze Sales Staging Table */
1424:
1425: msd_dem_common_utilities.log_debug ('Begin Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1426:
1427: analyze_table (
1428: x_errbuf1,
1429: x_retcode1,
1428: x_errbuf1,
1429: x_retcode1,
1430: x_dest_table);
1431:
1432: msd_dem_common_utilities.log_debug ('End Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1433:
1434: IF (x_retcode1 = 1)
1435: THEN
1436: retcode := 1;
1434: IF (x_retcode1 = 1)
1435: THEN
1436: retcode := 1;
1437: errbuf := x_errbuf1;
1438: msd_dem_common_utilities.log_message ('Warning(7): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1439: msd_dem_common_utilities.log_message ('Error while analyzing sales staging table. ');
1440: END IF;
1441:
1442: /*
1435: THEN
1436: retcode := 1;
1437: errbuf := x_errbuf1;
1438: msd_dem_common_utilities.log_message ('Warning(7): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1439: msd_dem_common_utilities.log_message ('Error while analyzing sales staging table. ');
1440: END IF;
1441:
1442: /*
1443: *Order Realignment
1452: l_sql := 'select id, table_name, from_date, until_date from '|| g_schema || '.transfer_query where query_name = ''Purge History Data''';
1453: execute immediate l_sql into l_profile_id, l_table_name, l_start_date, l_until_date;
1454:
1455: /* Refreshing the Purge Series Data profile to the default value ie No load and No Purge option */
1456: msd_dem_common_utilities.REFRESH_PURGE_SERIES(x_errbuf1, x_retcode1, l_profile_id, g_schema);
1457:
1458: IF (x_retcode1 = -1)
1459: THEN
1460: retcode := -1;
1459: THEN
1460: retcode := -1;
1461: errbuf := x_errbuf1;
1462:
1463: msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
1464: END IF;
1465:
1466: /* Calling API to modify the data profile to purge selected series */
1467: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_ATTR - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1463: msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
1464: END IF;
1465:
1466: /* Calling API to modify the data profile to purge selected series */
1467: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_ATTR - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1468:
1469:
1470:
1471: if p_bh_bi_bd = G_YES
1542:
1543:
1544:
1545: /* Calling API to modify the data profile date range */
1546: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1547:
1548: l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
1549: execute immediate l_sql into x_from_date;
1550:
1554: if (x_from_date > x_to_date) then
1555: x_to_date := x_from_date;
1556: end if;
1557:
1558: msd_dem_common_utilities.log_message ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
1559:
1560: l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id||', '''|| x_from_date||''' , '''||x_to_date||'''); end;';
1561: execute immediate l_sql;
1562:
1561: execute immediate l_sql;
1562:
1563:
1564: /* Calling API to notify the application server to refresh its engine */
1565: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1566:
1567: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
1568: execute immediate l_sql;
1569:
1584:
1585: execute immediate l_sql;
1586:
1587: else
1588: msd_dem_common_utilities.log_message('Demantra Schema not set');
1589: end if;
1590: EXCEPTION
1591: WHEN OTHERS THEN
1592: retcode := 1 ;
1590: EXCEPTION
1591: WHEN OTHERS THEN
1592: retcode := 1 ;
1593: errbuf := substr(SQLERRM,1,150);
1594: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1595: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1596: msd_dem_common_utilities.log_debug (errbuf);
1597: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1598:
1591: WHEN OTHERS THEN
1592: retcode := 1 ;
1593: errbuf := substr(SQLERRM,1,150);
1594: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1595: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1596: msd_dem_common_utilities.log_debug (errbuf);
1597: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1598:
1599: RETURN;
1592: retcode := 1 ;
1593: errbuf := substr(SQLERRM,1,150);
1594: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1595: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1596: msd_dem_common_utilities.log_debug (errbuf);
1597: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1598:
1599: RETURN;
1600: End;
1593: errbuf := substr(SQLERRM,1,150);
1594: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1595: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1596: msd_dem_common_utilities.log_debug (errbuf);
1597: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1598:
1599: RETURN;
1600: End;
1601:
1600: End;
1601:
1602: retcode := x_retcode;
1603: errbuf := x_errbuf;
1604: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1605:
1606: EXCEPTION
1607: WHEN OTHERS THEN
1608: retcode := -1 ;
1606: EXCEPTION
1607: WHEN OTHERS THEN
1608: retcode := -1 ;
1609: errbuf := substr(SQLERRM,1,150);
1610: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1611: msd_dem_common_utilities.log_message (errbuf);
1612: RETURN;
1613:
1614: END COLLECT_HISTORY_DATA;
1607: WHEN OTHERS THEN
1608: retcode := -1 ;
1609: errbuf := substr(SQLERRM,1,150);
1610: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1611: msd_dem_common_utilities.log_message (errbuf);
1612: RETURN;
1613:
1614: END COLLECT_HISTORY_DATA;
1615:
1630:
1631:
1632:
1633: BEGIN
1634: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1635:
1636: DEM_SCHEMA := fnd_profile.value('MSD_DEM_SCHEMA');
1637:
1638: IF (p_auto_run_download = G_YES)
1645:
1646: l_stmt := 'alter session set current_schema=' || DEM_SCHEMA;
1647: execute immediate l_stmt;
1648:
1649: msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1650: msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1651:
1652: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1653: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1646: l_stmt := 'alter session set current_schema=' || DEM_SCHEMA;
1647: execute immediate l_stmt;
1648:
1649: msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1650: msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1651:
1652: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1653: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1654: execute immediate l_stmt;
1648:
1649: msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1650: msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1651:
1652: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1653: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1654: execute immediate l_stmt;
1655:
1656: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1652: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1653: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1654: execute immediate l_stmt;
1655:
1656: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1657: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_ITEMS; end;';
1658: execute immediate l_stmt;
1659:
1660: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1656: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1657: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_ITEMS; end;';
1658: execute immediate l_stmt;
1659:
1660: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1661: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_LOCATION; end;';
1662: execute immediate l_stmt;
1663:
1664: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_SALES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1660: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1661: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_LOCATION; end;';
1662: execute immediate l_stmt;
1663:
1664: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_SALES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1665: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_SALES; end;';
1666:
1667: execute immediate l_stmt;
1668:
1665: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_SALES; end;';
1666:
1667: execute immediate l_stmt;
1668:
1669: msd_dem_common_utilities.log_message ('End - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1670:
1671: commit;
1672:
1673: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.LOG_EP_LOAD_SUCCESS; end;';
1678:
1679: */
1680:
1681:
1682: l_sql := 'select user_id from ' ||msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';
1683: execute immediate l_sql into l_user_id;
1684:
1685: IF l_user_id is not null
1686: then
1687: l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
1688: execute immediate l_sql into l_user_name, l_password;
1689:
1690: ELSE
1691: l_sql := 'select user_id from ' ||msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';
1692: execute immediate l_sql into l_user_id;
1693:
1694: If l_user_id is not null
1695: then
1695: then
1696: l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
1697: execute immediate l_sql into l_user_name, l_password;
1698: else
1699: msd_dem_common_utilities.log_message('Component is not found.');
1700: end if;
1701: END IF;
1702:
1703:
1708: l_sql := 'SELECT
1709: utl_http.request('''||l_url||'/WorkflowServer?action=run_proc&user='||l_user_name||'&password='||l_password||'&schema=EBS%20Full%20Download&sync=no'') FROM dual';
1710: execute immediate l_sql into l_dummy;
1711: else
1712: msd_dem_common_utilities.log_message('Error in launching the download workflow.');
1713: retcode := -1;
1714: Return;
1715: end if;
1716:
1714: Return;
1715: end if;
1716:
1717: else
1718: msd_dem_common_utilities.log_message('Demantra Schema not set');
1719: end if;
1720: ELSE
1721: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
1722: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
1717: else
1718: msd_dem_common_utilities.log_message('Demantra Schema not set');
1719: end if;
1720: ELSE
1721: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
1722: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
1723: END IF;
1724:
1725: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1718: msd_dem_common_utilities.log_message('Demantra Schema not set');
1719: end if;
1720: ELSE
1721: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
1722: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
1723: END IF;
1724:
1725: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1726:
1721: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
1722: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
1723: END IF;
1724:
1725: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1726:
1727: EXCEPTION
1728: WHEN OTHERS THEN
1729: errbuf := substr(SQLERRM,1,150);
1732: -- execute immediate l_stmt;
1733: -- l_stmt := 'alter session set current_schema=APPS';
1734: -- execute immediate l_stmt;
1735:
1736: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1737: msd_dem_common_utilities.log_message (errbuf);
1738: RETURN;
1739:
1740: END RUN_LOAD;
1733: -- l_stmt := 'alter session set current_schema=APPS';
1734: -- execute immediate l_stmt;
1735:
1736: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1737: msd_dem_common_utilities.log_message (errbuf);
1738: RETURN;
1739:
1740: END RUN_LOAD;
1741: