[Home] [Help]
83: OR p_exclude_order_types IS NOT NULL)
84: THEN
85: retcode := 1;
86: errbuf := 'The parameters Include Order Types and Exclude Order Types are ignored, if Collect All Order Types is Yes.';
87: 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'));
88: msd_dem_common_utilities.log_message (errbuf);
89: END IF;
90:
91: p_order_type_flag := C_ALL;
84: THEN
85: retcode := 1;
86: errbuf := 'The parameters Include Order Types and Exclude Order Types are ignored, if Collect All Order Types is Yes.';
87: 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'));
88: msd_dem_common_utilities.log_message (errbuf);
89: END IF;
90:
91: p_order_type_flag := C_ALL;
92: p_order_type_ids := '';
103: AND p_exclude_order_types IS NULL)
104: THEN
105: retcode := -1;
106: errbuf := 'Exactly one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
107: 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'));
108: msd_dem_common_utilities.log_message (errbuf);
109: RETURN -1;
110: ELSIF ( p_include_order_types IS NOT NULL
111: AND p_exclude_order_types IS NOT NULL)
104: THEN
105: retcode := -1;
106: errbuf := 'Exactly one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
107: 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'));
108: msd_dem_common_utilities.log_message (errbuf);
109: RETURN -1;
110: ELSIF ( p_include_order_types IS NOT NULL
111: AND p_exclude_order_types IS NOT NULL)
112: THEN
111: AND p_exclude_order_types IS NOT NULL)
112: THEN
113: retcode := -1;
114: errbuf := 'Only one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
115: 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'));
116: msd_dem_common_utilities.log_message (errbuf);
117: RETURN -1;
118: ELSIF (p_include_order_types IS NOT NULL)
119: THEN
112: THEN
113: retcode := -1;
114: errbuf := 'Only one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
115: 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'));
116: msd_dem_common_utilities.log_message (errbuf);
117: RETURN -1;
118: ELSIF (p_include_order_types IS NOT NULL)
119: THEN
120: l_order_type_flag := C_INCLUDE;
150: IF (l_order_type_table.COUNT = 0)
151: THEN
152: retcode := -1;
153: errbuf := 'No order types found in the source';
154: 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'));
155: msd_dem_common_utilities.log_message (errbuf);
156: RETURN -1;
157: END IF;
158:
151: THEN
152: retcode := -1;
153: errbuf := 'No order types found in the source';
154: 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'));
155: msd_dem_common_utilities.log_message (errbuf);
156: RETURN -1;
157: END IF;
158:
159:
227: l_start := l_position + 1;
228:
229: END LOOP;
230:
231: msd_dem_common_utilities.log_message (' Order Types');
232: msd_dem_common_utilities.log_message (' -------------');
233:
234: msd_dem_common_utilities.log_message (' Valid Order Types');
235: msd_dem_common_utilities.log_message (' -------------------');
228:
229: END LOOP;
230:
231: msd_dem_common_utilities.log_message (' Order Types');
232: msd_dem_common_utilities.log_message (' -------------');
233:
234: msd_dem_common_utilities.log_message (' Valid Order Types');
235: msd_dem_common_utilities.log_message (' -------------------');
236:
230:
231: msd_dem_common_utilities.log_message (' Order Types');
232: msd_dem_common_utilities.log_message (' -------------');
233:
234: msd_dem_common_utilities.log_message (' Valid Order Types');
235: msd_dem_common_utilities.log_message (' -------------------');
236:
237: IF (l_valid_count <> 0)
238: THEN
231: msd_dem_common_utilities.log_message (' Order Types');
232: msd_dem_common_utilities.log_message (' -------------');
233:
234: msd_dem_common_utilities.log_message (' Valid Order Types');
235: msd_dem_common_utilities.log_message (' -------------------');
236:
237: IF (l_valid_count <> 0)
238: THEN
239: FOR i in l_valid_order_type_table.FIRST..l_valid_order_type_table.LAST
237: IF (l_valid_count <> 0)
238: THEN
239: FOR i in l_valid_order_type_table.FIRST..l_valid_order_type_table.LAST
240: LOOP
241: msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_valid_order_type_table(i));
242: END LOOP;
243: ELSE
244: msd_dem_common_utilities.log_message ('No valid order types found in user input');
245: END IF;
240: LOOP
241: msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_valid_order_type_table(i));
242: END LOOP;
243: ELSE
244: msd_dem_common_utilities.log_message ('No valid order types found in user input');
245: END IF;
246:
247: msd_dem_common_utilities.log_message (' ');
248: msd_dem_common_utilities.log_message (' Invalid Order Types');
243: ELSE
244: msd_dem_common_utilities.log_message ('No valid order types found in user input');
245: END IF;
246:
247: msd_dem_common_utilities.log_message (' ');
248: msd_dem_common_utilities.log_message (' Invalid Order Types');
249: msd_dem_common_utilities.log_message (' ---------------------');
250:
251: IF (l_invalid_count <> 0)
244: msd_dem_common_utilities.log_message ('No valid order types found in user input');
245: END IF;
246:
247: msd_dem_common_utilities.log_message (' ');
248: msd_dem_common_utilities.log_message (' Invalid Order Types');
249: msd_dem_common_utilities.log_message (' ---------------------');
250:
251: IF (l_invalid_count <> 0)
252: THEN
245: END IF;
246:
247: msd_dem_common_utilities.log_message (' ');
248: msd_dem_common_utilities.log_message (' Invalid Order Types');
249: msd_dem_common_utilities.log_message (' ---------------------');
250:
251: IF (l_invalid_count <> 0)
252: THEN
253: FOR i in l_invalid_order_type_table.FIRST..l_invalid_order_type_table.LAST
251: IF (l_invalid_count <> 0)
252: THEN
253: FOR i in l_invalid_order_type_table.FIRST..l_invalid_order_type_table.LAST
254: LOOP
255: msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_invalid_order_type_table(i));
256: END LOOP;
257: END IF;
258:
259: IF (l_valid_count = 0)
259: IF (l_valid_count = 0)
260: THEN
261: retcode := -1;
262: errbuf := 'No valid order types found in user input';
263: 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'));
264: RETURN -1;
265: END IF;
266:
267: IF (l_invalid_count <> 0)
267: IF (l_invalid_count <> 0)
268: THEN
269: retcode := 1;
270: errbuf := 'Invalid order types found in user input';
271: 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'));
272: END IF;
273:
274:
275: p_order_type_flag := l_order_type_flag;
288: p_order_type_ids, --query
289: l_order_type_ids, --p_entity_name
290: p_sr_instance_id );
291:
292: msd_dem_common_utilities.log_debug('SQL statement used for selecting Order Types is :- ' || p_order_type_ids );
293:
294: If(p_order_type_ids is null) then
295: msd_dem_common_utilities.log_message(l_order_type_ids || ' entity_name NOT found. Please specify the correct Entity_Name.');
296: retcode := -1;
291:
292: msd_dem_common_utilities.log_debug('SQL statement used for selecting Order Types is :- ' || p_order_type_ids );
293:
294: If(p_order_type_ids is null) then
295: msd_dem_common_utilities.log_message(l_order_type_ids || ' entity_name NOT found. Please specify the correct Entity_Name.');
296: retcode := -1;
297: End if;
298:
299: l_invalid_count := 0 ;
322: 'fvtl.flex_value_id = fv.flex_value_id and ' ||
323: 'fv.enabled_flag = ''Y'' ';
324:
325: p_order_type_ids := l_order_type_ids;
326: msd_dem_common_utilities.log_debug('ValueSet used for selecting Order Types is :- ' || l_order_types );
327: l_invalid_count := 0 ;
328: RETURN l_invalid_count ;
329:
330: END IF;
358: ORDER BY created desc;
359:
360: BEGIN
361:
362: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
363:
364: x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE');
365:
366: open c_get_dm_schema;
360: BEGIN
361:
362: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
363:
364: x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE');
365:
366: open c_get_dm_schema;
367: fetch c_get_dm_schema into x_dem_schema;
368: close c_get_dm_schema;
391: || ' AND orgs.organization_code = orgs2.organization '
392: || ' AND orgs1.t_ep_lob_id = orgs2.t_ep_lob_id '
393: || ' AND orgs1.t_ep_lob_id > 0)';
394:
395: msd_dem_common_utilities.log_debug (delete_sql);
396: execute immediate delete_sql using p_instance_id;
397: commit;
398: else
399: msd_dem_common_utilities.log_message('Demantra not installed. Not deleting Internal Sales Orders.');
395: msd_dem_common_utilities.log_debug (delete_sql);
396: execute immediate delete_sql using p_instance_id;
397: commit;
398: else
399: msd_dem_common_utilities.log_message('Demantra not installed. Not deleting Internal Sales Orders.');
400: msd_dem_common_utilities.log_debug('Demantra not installed. Not deleting Internal Sales Orders.');
401: end if;
402:
403: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
396: execute immediate delete_sql using p_instance_id;
397: commit;
398: else
399: msd_dem_common_utilities.log_message('Demantra not installed. Not deleting Internal Sales Orders.');
400: msd_dem_common_utilities.log_debug('Demantra not installed. Not deleting Internal Sales Orders.');
401: end if;
402:
403: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
404:
399: msd_dem_common_utilities.log_message('Demantra not installed. Not deleting Internal Sales Orders.');
400: msd_dem_common_utilities.log_debug('Demantra not installed. Not deleting Internal Sales Orders.');
401: end if;
402:
403: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
404:
405: retcode := 1;
406: return;
407:
408: EXCEPTION
409: WHEN OTHERS THEN
410: errbuf := substr(SQLERRM,1,150);
411: retcode := -1 ;
412: msd_dem_common_utilities.log_debug ('Exception:
413: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
414: msd_dem_common_utilities.log_debug (errbuf);
415: RETURN;
416:
410: errbuf := substr(SQLERRM,1,150);
411: retcode := -1 ;
412: msd_dem_common_utilities.log_debug ('Exception:
413: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
414: msd_dem_common_utilities.log_debug (errbuf);
415: RETURN;
416:
417:
418: END DELETE_INTERNAL_SALES_ORDERS;
482: l_source_view_hint varchar2(255) := NULL;
483: l_source_view_hint2 varchar2(255) := NULL;
484: l_extra_where varchar2(500) := NULL;
485:
486: x_dem_version VARCHAR2(10) := msd_dem_common_utilities.get_demantra_version;
487:
488:
489: BEGIN
490:
487:
488:
489: BEGIN
490:
491: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
492:
493: OPEN c_get_series_info;
494: 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,
495: l_source_view_hint,l_source_view_hint2,l_extra_where;
498: IF (l_identifier IS NULL)
499: THEN
500: retcode := -1;
501: errbuf := 'Unable to get the query identifier.';
502: 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'));
503: msd_dem_common_utilities.log_message (errbuf);
504: RETURN;
505: END IF;
506:
499: THEN
500: retcode := -1;
501: errbuf := 'Unable to get the query identifier.';
502: 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'));
503: msd_dem_common_utilities.log_message (errbuf);
504: RETURN;
505: END IF;
506:
507: /* Check if custom view for Discrete */
525: IF ( p_instance_type <> 2
526: OR p_apps_ver = 4)
527: THEN
528:
529: msd_dem_common_utilities.log_debug ('Begin collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
530:
531: x_add_where_clause := ' 1 = 1 ';
532:
533: /* If p_collect_iso = No, then include an additional condition to filter out Internal Sales Orders */
535: THEN
536: x_add_where_clause := x_add_where_clause || ' AND nvl(ooha.order_source_id, 0) <> 10 ';
537: ELSIF (p_collect_ISO = G_YES) THEN -- BUG#13716734 --kkhatri--12.3--BUG#14683310 --kkhatri--12.2.1
538:
539: MSD_DEM_COMMON_UTILITIES.GET_DBLINK(ERRBUF,RETCODE,P_SR_INSTANCE_ID,XDBLINK);
540: execute immediate 'select fnd_profile.value' || XDBLINK ||'(''MSC_SUBINVENTORY_PART_CONDITION'') from dual'
541: into x_profile_val;
542:
543: IF (x_profile_val is not null) THEN
574: || '$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'
575: || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
576: || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
577: || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
578: || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
579:
580: IF (g_collection_method <> 1) THEN
581: x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' WHERE SDATE BETWEEN '
582: || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
600: || '$C_SOURCE_DATE#' || SUBSTR(L_MSD_SOURCE_DATE_COL, 1, instr(L_MSD_SOURCE_DATE_COL, 'SDATE')-1) ||' PDATE'
601: || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
602: || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
603: || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
604: || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
605:
606: IF (g_collection_method <> 1) THEN
607: x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN '
608: || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
656: OR x_large_sql IS NULL)
657: THEN
658: retcode := -1;
659: errbuf := 'Unable to get the query.';
660: 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'));
661: msd_dem_common_utilities.log_message (errbuf);
662: RETURN;
663: END IF;
664:
657: THEN
658: retcode := -1;
659: errbuf := 'Unable to get the query.';
660: 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'));
661: msd_dem_common_utilities.log_message (errbuf);
662: RETURN;
663: END IF;
664:
665:
662: RETURN;
663: END IF;
664:
665:
666: msd_dem_common_utilities.log_debug ('Query - ');
667: msd_dem_common_utilities.log_debug (x_large_sql);
668:
669: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
670:
663: END IF;
664:
665:
666: msd_dem_common_utilities.log_debug ('Query - ');
667: msd_dem_common_utilities.log_debug (x_large_sql);
668:
669: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
670:
671: BEGIN
665:
666: msd_dem_common_utilities.log_debug ('Query - ');
667: msd_dem_common_utilities.log_debug (x_large_sql);
668:
669: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
670:
671: BEGIN
672: /* Upsert history data into sales staging table */
673: EXECUTE IMMEDIATE x_large_sql;
675: EXCEPTION
676: WHEN OTHERS THEN
677: retcode := -1 ;
678: errbuf := substr(SQLERRM,1,150);
679: 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'));
680: msd_dem_common_utilities.log_message (errbuf);
681: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: RETURN;
683: END;
676: WHEN OTHERS THEN
677: retcode := -1 ;
678: errbuf := substr(SQLERRM,1,150);
679: 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'));
680: msd_dem_common_utilities.log_message (errbuf);
681: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: RETURN;
683: END;
684:
677: retcode := -1 ;
678: errbuf := substr(SQLERRM,1,150);
679: 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'));
680: msd_dem_common_utilities.log_message (errbuf);
681: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: RETURN;
683: END;
684:
685: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
681: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682: RETURN;
683: END;
684:
685: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
686:
687: msd_dem_common_utilities.log_debug ('End collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
688:
689: END IF;
683: END;
684:
685: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
686:
687: msd_dem_common_utilities.log_debug ('End collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
688:
689: END IF;
690:
691: x_large_sql := NULL;
695: IF ( p_instance_type IN (2, 4)
696: AND p_apps_ver = 3)
697: THEN
698:
699: msd_dem_common_utilities.log_debug ('Begin collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
700:
701: x_pquery_identifier := replace(l_identifier , 'MSD','GMP') ;
702:
703: x_add_where_clause := ' 1 = 1 ';
707: THEN
708: x_add_where_clause := x_add_where_clause || ' AND decode(ool.to_whse, NULL, 10, 0) <> 10 ';
709: -- BUG#13716734 kkhatri 12.3 BUG#14683310 --kkhatri --12.2.1
710: ELSIF (p_collect_ISO = G_YES) THEN
711: MSD_DEM_COMMON_UTILITIES.GET_DBLINK(ERRBUF,RETCODE,P_SR_INSTANCE_ID,XDBLINK);
712: execute immediate 'select fnd_profile.value' || XDBLINK ||'(''MSC_SUBINVENTORY_PART_CONDITION'') from dual'
713: into x_profile_val;
714:
715: IF (x_profile_val is not null) THEN
781: OR x_large_sql IS NULL)
782: THEN
783: retcode := -1;
784: errbuf := 'Unable to get the query.';
785: 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'));
786: msd_dem_common_utilities.log_message (errbuf);
787: RETURN;
788: END IF;
789:
782: THEN
783: retcode := -1;
784: errbuf := 'Unable to get the query.';
785: 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'));
786: msd_dem_common_utilities.log_message (errbuf);
787: RETURN;
788: END IF;
789:
790:
787: RETURN;
788: END IF;
789:
790:
791: msd_dem_common_utilities.log_debug ('Query - ');
792: msd_dem_common_utilities.log_debug (x_large_sql);
793:
794: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
795:
788: END IF;
789:
790:
791: msd_dem_common_utilities.log_debug ('Query - ');
792: msd_dem_common_utilities.log_debug (x_large_sql);
793:
794: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
795:
796: BEGIN
790:
791: msd_dem_common_utilities.log_debug ('Query - ');
792: msd_dem_common_utilities.log_debug (x_large_sql);
793:
794: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
795:
796: BEGIN
797: /* Upsert history data into sales staging table */
798: EXECUTE IMMEDIATE x_large_sql;
800: EXCEPTION
801: WHEN OTHERS THEN
802: retcode := -1 ;
803: errbuf := substr(SQLERRM,1,150);
804: 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'));
805: msd_dem_common_utilities.log_message (errbuf);
806: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807: RETURN;
808: END;
801: WHEN OTHERS THEN
802: retcode := -1 ;
803: errbuf := substr(SQLERRM,1,150);
804: 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'));
805: msd_dem_common_utilities.log_message (errbuf);
806: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807: RETURN;
808: END;
809:
802: retcode := -1 ;
803: errbuf := substr(SQLERRM,1,150);
804: 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'));
805: msd_dem_common_utilities.log_message (errbuf);
806: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807: RETURN;
808: END;
809:
810: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
806: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807: RETURN;
808: END;
809:
810: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
811: msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
812:
813: END IF;
814:
807: RETURN;
808: END;
809:
810: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
811: msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
812:
813: END IF;
814:
815: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
811: msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
812:
813: END IF;
814:
815: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
816:
817: EXCEPTION
818: WHEN OTHERS THEN
819: retcode := -1 ;
817: EXCEPTION
818: WHEN OTHERS THEN
819: retcode := -1 ;
820: errbuf := substr(SQLERRM,1,150);
821: 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'));
822: msd_dem_common_utilities.log_message (errbuf);
823: RETURN;
824:
825: END COLLECT_SERIES_DATA;
818: WHEN OTHERS THEN
819: retcode := -1 ;
820: errbuf := substr(SQLERRM,1,150);
821: 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'));
822: msd_dem_common_utilities.log_message (errbuf);
823: RETURN;
824:
825: END COLLECT_SERIES_DATA;
826:
853: x_new_items_present NUMBER := NULL;
854: x_sql VARCHAR2(32000) := NULL;
855: BEGIN
856:
857: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
858:
859: /* Check if there are any yet to be processed NPIs */
860: OPEN c_check_new_items;
861: FETCH c_check_new_items INTO x_new_items_present;
862: CLOSE c_check_new_items;
863:
864: IF (x_new_items_present = 1)
865: THEN
866: msd_dem_common_utilities.log_message ('Found new items for processing');
867:
868: msd_dem_query_utilities.get_query(
869: x_retcode,
870: x_sql,
875: IF (x_retcode = -1)
876: THEN
877: retcode := 1;
878: errbuf := 'Unable to get the query for inserting dummy rows for new items into sales staging table';
879: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.insert_dummy_rows');
880: msd_dem_common_utilities.log_message (errbuf);
881: RETURN;
882: END IF;
883:
876: THEN
877: retcode := 1;
878: errbuf := 'Unable to get the query for inserting dummy rows for new items into sales staging table';
879: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.insert_dummy_rows');
880: msd_dem_common_utilities.log_message (errbuf);
881: RETURN;
882: END IF;
883:
884: msd_dem_common_utilities.log_debug ('Query - ');
880: msd_dem_common_utilities.log_message (errbuf);
881: RETURN;
882: END IF;
883:
884: msd_dem_common_utilities.log_debug ('Query - ');
885: msd_dem_common_utilities.log_debug ('Bind Variables - ');
886: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
887: msd_dem_common_utilities.log_debug (x_sql);
888:
881: RETURN;
882: END IF;
883:
884: msd_dem_common_utilities.log_debug ('Query - ');
885: msd_dem_common_utilities.log_debug ('Bind Variables - ');
886: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
887: msd_dem_common_utilities.log_debug (x_sql);
888:
889: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
882: END IF;
883:
884: msd_dem_common_utilities.log_debug ('Query - ');
885: msd_dem_common_utilities.log_debug ('Bind Variables - ');
886: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
887: msd_dem_common_utilities.log_debug (x_sql);
888:
889: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
890: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id;
883:
884: msd_dem_common_utilities.log_debug ('Query - ');
885: msd_dem_common_utilities.log_debug ('Bind Variables - ');
886: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
887: msd_dem_common_utilities.log_debug (x_sql);
888:
889: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
890: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id;
891: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
885: msd_dem_common_utilities.log_debug ('Bind Variables - ');
886: msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
887: msd_dem_common_utilities.log_debug (x_sql);
888:
889: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
890: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id;
891: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
892:
893: /* Set the process_flag */
887: msd_dem_common_utilities.log_debug (x_sql);
888:
889: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
890: EXECUTE IMMEDIATE x_sql USING p_sr_instance_id;
891: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
892:
893: /* Set the process_flag */
894: UPDATE msd_dem_new_items
895: SET process_flag = 1
899: COMMIT;
900:
901: END IF;
902:
903: msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
904:
905: EXCEPTION
906: WHEN OTHERS THEN
907: retcode := 1 ;
905: EXCEPTION
906: WHEN OTHERS THEN
907: retcode := 1 ;
908: errbuf := substr(SQLERRM,1,150);
909: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
910: msd_dem_common_utilities.log_message (errbuf);
911: RETURN;
912: END INSERT_DUMMY_ROWS;
913:
906: WHEN OTHERS THEN
907: retcode := 1 ;
908: errbuf := substr(SQLERRM,1,150);
909: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
910: msd_dem_common_utilities.log_message (errbuf);
911: RETURN;
912: END INSERT_DUMMY_ROWS;
913:
914:
929: x_pos NUMBER := NULL;
930:
931: BEGIN
932:
933: msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
934:
935: x_pos := instr( p_table_name, '.', 1, 1);
936:
937: IF (x_pos = 0)
942: x_schema_name := substr (p_table_name, 1, x_pos - 1);
943: x_table_name := substr (p_table_name, x_pos +1);
944: END IF;
945:
946: msd_dem_common_utilities.log_message ('Analyzing Table - ' || x_schema_name || '.' || x_table_name);
947: fnd_stats.gather_table_stats(x_schema_name, x_table_name, 10, 4);
948:
949: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
950:
945:
946: msd_dem_common_utilities.log_message ('Analyzing Table - ' || x_schema_name || '.' || x_table_name);
947: fnd_stats.gather_table_stats(x_schema_name, x_table_name, 10, 4);
948:
949: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
950:
951:
952: EXCEPTION
953: WHEN OTHERS THEN
952: EXCEPTION
953: WHEN OTHERS THEN
954: retcode := 1 ;
955: errbuf := substr(SQLERRM,1,150);
956: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
957: msd_dem_common_utilities.log_message (errbuf);
958: RETURN;
959: END ANALYZE_TABLE;
960:
953: WHEN OTHERS THEN
954: retcode := 1 ;
955: errbuf := substr(SQLERRM,1,150);
956: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
957: msd_dem_common_utilities.log_message (errbuf);
958: RETURN;
959: END ANALYZE_TABLE;
960:
961: /*** PUBLIC PROCEDURES ***/
1043: ORDER BY created desc;
1044:
1045: BEGIN
1046:
1047: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1048:
1049: /* Get the db link to the source instance */
1050: msd_dem_common_utilities.get_dblink (
1051: x_errbuf,
1046:
1047: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1048:
1049: /* Get the db link to the source instance */
1050: msd_dem_common_utilities.get_dblink (
1051: x_errbuf,
1052: x_retcode,
1053: p_sr_instance_id,
1054: g_dblink);
1056: IF (x_retcode = '-1')
1057: THEN
1058: retcode := -1;
1059: errbuf := x_errbuf;
1060: 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'));
1061: RETURN;
1062: END IF;
1063:
1064: g_collection_method := p_collection_method;
1064: g_collection_method := p_collection_method;
1065:
1066: /* VALIDATION OF INPUT PARAMETERS - BEGIN */
1067:
1068: msd_dem_common_utilities.log_debug ('Begin validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1069:
1070: /* Atleast one parameter must be selected */
1071: IF ( p_bh_bi_bd = G_NO
1072: AND p_bh_bi_rd = G_NO
1078: AND p_sh_ri_rd = G_NO)
1079: THEN
1080: retcode := -1;
1081: errbuf := 'No series selected for collection';
1082: 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'));
1083: msd_dem_common_utilities.log_message (errbuf);
1084: RETURN;
1085: END IF;
1086:
1079: THEN
1080: retcode := -1;
1081: errbuf := 'No series selected for collection';
1082: 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'));
1083: msd_dem_common_utilities.log_message (errbuf);
1084: RETURN;
1085: END IF;
1086:
1087:
1092: OR p_collection_window IS NOT NULL))
1093: THEN
1094: x_retcode := 1;
1095: x_errbuf := 'Date Range filters are ignored in ''Refresh'' collections';
1096: 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'));
1097: msd_dem_common_utilities.log_message (x_errbuf);
1098: END IF;
1099:
1100:
1093: THEN
1094: x_retcode := 1;
1095: x_errbuf := 'Date Range filters are ignored in ''Refresh'' collections';
1096: 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'));
1097: msd_dem_common_utilities.log_message (x_errbuf);
1098: END IF;
1099:
1100:
1101: /* Show Warning if collection method is net change, date range type is Rolling and from date and to date are specified */
1105: OR p_to_date IS NOT NULL))
1106: THEN
1107: x_retcode := 1;
1108: x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date range type is selected.';
1109: 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'));
1110: msd_dem_common_utilities.log_message (x_errbuf);
1111: END IF;
1112:
1113:
1106: THEN
1107: x_retcode := 1;
1108: x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date range type is selected.';
1109: 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'));
1110: msd_dem_common_utilities.log_message (x_errbuf);
1111: END IF;
1112:
1113:
1114: /* Show Warning if collection method is net change, date range type is Absolute and history collection window is specified */
1117: AND p_collection_window IS NOT NULL)
1118: THEN
1119: x_retcode := 1;
1120: x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
1121: 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'));
1122: msd_dem_common_utilities.log_message (x_errbuf);
1123: END IF;
1124:
1125:
1118: THEN
1119: x_retcode := 1;
1120: x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
1121: 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'));
1122: msd_dem_common_utilities.log_message (x_errbuf);
1123: END IF;
1124:
1125:
1126: /* Error if collection method is net change, date range type is Rolling and history collection window is not specified */
1129: AND p_collection_window IS NULL)
1130: THEN
1131: retcode := -1;
1132: errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
1133: 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'));
1134: msd_dem_common_utilities.log_message (errbuf);
1135: RETURN;
1136: END IF;
1137:
1130: THEN
1131: retcode := -1;
1132: errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
1133: 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'));
1134: msd_dem_common_utilities.log_message (errbuf);
1135: RETURN;
1136: END IF;
1137:
1138:
1143: OR p_to_date IS NULL))
1144: THEN
1145: retcode := -1;
1146: errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
1147: 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'));
1148: msd_dem_common_utilities.log_message (errbuf);
1149: RETURN;
1150: END IF;
1151:
1144: THEN
1145: retcode := -1;
1146: errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
1147: 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'));
1148: msd_dem_common_utilities.log_message (errbuf);
1149: RETURN;
1150: END IF;
1151:
1152: /* Validate the order types specified by the user */
1163: IF (x_retcode1 = -1)
1164: THEN
1165: retcode := -1;
1166: errbuf := 'No valid order types found';
1167: 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'));
1168: msd_dem_common_utilities.log_message (errbuf);
1169: RETURN;
1170: ELSIF (x_invalid_count > 0)
1171: THEN
1164: THEN
1165: retcode := -1;
1166: errbuf := 'No valid order types found';
1167: 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'));
1168: msd_dem_common_utilities.log_message (errbuf);
1169: RETURN;
1170: ELSIF (x_invalid_count > 0)
1171: THEN
1172: x_retcode := 1;
1170: ELSIF (x_invalid_count > 0)
1171: THEN
1172: x_retcode := 1;
1173: x_errbuf := 'Invalid order types found';
1174: 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'));
1175: msd_dem_common_utilities.log_message (x_errbuf);
1176: ELSIF (x_retcode1 = 1)
1177: THEN
1178: x_retcode := 1;
1171: THEN
1172: x_retcode := 1;
1173: x_errbuf := 'Invalid order types found';
1174: 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'));
1175: msd_dem_common_utilities.log_message (x_errbuf);
1176: ELSIF (x_retcode1 = 1)
1177: THEN
1178: x_retcode := 1;
1179: x_errbuf := x_errbuf1;
1176: ELSIF (x_retcode1 = 1)
1177: THEN
1178: x_retcode := 1;
1179: x_errbuf := x_errbuf1;
1180: 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'));
1181: END IF;
1182:
1183: msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1184:
1179: x_errbuf := x_errbuf1;
1180: 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'));
1181: END IF;
1182:
1183: msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1184:
1185: /* VALIDATION OF INPUT PARAMETERS - END */
1186:
1187:
1186:
1187:
1188: /* Get the start date and end dates for collection */
1189:
1190: msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1191:
1192: IF (p_collection_method = 1) /* Refresh*/
1193: THEN
1194: x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
1215: IF (x_from_date > x_to_date)
1216: THEN
1217: retcode := -1;
1218: errbuf := 'From Date should not be greater than To Date.';
1219: 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'));
1220: msd_dem_common_utilities.log_message (errbuf);
1221: RETURN;
1222: END IF;
1223:
1216: THEN
1217: retcode := -1;
1218: errbuf := 'From Date should not be greater than To Date.';
1219: 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'));
1220: msd_dem_common_utilities.log_message (errbuf);
1221: RETURN;
1222: END IF;
1223:
1224: ELSE /* Rolling */
1226: IF (p_collection_window < 0)
1227: THEN
1228: retcode := -1;
1229: errbuf := 'History Collection Window must be a positive number.';
1230: 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'));
1231: msd_dem_common_utilities.log_message (errbuf);
1232: RETURN;
1233: ELSE
1234: x_to_date := trunc(sysdate);
1227: THEN
1228: retcode := -1;
1229: errbuf := 'History Collection Window must be a positive number.';
1230: 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'));
1231: msd_dem_common_utilities.log_message (errbuf);
1232: RETURN;
1233: ELSE
1234: x_to_date := trunc(sysdate);
1235: x_from_date := x_to_date - p_collection_window + 1;
1236: END IF;
1237: END IF;
1238: END IF;
1239:
1240: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1241: msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1242:
1243: /* Get the instance info */
1244: msd_dem_common_utilities.get_instance_info (
1237: END IF;
1238: END IF;
1239:
1240: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1241: msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1242:
1243: /* Get the instance info */
1244: msd_dem_common_utilities.get_instance_info (
1245: x_errbuf1,
1240: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1241: msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1242:
1243: /* Get the instance info */
1244: msd_dem_common_utilities.get_instance_info (
1245: x_errbuf1,
1246: x_retcode1,
1247: x_instance_code,
1248: x_apps_ver,
1253: IF (x_retcode1 = '-1')
1254: THEN
1255: retcode := -1;
1256: errbuf := x_errbuf1;
1257: 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'));
1258: msd_dem_common_utilities.log_message ('Unable to get instance info.');
1259: RETURN;
1260: END IF;
1261:
1254: THEN
1255: retcode := -1;
1256: errbuf := x_errbuf1;
1257: 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'));
1258: msd_dem_common_utilities.log_message ('Unable to get instance info.');
1259: RETURN;
1260: END IF;
1261:
1262: msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1258: msd_dem_common_utilities.log_message ('Unable to get instance info.');
1259: RETURN;
1260: END IF;
1261:
1262: msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1263:
1264: /* Get the sales staging table name */
1265: x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
1266:
1261:
1262: msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1263:
1264: /* Get the sales staging table name */
1265: x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
1266:
1267: IF (x_dest_table is NULL)
1268: THEN
1269: retcode := -1;
1267: IF (x_dest_table is NULL)
1268: THEN
1269: retcode := -1;
1270: errbuf := 'Unable to find the sales staging tables.';
1271: 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'));
1272: msd_dem_common_utilities.log_message (errbuf);
1273: RETURN;
1274: END IF;
1275:
1268: THEN
1269: retcode := -1;
1270: errbuf := 'Unable to find the sales staging tables.';
1271: 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'));
1272: msd_dem_common_utilities.log_message (errbuf);
1273: RETURN;
1274: END IF;
1275:
1276: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1272: msd_dem_common_utilities.log_message (errbuf);
1273: RETURN;
1274: END IF;
1275:
1276: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1277: msd_dem_common_utilities.log_message ('--------------------------------');
1278: msd_dem_common_utilities.log_message (' ');
1279:
1280: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1273: RETURN;
1274: END IF;
1275:
1276: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1277: msd_dem_common_utilities.log_message ('--------------------------------');
1278: msd_dem_common_utilities.log_message (' ');
1279:
1280: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1281: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1274: END IF;
1275:
1276: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1277: msd_dem_common_utilities.log_message ('--------------------------------');
1278: msd_dem_common_utilities.log_message (' ');
1279:
1280: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1281: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1282:
1276: msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1277: msd_dem_common_utilities.log_message ('--------------------------------');
1278: msd_dem_common_utilities.log_message (' ');
1279:
1280: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1281: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1282:
1283: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1284:
1277: msd_dem_common_utilities.log_message ('--------------------------------');
1278: msd_dem_common_utilities.log_message (' ');
1279:
1280: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1281: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1282:
1283: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1284:
1285: /* Truncate the sales staging table */
1279:
1280: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1281: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1282:
1283: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1284:
1285: /* Truncate the sales staging table */
1286: msd_dem_common_utilities.log_message ('Deleting data from sales staging table - ' || x_dest_table);
1287:
1282:
1283: msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1284:
1285: /* Truncate the sales staging table */
1286: msd_dem_common_utilities.log_message ('Deleting data from sales staging table - ' || x_dest_table);
1287:
1288: if p_collection_method = 1 then
1289: x_sql := 'TRUNCATE TABLE ' || x_dest_table;
1290: else
1314: EXECUTE IMMEDIATE x_sql;
1315:
1316: END IF;
1317:
1318: msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1319:
1320: -- bug#9327919 nallkuma
1321: l_schema_name := substr(x_dest_table , 1 , instr(x_dest_table, '.')-1) ;
1322: msd_dem_common_utilities.log_message('Fetched the schema name as : '||l_schema_name);
1318: msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1319:
1320: -- bug#9327919 nallkuma
1321: l_schema_name := substr(x_dest_table , 1 , instr(x_dest_table, '.')-1) ;
1322: msd_dem_common_utilities.log_message('Fetched the schema name as : '||l_schema_name);
1323:
1324: IF (l_schema_name <> 'MSD' ) then -- Bug#8721519 -- nallkuma
1325: msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1326: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1321: l_schema_name := substr(x_dest_table , 1 , instr(x_dest_table, '.')-1) ;
1322: msd_dem_common_utilities.log_message('Fetched the schema name as : '||l_schema_name);
1323:
1324: IF (l_schema_name <> 'MSD' ) then -- Bug#8721519 -- nallkuma
1325: msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1326: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1327:
1328: /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1329: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1322: msd_dem_common_utilities.log_message('Fetched the schema name as : '||l_schema_name);
1323:
1324: IF (l_schema_name <> 'MSD' ) then -- Bug#8721519 -- nallkuma
1325: msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1326: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1327:
1328: /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1329: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1330: x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1325: msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1326: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1327:
1328: /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1329: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1330: x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1331: EXECUTE IMMEDIATE x_sql;
1332: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1333: END IF;
1328: /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1329: msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1330: x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1331: EXECUTE IMMEDIATE x_sql;
1332: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1333: END IF;
1334:
1335: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1336: --saravan
1331: EXECUTE IMMEDIATE x_sql;
1332: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1333: END IF;
1334:
1335: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1336: --saravan
1337: msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1338:
1339: OPEN c_get_dm_schema; --jarora
1333: END IF;
1334:
1335: msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1336: --saravan
1337: msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1338:
1339: OPEN c_get_dm_schema; --jarora
1340: FETCH c_get_dm_schema INTO g_schema;
1341: CLOSE c_get_dm_schema;
1343: /* Get the lowest time bucket */
1344: /* Demantra is Installed */
1345: IF (g_schema IS NOT NULL) --jarora
1346: THEN
1347: x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
1348: ELSE
1349: x_dm_time_bucket := 'DAY';
1350: END IF;
1351:
1352: IF (x_dm_time_bucket IS NULL)
1353: THEN
1354: retcode := -1;
1355: errbuf := 'Unable to get lowest time bucket';
1356: 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'));
1357: msd_dem_common_utilities.log_message (errbuf);
1358: RETURN;
1359: ELSIF (upper(x_dm_time_bucket) = 'DAY')
1360: THEN
1353: THEN
1354: retcode := -1;
1355: errbuf := 'Unable to get lowest time bucket';
1356: 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'));
1357: msd_dem_common_utilities.log_message (errbuf);
1358: RETURN;
1359: ELSIF (upper(x_dm_time_bucket) = 'DAY')
1360: THEN
1361: x_dm_time_level := 1;
1362: ELSE
1363: x_dm_time_level := 2;
1364: END IF;
1365:
1366: msd_dem_common_utilities.log_debug ('End get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1367:
1368: /* Collect each series selected by the user */
1369:
1370: /* Booking History - Booked Items - Booked Date */
1367:
1368: /* Collect each series selected by the user */
1369:
1370: /* Booking History - Booked Items - Booked Date */
1371: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1372: IF (p_bh_bi_bd = G_YES)
1373: THEN
1374: collect_series_data (
1375: x_errbuf1,
1373: THEN
1374: collect_series_data (
1375: x_errbuf1,
1376: x_retcode1,
1377: MSD_DEM_COMMON_UTILITIES.C_BH_BI_BD,
1378: x_dest_table,
1379: x_dm_time_level,
1380: p_sr_instance_id,
1381: x_apps_ver,
1391: IF (x_retcode1 = -1)
1392: THEN
1393: retcode := -1;
1394: errbuf := x_errbuf1;
1395: 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'));
1396: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1397: RETURN;
1398: END IF;
1399: END IF;
1392: THEN
1393: retcode := -1;
1394: errbuf := x_errbuf1;
1395: 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'));
1396: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1397: RETURN;
1398: END IF;
1399: END IF;
1400: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1396: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1397: RETURN;
1398: END IF;
1399: END IF;
1400: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1401:
1402:
1403: /* Booking History - Booked Items - Requested Date */
1404: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1400: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1401:
1402:
1403: /* Booking History - Booked Items - Requested Date */
1404: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1405: IF (p_bh_bi_rd = G_YES)
1406: THEN
1407: collect_series_data (
1408: x_errbuf1,
1406: THEN
1407: collect_series_data (
1408: x_errbuf1,
1409: x_retcode1,
1410: MSD_DEM_COMMON_UTILITIES.C_BH_BI_RD,
1411: x_dest_table,
1412: x_dm_time_level,
1413: p_sr_instance_id,
1414: x_apps_ver,
1424: IF (x_retcode1 = -1)
1425: THEN
1426: retcode := -1;
1427: errbuf := x_errbuf1;
1428: 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'));
1429: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1430: RETURN;
1431: END IF;
1432: END IF;
1425: THEN
1426: retcode := -1;
1427: errbuf := x_errbuf1;
1428: 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'));
1429: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1430: RETURN;
1431: END IF;
1432: END IF;
1433: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1429: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1430: RETURN;
1431: END IF;
1432: END IF;
1433: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1434:
1435: /* Booking History - Requested Items - Booked Date */
1436: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1437: IF (p_bh_ri_bd = G_YES)
1432: END IF;
1433: msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1434:
1435: /* Booking History - Requested Items - Booked Date */
1436: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1437: IF (p_bh_ri_bd = G_YES)
1438: THEN
1439: collect_series_data (
1440: x_errbuf1,
1438: THEN
1439: collect_series_data (
1440: x_errbuf1,
1441: x_retcode1,
1442: MSD_DEM_COMMON_UTILITIES.C_BH_RI_BD,
1443: x_dest_table,
1444: x_dm_time_level,
1445: p_sr_instance_id,
1446: x_apps_ver,
1456: IF (x_retcode1 = -1)
1457: THEN
1458: retcode := -1;
1459: errbuf := x_errbuf1;
1460: 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'));
1461: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1462: RETURN;
1463: END IF;
1464: END IF;
1457: THEN
1458: retcode := -1;
1459: errbuf := x_errbuf1;
1460: 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'));
1461: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1462: RETURN;
1463: END IF;
1464: END IF;
1465: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1461: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1462: RETURN;
1463: END IF;
1464: END IF;
1465: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1466:
1467: /* Booking History - Requested Items - Requested Date */
1468: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1469: IF (p_bh_ri_rd = G_YES)
1464: END IF;
1465: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1466:
1467: /* Booking History - Requested Items - Requested Date */
1468: msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1469: IF (p_bh_ri_rd = G_YES)
1470: THEN
1471: collect_series_data (
1472: x_errbuf1,
1470: THEN
1471: collect_series_data (
1472: x_errbuf1,
1473: x_retcode1,
1474: MSD_DEM_COMMON_UTILITIES.C_BH_RI_RD,
1475: x_dest_table,
1476: x_dm_time_level,
1477: p_sr_instance_id,
1478: x_apps_ver,
1488: IF (x_retcode1 = -1)
1489: THEN
1490: retcode := -1;
1491: errbuf := x_errbuf1;
1492: 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'));
1493: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1494: RETURN;
1495: END IF;
1496: END IF;
1489: THEN
1490: retcode := -1;
1491: errbuf := x_errbuf1;
1492: 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'));
1493: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1494: RETURN;
1495: END IF;
1496: END IF;
1497: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1493: msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1494: RETURN;
1495: END IF;
1496: END IF;
1497: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1498:
1499: /* Shipment History - Shipped Items - Shipped Date */
1500: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1501: IF (p_sh_si_sd = G_YES)
1496: END IF;
1497: msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1498:
1499: /* Shipment History - Shipped Items - Shipped Date */
1500: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1501: IF (p_sh_si_sd = G_YES)
1502: THEN
1503: collect_series_data (
1504: x_errbuf1,
1502: THEN
1503: collect_series_data (
1504: x_errbuf1,
1505: x_retcode1,
1506: MSD_DEM_COMMON_UTILITIES.C_SH_SI_SD,
1507: x_dest_table,
1508: x_dm_time_level,
1509: p_sr_instance_id,
1510: x_apps_ver,
1520: IF (x_retcode1 = -1)
1521: THEN
1522: retcode := -1;
1523: errbuf := x_errbuf1;
1524: 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'));
1525: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1526: RETURN;
1527: END IF;
1528: END IF;
1521: THEN
1522: retcode := -1;
1523: errbuf := x_errbuf1;
1524: 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'));
1525: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1526: RETURN;
1527: END IF;
1528: END IF;
1529: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1525: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1526: RETURN;
1527: END IF;
1528: END IF;
1529: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1530:
1531: /* Shipment History - Shipped Items - Requested Date */
1532: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1533: IF (p_sh_si_rd = G_YES)
1528: END IF;
1529: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1530:
1531: /* Shipment History - Shipped Items - Requested Date */
1532: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1533: IF (p_sh_si_rd = G_YES)
1534: THEN
1535: collect_series_data (
1536: x_errbuf1,
1534: THEN
1535: collect_series_data (
1536: x_errbuf1,
1537: x_retcode1,
1538: MSD_DEM_COMMON_UTILITIES.C_SH_SI_RD,
1539: x_dest_table,
1540: x_dm_time_level,
1541: p_sr_instance_id,
1542: x_apps_ver,
1552: IF (x_retcode1 = -1)
1553: THEN
1554: retcode := -1;
1555: errbuf := x_errbuf1;
1556: 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'));
1557: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1558: RETURN;
1559: END IF;
1560: END IF;
1553: THEN
1554: retcode := -1;
1555: errbuf := x_errbuf1;
1556: 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'));
1557: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1558: RETURN;
1559: END IF;
1560: END IF;
1561: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1557: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1558: RETURN;
1559: END IF;
1560: END IF;
1561: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1562:
1563: /* Shipment History - Requested Items - Shipped Date */
1564: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1565: IF (p_sh_ri_sd = G_YES)
1560: END IF;
1561: msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1562:
1563: /* Shipment History - Requested Items - Shipped Date */
1564: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1565: IF (p_sh_ri_sd = G_YES)
1566: THEN
1567: collect_series_data (
1568: x_errbuf1,
1566: THEN
1567: collect_series_data (
1568: x_errbuf1,
1569: x_retcode1,
1570: MSD_DEM_COMMON_UTILITIES.C_SH_RI_SD,
1571: x_dest_table,
1572: x_dm_time_level,
1573: p_sr_instance_id,
1574: x_apps_ver,
1584: IF (x_retcode1 = -1)
1585: THEN
1586: retcode := -1;
1587: errbuf := x_errbuf1;
1588: 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'));
1589: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1590: RETURN;
1591: END IF;
1592: END IF;
1585: THEN
1586: retcode := -1;
1587: errbuf := x_errbuf1;
1588: 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'));
1589: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1590: RETURN;
1591: END IF;
1592: END IF;
1593: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1589: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1590: RETURN;
1591: END IF;
1592: END IF;
1593: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1594:
1595: /* Shipment History - Requested Items - Requested Date */
1596: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1597: IF (p_sh_ri_rd = G_YES)
1592: END IF;
1593: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1594:
1595: /* Shipment History - Requested Items - Requested Date */
1596: msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1597: IF (p_sh_ri_rd = G_YES)
1598: THEN
1599: collect_series_data (
1600: x_errbuf1,
1598: THEN
1599: collect_series_data (
1600: x_errbuf1,
1601: x_retcode1,
1602: MSD_DEM_COMMON_UTILITIES.C_SH_RI_RD,
1603: x_dest_table,
1604: x_dm_time_level,
1605: p_sr_instance_id,
1606: x_apps_ver,
1618: IF (x_retcode1 = -1)
1619: THEN
1620: retcode := -1;
1621: errbuf := x_errbuf1;
1622: 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'));
1623: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1624: RETURN;
1625: END IF;
1626: END IF;
1619: THEN
1620: retcode := -1;
1621: errbuf := x_errbuf1;
1622: 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'));
1623: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1624: RETURN;
1625: END IF;
1626: END IF;
1627: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1623: msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1624: RETURN;
1625: END IF;
1626: END IF;
1627: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1628:
1629: /* Bug# 5869314 - Insert dummy rows in the staging table for new items */
1630: 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'));
1631: insert_dummy_rows (
1626: END IF;
1627: msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1628:
1629: /* Bug# 5869314 - Insert dummy rows in the staging table for new items */
1630: 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'));
1631: insert_dummy_rows (
1632: x_errbuf1,
1633: x_retcode1,
1634: x_dest_table,
1637: IF (x_retcode1 = 1)
1638: THEN
1639: retcode := 1;
1640: errbuf := x_errbuf1;
1641: 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'));
1642: msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1643: END IF;
1644: 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'));
1645:
1638: THEN
1639: retcode := 1;
1640: errbuf := x_errbuf1;
1641: 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'));
1642: msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1643: END IF;
1644: 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'));
1645:
1646: COMMIT;
1640: errbuf := x_errbuf1;
1641: 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'));
1642: msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1643: END IF;
1644: 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'));
1645:
1646: COMMIT;
1647:
1648: /* Delete Internal Sales Orders in the same Line of Business */
1647:
1648: /* Delete Internal Sales Orders in the same Line of Business */
1649:
1650: if p_collect_iso = 1 then
1651: msd_dem_common_utilities.log_debug ('Begin Delete Internal Sales Orders in the same Line of Business - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1652: delete_internal_sales_orders(x_errbuf1,x_retcode1,p_sr_instance_id);
1653:
1654: IF (x_retcode1 = -1)
1655: THEN
1654: IF (x_retcode1 = -1)
1655: THEN
1656: retcode := -1;
1657: errbuf := x_errbuf1;
1658: 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'));
1659: msd_dem_common_utilities.log_message ('Error while deleting Internal Sales Orders in the same Line of Business');
1660: RETURN;
1661: END IF;
1662:
1655: THEN
1656: retcode := -1;
1657: errbuf := x_errbuf1;
1658: 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'));
1659: msd_dem_common_utilities.log_message ('Error while deleting Internal Sales Orders in the same Line of Business');
1660: RETURN;
1661: END IF;
1662:
1663: msd_dem_common_utilities.log_debug ('End Delete Internal Sales Orders in the same Line of Business - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1659: msd_dem_common_utilities.log_message ('Error while deleting Internal Sales Orders in the same Line of Business');
1660: RETURN;
1661: END IF;
1662:
1663: msd_dem_common_utilities.log_debug ('End Delete Internal Sales Orders in the same Line of Business - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1664: end if;
1665:
1666: /* For SPF, move the shipments data from T_SRC_SALES_TMPL to BIIO_SPF_SPARES_HIST_DATA */
1667: IF (p_for_spf = G_YES)
1666: /* For SPF, move the shipments data from T_SRC_SALES_TMPL to BIIO_SPF_SPARES_HIST_DATA */
1667: IF (p_for_spf = G_YES)
1668: THEN
1669:
1670: msd_dem_common_utilities.log_debug ('Begin processing for SPF - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1671: msd_dem_common_utilities.log_message ('Moving shipments data from T_SRC_SALES_TMPL to BIIO_SPF_SPARES_HIST_DATA...');
1672:
1673: msd_spf_collect_history_data.populate_staging_table (x_errbuf1, x_retcode1, 'EQ_SPF_SHIPMENTS', p_sr_instance_id);
1674: IF (x_retcode1 = -1)
1667: IF (p_for_spf = G_YES)
1668: THEN
1669:
1670: msd_dem_common_utilities.log_debug ('Begin processing for SPF - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1671: msd_dem_common_utilities.log_message ('Moving shipments data from T_SRC_SALES_TMPL to BIIO_SPF_SPARES_HIST_DATA...');
1672:
1673: msd_spf_collect_history_data.populate_staging_table (x_errbuf1, x_retcode1, 'EQ_SPF_SHIPMENTS', p_sr_instance_id);
1674: IF (x_retcode1 = -1)
1675: THEN
1674: IF (x_retcode1 = -1)
1675: THEN
1676: retcode := -1;
1677: errbuf := x_errbuf1;
1678: msd_dem_common_utilities.log_message ('Error(21): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1679: msd_dem_common_utilities.log_message ('Error in call to msd_spf_collect_history_data.populate_staging_table ');
1680: RETURN;
1681: END IF;
1682:
1675: THEN
1676: retcode := -1;
1677: errbuf := x_errbuf1;
1678: msd_dem_common_utilities.log_message ('Error(21): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1679: msd_dem_common_utilities.log_message ('Error in call to msd_spf_collect_history_data.populate_staging_table ');
1680: RETURN;
1681: END IF;
1682:
1683: msd_dem_common_utilities.log_message ('Truncating staging table T_SRC_SALES_TMPL...');
1679: msd_dem_common_utilities.log_message ('Error in call to msd_spf_collect_history_data.populate_staging_table ');
1680: RETURN;
1681: END IF;
1682:
1683: msd_dem_common_utilities.log_message ('Truncating staging table T_SRC_SALES_TMPL...');
1684: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dest_table;
1685:
1686: msd_dem_common_utilities.log_debug ('End processing for SPF - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1687:
1682:
1683: msd_dem_common_utilities.log_message ('Truncating staging table T_SRC_SALES_TMPL...');
1684: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dest_table;
1685:
1686: msd_dem_common_utilities.log_debug ('End processing for SPF - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1687:
1688: END IF;
1689:
1690: /* Call Custom Hook for History */
1688: END IF;
1689:
1690: /* Call Custom Hook for History */
1691:
1692: 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'));
1693:
1694: msd_dem_custom_hooks.history_hook (
1695: x_errbuf1,
1696: x_retcode1);
1694: msd_dem_custom_hooks.history_hook (
1695: x_errbuf1,
1696: x_retcode1);
1697:
1698: 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'));
1699:
1700: IF (x_retcode1 = -1)
1701: THEN
1702: retcode := -1;
1700: IF (x_retcode1 = -1)
1701: THEN
1702: retcode := -1;
1703: errbuf := x_errbuf1;
1704: msd_dem_common_utilities.log_message ('Error(20): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1705: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.history_hook ');
1706: RETURN;
1707: END IF;
1708:
1701: THEN
1702: retcode := -1;
1703: errbuf := x_errbuf1;
1704: msd_dem_common_utilities.log_message ('Error(20): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1705: msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.history_hook ');
1706: RETURN;
1707: END IF;
1708:
1709: /* Analyze Sales Staging Table */
1707: END IF;
1708:
1709: /* Analyze Sales Staging Table */
1710:
1711: msd_dem_common_utilities.log_debug ('Begin Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1712:
1713: analyze_table (
1714: x_errbuf1,
1715: x_retcode1,
1714: x_errbuf1,
1715: x_retcode1,
1716: x_dest_table);
1717:
1718: msd_dem_common_utilities.log_debug ('End Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1719:
1720: IF (x_retcode1 = 1)
1721: THEN
1722: retcode := 1;
1720: IF (x_retcode1 = 1)
1721: THEN
1722: retcode := 1;
1723: errbuf := x_errbuf1;
1724: 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'));
1725: msd_dem_common_utilities.log_message ('Error while analyzing sales staging table. ');
1726: END IF;
1727:
1728: /*
1721: THEN
1722: retcode := 1;
1723: errbuf := x_errbuf1;
1724: 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'));
1725: msd_dem_common_utilities.log_message ('Error while analyzing sales staging table. ');
1726: END IF;
1727:
1728: /*
1729: *Order Realignment
1737: if (g_schema is not null)
1738: then
1739:
1740: -- Bug#8224935 - APP ID
1741: l_profile_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1742: 'PROFILE_PURGE_HISTORY_DATA',
1743: 1,
1744: 'id'));
1745:
1747: execute immediate l_sql into l_table_name, l_start_date, l_until_date;
1748: -- syenamar
1749:
1750: /* Refreshing the Purge Series Data profile to the default value ie No load and No Purge option */
1751: msd_dem_common_utilities.REFRESH_PURGE_SERIES(x_errbuf1, x_retcode1, l_profile_id, g_schema);
1752:
1753: IF (x_retcode1 = -1)
1754: THEN
1755: retcode := -1;
1754: THEN
1755: retcode := -1;
1756: errbuf := x_errbuf1;
1757:
1758: msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
1759: END IF;
1760:
1761: /* Calling API to modify the data profile to purge selected series */
1762: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_ATTR - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1758: msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
1759: END IF;
1760:
1761: /* Calling API to modify the data profile to purge selected series */
1762: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_ATTR - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1763:
1764:
1765:
1766: if p_bh_bi_bd = G_YES
1765:
1766: if p_bh_bi_bd = G_YES
1767: then
1768: /* Bug#8224935 - APP ID */
1769: l_bh_bi_bd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1770: 'SERIES_EBS_BH_BOOK_QTY_BD',
1771: 1,
1772: 'forecast_type_id'));
1773:
1777:
1778: if p_bh_bi_rd = G_YES
1779: then
1780: /* Bug#8224935 - APP ID */
1781: l_bh_bi_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1782: 'SERIES_EBS_BH_BOOK_QTY_RD',
1783: 1,
1784: 'forecast_type_id'));
1785:
1789:
1790: if p_bh_ri_bd = G_YES
1791: then
1792: /* Bug#8224935 - APP ID */
1793: l_bh_ri_bd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1794: 'SERIES_EBS_BH_REQ_QTY_BD',
1795: 1,
1796: 'forecast_type_id'));
1797:
1801:
1802: if p_bh_ri_rd = G_YES
1803: then
1804: /* Bug#8224935 - APP ID */
1805: l_bh_ri_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1806: 'SERIES_EBS_BH_REQ_QTY_RD',
1807: 1,
1808: 'forecast_type_id'));
1809:
1813:
1814: if p_sh_si_sd = G_YES
1815: then
1816: /* Bug#8224935 - APP ID */
1817: l_sh_si_sd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1818: 'SERIES_EBS_SH_SHIP_QTY_SD',
1819: 1,
1820: 'forecast_type_id'));
1821:
1825:
1826: if p_sh_si_rd = G_YES
1827: then
1828: /* Bug#8224935 - APP ID */
1829: l_sh_si_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1830: 'SERIES_EBS_SH_SHIP_QTY_RD',
1831: 1,
1832: 'forecast_type_id'));
1833:
1837:
1838: if p_sh_ri_sd = G_YES
1839: then
1840: /* Bug#8224935 - APP ID */
1841: l_sh_ri_sd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1842: 'SERIES_SALES',
1843: 1,
1844: 'forecast_type_id'));
1845:
1849:
1850: if p_sh_ri_rd = G_YES
1851: then
1852: /* Bug#8224935 - APP ID */
1853: l_sh_ri_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1854: 'SERIES_EBS_SH_REQ_QTY_RD',
1855: 1,
1856: 'forecast_type_id'));
1857:
1861:
1862:
1863:
1864: /* Calling API to modify the data profile date range */
1865: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1866:
1867: l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
1868: execute immediate l_sql into x_from_date;
1869:
1873: if (x_from_date > x_to_date) then
1874: x_to_date := x_from_date;
1875: end if;
1876:
1877: msd_dem_common_utilities.log_message ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
1878:
1879: l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id||', '''|| x_from_date||''' , '''||x_to_date||'''); end;';
1880: execute immediate l_sql;
1881:
1880: execute immediate l_sql;
1881:
1882:
1883: /* Calling API to notify the application server to refresh its engine */
1884: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1885:
1886: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
1887: msd_dem_common_utilities.log_debug (l_sql);
1888: execute immediate l_sql;
1883: /* Calling API to notify the application server to refresh its engine */
1884: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1885:
1886: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
1887: msd_dem_common_utilities.log_debug (l_sql);
1888: execute immediate l_sql;
1889:
1890: l_sql := 'truncate table '|| g_schema ||'.'||l_table_name ;
1891: msd_dem_common_utilities.log_debug (l_sql);
1887: msd_dem_common_utilities.log_debug (l_sql);
1888: execute immediate l_sql;
1889:
1890: l_sql := 'truncate table '|| g_schema ||'.'||l_table_name ;
1891: msd_dem_common_utilities.log_debug (l_sql);
1892: execute immediate l_sql;
1893:
1894: l_sql := 'insert into '|| g_schema ||'.'||l_table_name||'(sdate, level1)'||
1895: 'select '''||x_from_date||''', teo.organization from '||g_schema||'.t_ep_organization teo '||
1903: ' AND mtp.sr_instance_id = mio.sr_instance_id ' ||
1904: ' AND mtp.sr_tp_id = mio.organization_id '||
1905: ' AND mtp.partner_type = 3) ';
1906:
1907: msd_dem_common_utilities.log_debug (l_sql);
1908:
1909: execute immediate l_sql;
1910:
1911: else
1908:
1909: execute immediate l_sql;
1910:
1911: else
1912: msd_dem_common_utilities.log_message('Demantra Schema not set');
1913: end if;
1914: EXCEPTION
1915: WHEN OTHERS THEN
1916: retcode := 1 ;
1914: EXCEPTION
1915: WHEN OTHERS THEN
1916: retcode := 1 ;
1917: errbuf := substr(SQLERRM,1,150);
1918: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1919: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1920: msd_dem_common_utilities.log_debug (errbuf);
1921: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1922:
1915: WHEN OTHERS THEN
1916: retcode := 1 ;
1917: errbuf := substr(SQLERRM,1,150);
1918: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1919: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1920: msd_dem_common_utilities.log_debug (errbuf);
1921: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1922:
1923: RETURN;
1916: retcode := 1 ;
1917: errbuf := substr(SQLERRM,1,150);
1918: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1919: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1920: msd_dem_common_utilities.log_debug (errbuf);
1921: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1922:
1923: RETURN;
1924: End;
1917: errbuf := substr(SQLERRM,1,150);
1918: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1919: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1920: msd_dem_common_utilities.log_debug (errbuf);
1921: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1922:
1923: RETURN;
1924: End;
1925:
1924: End;
1925:
1926: retcode := x_retcode;
1927: errbuf := x_errbuf;
1928: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1929:
1930: EXCEPTION
1931: WHEN OTHERS THEN
1932: retcode := -1 ;
1930: EXCEPTION
1931: WHEN OTHERS THEN
1932: retcode := -1 ;
1933: errbuf := substr(SQLERRM,1,150);
1934: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1935: msd_dem_common_utilities.log_message (errbuf);
1936: RETURN;
1937:
1938: END COLLECT_HISTORY_DATA;
1931: WHEN OTHERS THEN
1932: retcode := -1 ;
1933: errbuf := substr(SQLERRM,1,150);
1934: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1935: msd_dem_common_utilities.log_message (errbuf);
1936: RETURN;
1937:
1938: END COLLECT_HISTORY_DATA;
1939:
1960: ret_process_id varchar2(100);
1961:
1962:
1963: BEGIN
1964: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1965:
1966: DEM_SCHEMA := fnd_profile.value('MSD_DEM_SCHEMA');
1967:
1968: IF (p_auto_run_download = G_YES)
1975:
1976: l_stmt := 'alter session set current_schema=' || DEM_SCHEMA;
1977: execute immediate l_stmt;
1978:
1979: msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1980: msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1981:
1982: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1983: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1976: l_stmt := 'alter session set current_schema=' || DEM_SCHEMA;
1977: execute immediate l_stmt;
1978:
1979: msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1980: msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1981:
1982: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1983: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1984: execute immediate l_stmt;
1978:
1979: msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1980: msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1981:
1982: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1983: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1984: execute immediate l_stmt;
1985:
1986: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1982: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1983: l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1984: execute immediate l_stmt;
1985:
1986: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1987: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_ITEMS; end;';
1988: execute immediate l_stmt;
1989:
1990: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1986: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1987: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_ITEMS; end;';
1988: execute immediate l_stmt;
1989:
1990: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1991: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_LOCATION; end;';
1992: execute immediate l_stmt;
1993:
1994: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_SALES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1990: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1991: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_LOCATION; end;';
1992: execute immediate l_stmt;
1993:
1994: msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_SALES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1995: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_SALES; end;';
1996:
1997: execute immediate l_stmt;
1998:
1995: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_SALES; end;';
1996:
1997: execute immediate l_stmt;
1998:
1999: msd_dem_common_utilities.log_message ('End - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2000:
2001: commit;
2002:
2003: l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.LOG_EP_LOAD_SUCCESS; end;';
2009: */
2010:
2011:
2012: /* Bug#8224935 - APP ID */
2013: l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
2014: 'COMP_DM',
2015: 1,
2016: 'user_id'));
2017:
2021: execute immediate l_sql into l_user_name, l_password;
2022:
2023: ELSE
2024: /* Bug#8224935 - APP ID */
2025: l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
2026: 'COMP_SOP',
2027: 1,
2028: 'user_id'));
2029:
2031: then
2032: l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
2033: execute immediate l_sql into l_user_name, l_password;
2034: else
2035: msd_dem_common_utilities.log_message('Component is not found.');
2036: end if;
2037: END IF;
2038:
2039:
2044: -- Bug#7199587 syenamar
2045: -- Do not hard-code 'EBS Full Download' workflow name here. Get its ID from lookup, get its name from demantra schema using the ID.
2046:
2047: /* Bug#8224935 - APP ID */
2048: l_schema_name := msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
2049: 'WF_EBS_FULL_DOWNLOAD',
2050: 1,
2051: 'schema_name');
2052:
2054: l_sql := null;
2055: l_sql := 'SELECT
2056: utl_http.request('''||l_url||'/WorkflowServer?action=run_proc&user='||l_user_name||'&password='||l_password||'&schema='|| replace(l_schema_name, ' ', '%20') ||'&sync=no'') FROM dual';
2057:
2058: msd_dem_common_utilities.log_debug (l_sql);
2059: execute immediate l_sql into ret_process_id;
2060: msd_dem_common_utilities.log_message(' workflow process_id - ' || ret_process_id);
2061:
2062: /* BUG#6393839 */
2056: utl_http.request('''||l_url||'/WorkflowServer?action=run_proc&user='||l_user_name||'&password='||l_password||'&schema='|| replace(l_schema_name, ' ', '%20') ||'&sync=no'') FROM dual';
2057:
2058: msd_dem_common_utilities.log_debug (l_sql);
2059: execute immediate l_sql into ret_process_id;
2060: msd_dem_common_utilities.log_message(' workflow process_id - ' || ret_process_id);
2061:
2062: /* BUG#6393839 */
2063: -- Calls only if workflow status_check option is set to 'synchronous'
2064: IF (p_synchronous = 1) THEN
2062: /* BUG#6393839 */
2063: -- Calls only if workflow status_check option is set to 'synchronous'
2064: IF (p_synchronous = 1) THEN
2065:
2066: retcode := msd_dem_common_utilities.check_wf_status(ret_process_id,
2067: p_check_finish,p_time_out,dem_schema);
2068: END IF;
2069: /*------------------------*/
2070: else
2067: p_check_finish,p_time_out,dem_schema);
2068: END IF;
2069: /*------------------------*/
2070: else
2071: msd_dem_common_utilities.log_message('Error in launching the download workflow.');
2072: retcode := -1;
2073: Return;
2074: end if;
2075:
2073: Return;
2074: end if;
2075:
2076: else
2077: msd_dem_common_utilities.log_message('Demantra Schema not set');
2078: end if;
2079: ELSE
2080: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
2081: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
2076: else
2077: msd_dem_common_utilities.log_message('Demantra Schema not set');
2078: end if;
2079: ELSE
2080: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
2081: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
2082: END IF;
2083:
2084: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2077: msd_dem_common_utilities.log_message('Demantra Schema not set');
2078: end if;
2079: ELSE
2080: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
2081: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
2082: END IF;
2083:
2084: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2085:
2080: msd_dem_common_utilities.log_message ('Auto Run Download - No ');
2081: msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
2082: END IF;
2083:
2084: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2085:
2086: EXCEPTION
2087: WHEN OTHERS THEN
2088: errbuf := substr(SQLERRM,1,150);
2091: -- execute immediate l_stmt;
2092: -- l_stmt := 'alter session set current_schema=APPS';
2093: -- execute immediate l_stmt;
2094:
2095: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2096: msd_dem_common_utilities.log_message (errbuf);
2097: RETURN;
2098:
2099: END RUN_LOAD;
2092: -- l_stmt := 'alter session set current_schema=APPS';
2093: -- execute immediate l_stmt;
2094:
2095: msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2096: msd_dem_common_utilities.log_message (errbuf);
2097: RETURN;
2098:
2099: END RUN_LOAD;
2100: