[Home] [Help]
1: PACKAGE BODY MSD_SPF_COLLECT_HISTORY_DATA AS
2: /* $Header: msdspfchdb.pls 120.49.12020000.6 2012/11/22 13:33:08 nallkuma ship $ */
3:
4: /*** DATA TYPES ***/
5:
73:
74:
75: BEGIN
76:
77: msd_dem_common_utilities.log_debug ('Entering: MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SERIES_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
78:
79: OPEN c_get_series_info;
80: FETCH c_get_series_info INTO l_series_name, l_identifier, l_stg_series_col_name, l_custom_view_name;
81: CLOSE c_get_series_info;
82:
83: IF (l_identifier IS NULL) THEN
84: retcode := -1;
85: errbuf := 'Unable to get the query identifier.';
86: msd_dem_common_utilities.log_message ('Error(1): MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SERIES_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
87: msd_dem_common_utilities.log_message (errbuf);
88: RETURN;
89: END IF;
90:
173:
174: IF ( x_retcode1 = '-1' OR x_large_sql IS NULL) THEN
175: retcode := -1;
176: errbuf := 'Unable to get the query.';
177: msd_dem_common_utilities.log_message ('Error(2): MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SERIES_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
178: msd_dem_common_utilities.log_message (errbuf);
179: RETURN;
180: END IF;
181:
192: EXCEPTION
193: WHEN OTHERS THEN
194: retcode := -1 ;
195: errbuf := substr(SQLERRM,1,150);
196: msd_dem_common_utilities.log_message ('Exception(1): MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SERIES_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
197: msd_dem_common_utilities.log_message (errbuf);
198: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
199: RETURN;
200: END;
200: END;
201:
202: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
203: msd_dem_common_utilities.log_debug ('End collection of data for - ' || l_series_name || ' - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
204: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SERIES_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
205:
206: EXCEPTION
207: WHEN OTHERS THEN
208: retcode := -1 ;
206: EXCEPTION
207: WHEN OTHERS THEN
208: retcode := -1 ;
209: errbuf := substr(SQLERRM,1,150);
210: msd_dem_common_utilities.log_message ('Exception(3): MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SERIES_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
211: msd_dem_common_utilities.log_message (errbuf);
212: RETURN;
213:
214: END COLLECT_SERIES_DATA;
238: x_to_date date;
239: x_errbuf1 varchar2(200) := null;
240: x_retcode1 varchar2(100) := null;
241: BEGIN
242: msd_dem_common_utilities.log_debug ('Entering: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
243:
244: x_sql := 'select datet from '|| g_schema ||'.inputs where datet >= :1 and rownum = 1 order by datet asc';
245: execute immediate x_sql into x_from_date using p_from_date;
246:
395: msd_dem_common_utilities.log_debug(x_sql);
396: execute immediate x_sql;
397: END IF;
398:
399: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
400: EXCEPTION WHEN OTHERS THEN
401: retcode := 1 ;
402: errbuf := errbuf || substr(SQLERRM,1,150);
403: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
399: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
400: EXCEPTION WHEN OTHERS THEN
401: retcode := 1 ;
402: errbuf := errbuf || substr(SQLERRM,1,150);
403: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
404: msd_dem_common_utilities.log_debug (errbuf);
405: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
406: RETURN;
407: END SET_PURGE_PROFILES;
401: retcode := 1 ;
402: errbuf := errbuf || substr(SQLERRM,1,150);
403: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
404: msd_dem_common_utilities.log_debug (errbuf);
405: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
406: RETURN;
407: END SET_PURGE_PROFILES;
408:
409: PROCEDURE CHECK_REQUESTS_COMPLETION (
434: var_elapsed_seconds NUMBER DEFAULT NULL;
435:
436: BEGIN
437:
438: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.check_requests_completion...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
439:
440: var_start_date := TO_NUMBER(TO_CHAR(p_run_time, 'J'));
441: var_start_date_seconds := TO_NUMBER(TO_CHAR(p_run_time, 'SSSSS'));
442:
505: THEN
506:
507: retcode := -1;
508: errbuf := 'Timeout occured while waiting for requests to finish.';
509: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
510: msd_dem_common_utilities.log_message (errbuf);
511: RETURN;
512:
513: END IF;
517: IF (var_num_warning > 0)
518: THEN
519: retcode := 1;
520: errbuf := 'Some requests completed with warnings';
521: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
522: msd_dem_common_utilities.log_message (errbuf);
523: END IF;
524:
525: IF (var_num_error > 0)
525: IF (var_num_error > 0)
526: THEN
527: retcode := -1;
528: errbuf := 'Some requests completed with errors';
529: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
530: msd_dem_common_utilities.log_message (errbuf);
531: END IF;
532:
533: IF (var_num_complete <> (var_num_success + var_num_warning + var_num_error))
533: IF (var_num_complete <> (var_num_success + var_num_warning + var_num_error))
534: THEN
535: retcode := -1;
536: errbuf := 'Some requests completed with unknown status';
537: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
538: msd_dem_common_utilities.log_message (errbuf);
539: END IF;
540:
541: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.check_requests_completion...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
537: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
538: msd_dem_common_utilities.log_message (errbuf);
539: END IF;
540:
541: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.check_requests_completion...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
542:
543: EXCEPTION
544: WHEN OTHERS THEN
545: retcode := -1;
543: EXCEPTION
544: WHEN OTHERS THEN
545: retcode := -1;
546: errbuf := substr(SQLERRM,1,150);
547: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
548: msd_dem_common_utilities.log_message (errbuf);
549: RETURN;
550:
551: END CHECK_REQUESTS_COMPLETION;
604:
605:
606: BEGIN
607:
608: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.launch_spf_data_collection...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
609:
610: /* STAGE I - Push Setup Parameters *** BEGIN *** */
611: msd_dem_common_utilities.log_message('--------------------------------Stage I - Begin---------------------------------------');
612: var_request_num := 0;
619: EXCEPTION
620: WHEN OTHERS THEN
621: retcode := -1;
622: errbuf := substr(SQLERRM,1,150);
623: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
624: msd_dem_common_utilities.log_message ('Error while submitting request for push setup parameters');
625: msd_dem_common_utilities.log_message (errbuf);
626: RETURN;
627: END;
659: EXCEPTION
660: WHEN OTHERS THEN
661: retcode := -1;
662: errbuf := substr(SQLERRM,1,150);
663: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
664: msd_dem_common_utilities.log_message ('Error while submitting request for Usage Flat File Load');
665: msd_dem_common_utilities.log_message (errbuf);
666: RETURN;
667: END;
691: EXCEPTION
692: WHEN OTHERS THEN
693: retcode := -1;
694: errbuf := substr(SQLERRM,1,150);
695: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
696: msd_dem_common_utilities.log_message ('Error while submitting request for Shipment Flat File Load');
697: msd_dem_common_utilities.log_message (errbuf);
698: RETURN;
699: END;
716: IF (var_retcode = -1)
717: THEN
718: retcode := -1;
719: errbuf := var_errbuf;
720: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
721: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage I to complete.');
722: msd_dem_common_utilities.log_message (errbuf);
723: RETURN;
724: END IF;
725: IF (var_retcode = 1)
726: THEN
727: retcode := 1;
728: errbuf := var_errbuf;
729: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
730: msd_dem_common_utilities.log_message ('One or more requests in Stage I completed with warnings.');
731: msd_dem_common_utilities.log_message (errbuf);
732: END IF;
733:
756: EXCEPTION
757: WHEN OTHERS THEN
758: retcode := -1;
759: errbuf := substr(SQLERRM,1,150);
760: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
761: msd_dem_common_utilities.log_message ('Error while submitting request for loading consensus forecast to spf');
762: msd_dem_common_utilities.log_message (errbuf);
763: RETURN;
764: END;
792: EXCEPTION
793: WHEN OTHERS THEN
794: retcode := -1;
795: errbuf := substr(SQLERRM,1,150);
796: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
797: msd_dem_common_utilities.log_message ('Error while submitting request for collecting shipment history');
798: msd_dem_common_utilities.log_message (errbuf);
799: RETURN;
800: END;
825: EXCEPTION
826: WHEN OTHERS THEN
827: retcode := -1;
828: errbuf := substr(SQLERRM,1,150);
829: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
830: msd_dem_common_utilities.log_message ('Error while submitting request for collecting spare usage');
831: msd_dem_common_utilities.log_message (errbuf);
832: RETURN;
833: END;
882: EXCEPTION
883: WHEN OTHERS THEN
884: retcode := -1;
885: errbuf := substr(SQLERRM,1,150);
886: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
887: msd_dem_common_utilities.log_message ('Error while submitting request for Usage History Pre Process');
888: msd_dem_common_utilities.log_message (errbuf);
889: RETURN;
890: END;
914: EXCEPTION
915: WHEN OTHERS THEN
916: retcode := -1;
917: errbuf := substr(SQLERRM,1,150);
918: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
919: msd_dem_common_utilities.log_message ('Error while submitting request for deriving supersession chain');
920: msd_dem_common_utilities.log_message (errbuf);
921: RETURN;
922: END;
942: IF (var_retcode = -1)
943: THEN
944: retcode := -1;
945: errbuf := var_errbuf;
946: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
947: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage II to complete.');
948: msd_dem_common_utilities.log_message (errbuf);
949: RETURN;
950: END IF;
951: IF (var_retcode = 1)
952: THEN
953: retcode := 1;
954: errbuf := var_errbuf;
955: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
956: msd_dem_common_utilities.log_message ('One or more requests in Stage II completed with warnings.');
957: msd_dem_common_utilities.log_message (errbuf);
958: END IF;
959:
972:
973: IF (var_retcode = 1) THEN
974: retcode := 1;
975: errbuf := var_errbuf;
976: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.LAUNCH_SPF_DATA_COLLECTION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
977: msd_dem_common_utilities.log_message ('Error while analyzing staging table');
978: END IF;
979:
980: msd_dem_common_utilities.log_message('--------------------------------Stage II - End----------------------------------------');
1002: EXCEPTION
1003: WHEN OTHERS THEN
1004: retcode := -1;
1005: errbuf := substr(SQLERRM,1,150);
1006: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1007: msd_dem_common_utilities.log_message ('Error while submitting request for collecting install base under contracts');
1008: msd_dem_common_utilities.log_message (errbuf);
1009: RETURN;
1010: END;
1032: EXCEPTION
1033: WHEN OTHERS THEN
1034: retcode := -1;
1035: errbuf := substr(SQLERRM,1,150);
1036: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1037: msd_dem_common_utilities.log_message ('Error while submitting request for derive service bom');
1038: msd_dem_common_utilities.log_message (errbuf);
1039: RETURN;
1040: END;
1060: IF (var_retcode = -1)
1061: THEN
1062: retcode := -1;
1063: errbuf := var_errbuf;
1064: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1065: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage III to complete.');
1066: msd_dem_common_utilities.log_message (errbuf);
1067: RETURN;
1068: END IF;
1069: IF (var_retcode = 1)
1070: THEN
1071: retcode := 1;
1072: errbuf := var_errbuf;
1073: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1074: msd_dem_common_utilities.log_message ('One or more requests in Stage III completed with warnings.');
1075: msd_dem_common_utilities.log_message (errbuf);
1076: END IF;
1077:
1103: EXCEPTION
1104: WHEN OTHERS THEN
1105: retcode := -1;
1106: errbuf := substr(SQLERRM,1,150);
1107: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1108: msd_dem_common_utilities.log_message ('Error while submitting request for populating BIIO_SPF_BASE_MODEL');
1109: msd_dem_common_utilities.log_message (errbuf);
1110: RETURN;
1111: END;
1130: EXCEPTION
1131: WHEN OTHERS THEN
1132: retcode := -1;
1133: errbuf := substr(SQLERRM,1,150);
1134: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1135: msd_dem_common_utilities.log_message ('Error while submitting request for populating BIIO_SPF_CHILD');
1136: msd_dem_common_utilities.log_message (errbuf);
1137: RETURN;
1138: END;
1157: EXCEPTION
1158: WHEN OTHERS THEN
1159: retcode := -1;
1160: errbuf := substr(SQLERRM,1,150);
1161: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1162: msd_dem_common_utilities.log_message ('Error while submitting request for populating BIIO_SPF_LATEST_REVISION');
1163: msd_dem_common_utilities.log_message (errbuf);
1164: RETURN;
1165: END;
1194: EXCEPTION
1195: WHEN OTHERS THEN
1196: retcode := -1;
1197: errbuf := substr(SQLERRM,1,150);
1198: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1199: msd_dem_common_utilities.log_message ('Error while submitting request for populating BIIO_SPF_LEVEL');
1200: msd_dem_common_utilities.log_message (errbuf);
1201: RETURN;
1202: END;
1221: EXCEPTION
1222: WHEN OTHERS THEN
1223: retcode := -1;
1224: errbuf := substr(SQLERRM,1,150);
1225: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1226: msd_dem_common_utilities.log_message ('Error while submitting request for populating T_SRC_SALES_TMPL');
1227: msd_dem_common_utilities.log_message (errbuf);
1228: RETURN;
1229: END;
1248: EXCEPTION
1249: WHEN OTHERS THEN
1250: retcode := -1;
1251: errbuf := substr(SQLERRM,1,150);
1252: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1253: msd_dem_common_utilities.log_message ('Error while submitting request for populating BIIO_SPF_DATA');
1254: msd_dem_common_utilities.log_message (errbuf);
1255: RETURN;
1256: END;
1277: IF (var_retcode = -1)
1278: THEN
1279: retcode := -1;
1280: errbuf := var_errbuf;
1281: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1282: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage IV to complete.');
1283: msd_dem_common_utilities.log_message (errbuf);
1284: RETURN;
1285: END IF;
1286: IF (var_retcode = 1)
1287: THEN
1288: retcode := 1;
1289: errbuf := var_errbuf;
1290: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1291: msd_dem_common_utilities.log_message ('One or more requests in Stage IV completed with warnings.');
1292: msd_dem_common_utilities.log_message (errbuf);
1293: END IF;
1294:
1320: EXCEPTION
1321: WHEN OTHERS THEN
1322: retcode := -1;
1323: errbuf := substr(SQLERRM,1,150);
1324: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1325: msd_dem_common_utilities.log_message ('Error while submitting request for populating BIIO_SPF_POPULATION');
1326: msd_dem_common_utilities.log_message (errbuf);
1327: RETURN;
1328: END;
1347: EXCEPTION
1348: WHEN OTHERS THEN
1349: retcode := -1;
1350: errbuf := substr(SQLERRM,1,150);
1351: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1352: msd_dem_common_utilities.log_message ('Error while submitting request for populating T_SRC_ITEM_TMPL');
1353: msd_dem_common_utilities.log_message (errbuf);
1354: RETURN;
1355: END;
1374: EXCEPTION
1375: WHEN OTHERS THEN
1376: retcode := -1;
1377: errbuf := substr(SQLERRM,1,150);
1378: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1379: msd_dem_common_utilities.log_message ('Error while submitting request for populating T_SRC_LOC_TMPL');
1380: msd_dem_common_utilities.log_message (errbuf);
1381: RETURN;
1382: END;
1402: IF (var_retcode = -1)
1403: THEN
1404: retcode := -1;
1405: errbuf := var_errbuf;
1406: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1407: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage V to complete.');
1408: msd_dem_common_utilities.log_message (errbuf);
1409: RETURN;
1410: END IF;
1411: IF (var_retcode = 1)
1412: THEN
1413: retcode := 1;
1414: errbuf := var_errbuf;
1415: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1416: msd_dem_common_utilities.log_message ('One or more requests in Stage V completed with warnings.');
1417: msd_dem_common_utilities.log_message (errbuf);
1418: END IF;
1419:
1512: exception
1513: when others then
1514: retcode := -1;
1515: errbuf := substr(SQLERRM,1,150);
1516: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1517: msd_dem_common_utilities.log_message ('Error when setting Load/Purge option for data profile');
1518: msd_dem_common_utilities.log_message (errbuf);
1519: RETURN;
1520: end;
1538: EXCEPTION
1539: WHEN OTHERS THEN
1540: retcode := -1;
1541: errbuf := substr(SQLERRM,1,150);
1542: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1543: msd_dem_common_utilities.log_message ('Error while submitting request for launch download');
1544: msd_dem_common_utilities.log_message (errbuf);
1545: RETURN;
1546: END;
1566: IF (var_retcode = -1)
1567: THEN
1568: retcode := -1;
1569: errbuf := var_errbuf;
1570: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1571: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage VI to complete.');
1572: msd_dem_common_utilities.log_message (errbuf);
1573: RETURN;
1574: END IF;
1575: IF (var_retcode = 1)
1576: THEN
1577: retcode := 1;
1578: errbuf := var_errbuf;
1579: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1580: msd_dem_common_utilities.log_message ('One or more requests in Stage VI completed with warnings.');
1581: msd_dem_common_utilities.log_message (errbuf);
1582: END IF;
1583:
1588: msd_dem_common_utilities.log_message ('');
1589: /* STAGE VI *** END *** */
1590:
1591:
1592: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.launch_spf_data_collection...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1593:
1594:
1595: EXCEPTION
1596: WHEN OTHERS THEN
1595: EXCEPTION
1596: WHEN OTHERS THEN
1597: retcode := -1;
1598: errbuf := substr(SQLERRM,1,150);
1599: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_data_collection - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1600: msd_dem_common_utilities.log_message (errbuf);
1601: RETURN;
1602:
1603: END LAUNCH_SPF_DATA_COLLECTION;
1630: var_count NUMBER DEFAULT NULL;
1631:
1632: BEGIN
1633:
1634: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.launch_spf_shipment_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1635:
1636: /* STAGE I - Load Shipment Flat File *** BEGIN *** */
1637: msd_dem_common_utilities.log_message('--------------------------------Stage I - Begin---------------------------------------');
1638: var_request_num := 0;
1647: EXCEPTION
1648: WHEN OTHERS THEN
1649: retcode := -1;
1650: errbuf := substr(SQLERRM,1,150);
1651: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1652: msd_dem_common_utilities.log_message ('Error while submitting request for Flat File Loads');
1653: msd_dem_common_utilities.log_message (errbuf);
1654: RETURN;
1655: END;
1680: EXCEPTION
1681: WHEN OTHERS THEN
1682: retcode := -1;
1683: errbuf := substr(SQLERRM,1,150);
1684: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1685: msd_dem_common_utilities.log_message ('Error while submitting request for push setup parameters');
1686: msd_dem_common_utilities.log_message (errbuf);
1687: RETURN;
1688: END;
1704: IF (var_retcode = -1)
1705: THEN
1706: retcode := -1;
1707: errbuf := var_errbuf;
1708: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1709: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage I to complete.');
1710: msd_dem_common_utilities.log_message (errbuf);
1711: RETURN;
1712: END IF;
1713: IF (var_retcode = 1)
1714: THEN
1715: retcode := 1;
1716: errbuf := var_errbuf;
1717: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1718: msd_dem_common_utilities.log_message ('One or more requests in Stage I completed with warnings.');
1719: msd_dem_common_utilities.log_message (errbuf);
1720: END IF;
1721:
1758: IF (var_retcode = -1)
1759: THEN
1760: retcode := -1;
1761: errbuf := var_errbuf;
1762: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1763: msd_dem_common_utilities.log_message ('Error in call to msd_dem_collect_history_data.analyze_table');
1764: RETURN;
1765: END IF;
1766:
1790: EXCEPTION
1791: WHEN OTHERS THEN
1792: retcode := -1;
1793: errbuf := substr(SQLERRM,1,150);
1794: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1795: msd_dem_common_utilities.log_message ('Error while submitting request for launch download');
1796: msd_dem_common_utilities.log_message (errbuf);
1797: RETURN;
1798: END;
1818: IF (var_retcode = -1)
1819: THEN
1820: retcode := -1;
1821: errbuf := var_errbuf;
1822: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1823: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage III to complete.');
1824: msd_dem_common_utilities.log_message (errbuf);
1825: RETURN;
1826: END IF;
1827: IF (var_retcode = 1)
1828: THEN
1829: retcode := 1;
1830: errbuf := var_errbuf;
1831: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1832: msd_dem_common_utilities.log_message ('One or more requests in Stage III completed with warnings.');
1833: msd_dem_common_utilities.log_message (errbuf);
1834: END IF;
1835:
1839: msd_dem_common_utilities.log_message ('');
1840: msd_dem_common_utilities.log_message ('');
1841: /* STAGE III - Launch Download *** END *** */
1842:
1843: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.launch_spf_shipment_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1844:
1845: EXCEPTION
1846: WHEN OTHERS THEN
1847: retcode := -1;
1845: EXCEPTION
1846: WHEN OTHERS THEN
1847: retcode := -1;
1848: errbuf := substr(SQLERRM,1,150);
1849: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_spf_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1850: msd_dem_common_utilities.log_message (errbuf);
1851: RETURN;
1852:
1853: END LAUNCH_SPF_SHIPMENT_DATA_FF;
1900:
1901:
1902: BEGIN
1903:
1904: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.collect_spf_data...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1905:
1906: /* Log the input parameters to the log file */
1907: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
1908:
1959: IF (var_retcode = -1)
1960: THEN
1961: retcode := -1;
1962: errbuf := substr(SQLERRM,1,150);
1963: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1964: msd_dem_common_utilities.log_message ('Error in call to msd_dem_common_utilities.get_instance_info');
1965: msd_dem_common_utilities.log_message (errbuf);
1966: RETURN;
1967: END IF;
1969: IF (var_instance_type <> 3 AND var_apps_ver <5)
1970: THEN
1971: retcode := -1;
1972: errbuf := 'Source Version ' || to_char(var_apps_ver) || ' is not supported';
1973: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1974: msd_dem_common_utilities.log_message (errbuf);
1975: RETURN;
1976: END IF;
1977:
1978: IF (var_instance_type = 3 AND (p_shipment_data = G_YES OR p_usage_data = G_YES))
1979: THEN
1980: retcode := -1;
1981: errbuf := 'This program does not support legacy load for Shipment and Usage History. Please use the respective self service options.';
1982: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1983: msd_dem_common_utilities.log_message (errbuf);
1984: RETURN;
1985: END IF;
1986:
1993: IF (var_retcode = -1)
1994: THEN
1995: retcode := -1;
1996: errbuf := substr(SQLERRM,1,150);
1997: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1998: msd_dem_common_utilities.log_message ('Error in call to msd_dem_common_utilities.get_dblink');
1999: msd_dem_common_utilities.log_message (errbuf);
2000: RETURN;
2001: END IF;
2005: IF (p_shipment_data = G_NO AND p_usage_data = G_NO AND p_install_base = G_NO AND p_sales_forecast = G_NO)
2006: THEN
2007: retcode := -1;
2008: errbuf := 'No series selected for collection. Please select atleast one series for collection.';
2009: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2010: msd_dem_common_utilities.log_message (errbuf);
2011: RETURN;
2012: END IF;
2013:
2018: IF (p_collection_method = 1 AND (p_collection_window IS NOT NULL OR p_from_date IS NOT NULL OR p_to_date IS NOT NULL))
2019: THEN
2020: retcode := -1;
2021: errbuf := 'For Collection Method - Complete, the date range filters should not be specified. If you want to run collection for a specific date range please select Net-change mode';
2022: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2023: msd_dem_common_utilities.log_message (errbuf);
2024: RETURN;
2025: END IF;
2026:
2028: IF (p_collection_method = 2 AND p_date_range_type = 2 AND p_collection_window IS NULL)
2029: THEN
2030: retcode := -1;
2031: errbuf := 'For Collection Method - Net Change and Date Range Type - Rolling, the field History Collection Window in mandatory.';
2032: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2033: msd_dem_common_utilities.log_message (errbuf);
2034: RETURN;
2035: END IF;
2036:
2038: IF (p_collection_method = 2 AND p_date_range_type = 1 AND (p_from_date IS NULL OR p_to_date IS NULL))
2039: THEN
2040: retcode := -1;
2041: errbuf := 'For Collection Method - Net Change and Date Range Type - Absolute, the fields From Date and To Date are mandatory.';
2042: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2043: msd_dem_common_utilities.log_message (errbuf);
2044: RETURN;
2045: END IF;
2046:
2048: IF (p_collection_method = 2 AND p_date_range_type = 2 AND (p_from_date IS NOT NULL OR p_to_date IS NOT NULL))
2049: THEN
2050: retcode := -1;
2051: errbuf := 'For Collection Method - Net Change and Date Range Type - Rolling, the fields From Date and To Date should not be specified. Only the field History Collection Window should be specified';
2052: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2053: msd_dem_common_utilities.log_message (errbuf);
2054: RETURN;
2055: END IF;
2056:
2058: IF (p_collection_method = 2 AND p_date_range_type = 1 AND p_collection_window IS NOT NULL)
2059: THEN
2060: retcode := -1;
2061: errbuf := 'For Collection Method - Net Change and Date Range Type - Absolute, the field History Collection Window should not be specified. Only the fields From Date and To Date should be specified';
2062: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2063: msd_dem_common_utilities.log_message (errbuf);
2064: RETURN;
2065: END IF;
2066:
2071: IF (G_SCHEMA IS NULL)
2072: THEN
2073: retcode := -1;
2074: errbuf := 'Profile MSD_DEM Schema is not set.';
2075: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2076: msd_dem_common_utilities.log_message (errbuf);
2077: RETURN;
2078: END IF;
2079:
2105: IF (var_from_date > var_to_date)
2106: THEN
2107: retcode := -1;
2108: errbuf := 'From Date should not be greater than To Date';
2109: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2110: msd_dem_common_utilities.log_message (errbuf);
2111: RETURN;
2112: END IF;
2113:
2117: IF (p_collection_window < 1)
2118: THEN
2119: retcode := -1;
2120: errbuf := 'History Collection Window must be a positive number';
2121: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2122: msd_dem_common_utilities.log_message (errbuf);
2123: RETURN;
2124: END IF;
2125:
2142: IF (var_retcode = -1)
2143: THEN
2144: retcode := -1;
2145: errbuf := var_errbuf;
2146: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2147: msd_dem_common_utilities.log_message ('Error in call to truncate_spf_staging');
2148: RETURN;
2149: END IF;
2150:
2148: RETURN;
2149: END IF;
2150:
2151: /* Call the main program to run the spf data collection in various stages */
2152: msd_dem_common_utilities.log_debug ('Calling msd_spf_collect_history_data.launch_spf_data_collection...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2153: launch_spf_data_collection (var_errbuf,
2154: var_retcode,
2155: var_runtime,
2156: p_sr_instance_id,
2182: IF (var_retcode = -1)
2183: THEN
2184: retcode := -1;
2185: errbuf := var_errbuf;
2186: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_spf_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2187: msd_dem_common_utilities.log_message ('Error in call to launch_spf_data_collection');
2188: RETURN;
2189: END IF;
2190:
2187: msd_dem_common_utilities.log_message ('Error in call to launch_spf_data_collection');
2188: RETURN;
2189: END IF;
2190:
2191: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.collect_spf_data...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2192:
2193: END COLLECT_SPF_DATA;
2194:
2195:
2213: var_wf_schema_name VARCHAR2(255) DEFAULT NULL;
2214:
2215: BEGIN
2216:
2217: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.launch_download...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2218:
2219: /* Log the input parameters to the log file */
2220: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
2221:
2247: IF (var_user_id IS NULL)
2248: THEN
2249: retcode := -1;
2250: errbuf := 'Unable to get dm component user';
2251: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_download - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2252: msd_dem_common_utilities.log_message (errbuf);
2253: RETURN;
2254: ELSE
2255: msd_dem_common_utilities.log_debug('User Id - ' || to_char(var_user_id));
2265: IF (var_user_name IS NULL or var_user_password IS NULL)
2266: THEN
2267: retcode := -1;
2268: errbuf := 'Unable to get user details';
2269: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_download - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2270: msd_dem_common_utilities.log_message (errbuf);
2271: RETURN;
2272: END IF;
2273:
2277: IF (var_url IS NULL)
2278: THEN
2279: retcode := -1;
2280: errbuf := 'Unable to get host url';
2281: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_download - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2282: msd_dem_common_utilities.log_message (errbuf);
2283: RETURN;
2284: ELSE
2285: msd_dem_common_utilities.log_debug('Host URL - <' || var_url || '>');
2291: IF (var_wf_schema_name IS NULL)
2292: THEN
2293: retcode := -1;
2294: errbuf := 'Unable to get workflow schema name';
2295: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_download - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2296: msd_dem_common_utilities.log_message (errbuf);
2297: RETURN;
2298: END IF;
2299:
2312: THEN
2313:
2314: retcode := -1;
2315: errbuf := var_ret_val;
2316: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_download - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2317: msd_dem_common_utilities.log_message ('Failed to launch download workflow');
2318: msd_dem_common_utilities.log_message (errbuf);
2319: RETURN;
2320:
2329: EXCEPTION
2330: WHEN OTHERS THEN
2331: retcode := -1;
2332: errbuf := substr(SQLERRM,1,150);
2333: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_download - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2334: msd_dem_common_utilities.log_message ('Error while launching download workflow');
2335: msd_dem_common_utilities.log_message (errbuf);
2336: RETURN;
2337: END;
2335: msd_dem_common_utilities.log_message (errbuf);
2336: RETURN;
2337: END;
2338:
2339: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.launch_download...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2340:
2341: EXCEPTION
2342: WHEN OTHERS THEN
2343: retcode := -1;
2341: EXCEPTION
2342: WHEN OTHERS THEN
2343: retcode := -1;
2344: errbuf := substr(SQLERRM,1,150);
2345: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.launch_download - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2346: msd_dem_common_utilities.log_message (errbuf);
2347: RETURN;
2348:
2349: END LAUNCH_DOWNLOAD;
2364: var_retcode VARCHAR2(10) DEFAULT NULL;
2365:
2366: BEGIN
2367:
2368: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.truncate_spf_cmro_staging...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2369:
2370: truncate_spf_staging(var_errbuf, var_retcode);
2371: IF (var_retcode = -1) THEN
2372: retcode := -1;
2370: truncate_spf_staging(var_errbuf, var_retcode);
2371: IF (var_retcode = -1) THEN
2372: retcode := -1;
2373: errbuf := var_errbuf;
2374: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.truncate_spf_cmro_staging - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2375: msd_dem_common_utilities.log_message ('Error in call to truncate_spf_staging');
2376: RETURN;
2377: END IF;
2378:
2450: msd_dem_common_utilities.log_message ('');
2451: msd_dem_common_utilities.log_debug ('');
2452: msd_dem_common_utilities.log_debug ('');
2453:
2454: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.truncate_spf_cmro_staging...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2455:
2456: EXCEPTION
2457: WHEN OTHERS THEN
2458: retcode := -1;
2456: EXCEPTION
2457: WHEN OTHERS THEN
2458: retcode := -1;
2459: errbuf := substr(SQLERRM,1,150);
2460: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.truncate_spf_cmro_staging - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2461: msd_dem_common_utilities.log_message (errbuf);
2462: RETURN;
2463:
2464: END TRUNCATE_SPF_CMRO_STAGING;
2477: var_table_name VARCHAR2(100) DEFAULT NULL;
2478:
2479: BEGIN
2480:
2481: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.truncate_spf_staging...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2482:
2483: /* Get the MSD schema name */
2484: var_retval := fnd_installation.get_app_info ('MSD', var_dummy1, var_dummy2, var_msd_schema_name);
2485:
2630: msd_dem_common_utilities.log_message ('');
2631: msd_dem_common_utilities.log_debug ('');
2632: msd_dem_common_utilities.log_debug ('');
2633:
2634: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.truncate_spf_staging...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2635:
2636: EXCEPTION
2637: WHEN OTHERS THEN
2638: retcode := -1;
2636: EXCEPTION
2637: WHEN OTHERS THEN
2638: retcode := -1;
2639: errbuf := substr(SQLERRM,1,150);
2640: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.truncate_spf_staging - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2641: msd_dem_common_utilities.log_message (errbuf);
2642: RETURN;
2643:
2644: END TRUNCATE_SPF_STAGING;
2685:
2686:
2687: BEGIN
2688:
2689: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.collect_install_base_uc_data...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2690:
2691: /* Log the input parameters to the log file */
2692: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
2693:
2751: OR var_ps_view_name IS NULL)
2752: THEN
2753: retcode := -1;
2754: errbuf := 'Unable to get series information for Install Base under Contracts from table msd_dem_series';
2755: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_install_base_uc_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2756: msd_dem_common_utilities.log_message (errbuf);
2757: RETURN;
2758: END IF;
2759:
2791: IF (var_retcode = -1)
2792: THEN
2793: retcode := -1;
2794: errbuf := 'Error in call to msd_dem_query_utilities.get_query3';
2795: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_install_base_uc_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2796: msd_dem_common_utilities.log_message (errbuf);
2797: RETURN;
2798: END IF;
2799:
2807: EXCEPTION
2808: WHEN OTHERS THEN
2809: retcode := -1;
2810: errbuf := substr(SQLERRM,1,150);
2811: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_install_base_uc_data - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2812: msd_dem_common_utilities.log_message ('Error while executing query');
2813: msd_dem_common_utilities.log_message (errbuf);
2814: RETURN;
2815: END;
2815: END;
2816: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2817:
2818:
2819: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.collect_install_base_uc_data...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2820:
2821: EXCEPTION
2822: WHEN OTHERS THEN
2823: retcode := -1;
2821: EXCEPTION
2822: WHEN OTHERS THEN
2823: retcode := -1;
2824: errbuf := substr(SQLERRM,1,150);
2825: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_install_base_uc_data - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2826: msd_dem_common_utilities.log_message (errbuf);
2827: RETURN;
2828:
2829: END COLLECT_INSTALL_BASE_UC_DATA;
2841: var_is_present NUMBER DEFAULT NULL;
2842: var_key_value_pairs VARCHAR2(500) DEFAULT NULL;
2843: BEGIN
2844:
2845: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.populate_staging_table...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2846:
2847: /* Log the input parameters to the log file */
2848: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
2849:
2873: p_sr_instance_id,
2874: p_key_value_pairs );
2875: IF (retcode = -1)
2876: THEN
2877: msd_dem_common_utilities.log_message ('Error(3) in msd_spf_collect_history_data.populate_staging_table - '
2878: || 'Error in call to msd_dem_query_utilities.execute_query');
2879: msd_dem_common_utilities.log_message(errbuf);
2880: RETURN;
2881: ELSE
2887:
2888: /* Post-Process End */
2889:
2890: retcode := 0;
2891: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.populate_staging_table...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2892:
2893: EXCEPTION
2894: WHEN OTHERS THEN
2895: retcode := -1;
2893: EXCEPTION
2894: WHEN OTHERS THEN
2895: retcode := -1;
2896: errbuf := substr(SQLERRM,1,150);
2897: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.populate_staging_table - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2898: msd_dem_common_utilities.log_message (errbuf);
2899: RETURN;
2900:
2901: END POPULATE_STAGING_TABLE;
2913: type rctype is ref cursor; -- Bug#12675064
2914: rctvar rctype;
2915: x_item varchar2(240) := null;
2916: BEGIN
2917: msd_dem_common_utilities.log_message('Begin - MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SPARE_SUPERSESSIONS');
2918:
2919: -- Bug#12675064
2920: x_category_set_id := MSC_UTIL.GET_SERVICE_ITEMS_CATSET_ID;
2921: x_sql := 'select to_number(parameter_value) from msd_dem_setup_parameters ' ||
3046: msd_dem_common_utilities.log_message('Derivation of Supersession chains complete');
3047: msd_dem_common_utilities.log_debug('End - Derivation - ' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') );
3048:
3049: retcode := 0;
3050: msd_dem_common_utilities.log_message('End - MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SPARE_SUPERSESSIONS');
3051: EXCEPTION
3052: WHEN OTHERS THEN
3053: retcode := -1;
3054: errbuf := substr(sqlerrm, 1, 200);
3051: EXCEPTION
3052: WHEN OTHERS THEN
3053: retcode := -1;
3054: errbuf := substr(sqlerrm, 1, 200);
3055: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SPARE_SUPERSESSIONS - ' || errbuf);
3056: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SPARE_SUPERSESSIONS (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3057: END DERIVE_SPARE_SUPERSESSIONS;
3058:
3059: PROCEDURE LOAD_DM_FORECAST_FOR_SPF (
3052: WHEN OTHERS THEN
3053: retcode := -1;
3054: errbuf := substr(sqlerrm, 1, 200);
3055: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SPARE_SUPERSESSIONS - ' || errbuf);
3056: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SPARE_SUPERSESSIONS (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3057: END DERIVE_SPARE_SUPERSESSIONS;
3058:
3059: PROCEDURE LOAD_DM_FORECAST_FOR_SPF (
3060: errbuf OUT NOCOPY VARCHAR2,
3072: x_sql varchar2(1100) := null;
3073: x_sales_fcst_stg varchar2(100) := null;
3074:
3075: BEGIN
3076: msd_dem_common_utilities.log_message('Begin - MSD_SPF_COLLECT_HISTORY_DATA.LOAD_DM_FORECAST_FOR_SPF');
3077:
3078: /* check if Sales Forecast download is selected */
3079: if( p_load_sales_fcst = G_NO ) then
3080: msd_dem_common_utilities.log_message('Load Sales Forecast not selected. Exiting.');
3165: end if;
3166: end if;
3167:
3168: retcode := 0;
3169: msd_dem_common_utilities.log_message('End - MSD_SPF_COLLECT_HISTORY_DATA.LOAD_DM_FORECAST_FOR_SPF');
3170: EXCEPTION
3171: WHEN OTHERS THEN
3172: retcode := -1;
3173: errbuf := substr(sqlerrm, 1, 200);
3170: EXCEPTION
3171: WHEN OTHERS THEN
3172: retcode := -1;
3173: errbuf := substr(sqlerrm, 1, 200);
3174: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.LOAD_DM_FORECAST_FOR_SPF - ' || errbuf);
3175: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.LOAD_DM_FORECAST_FOR_SPF (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3176: END LOAD_DM_FORECAST_FOR_SPF;
3177:
3178: PROCEDURE COLLECT_SPARE_USAGE_HISTORY (
3171: WHEN OTHERS THEN
3172: retcode := -1;
3173: errbuf := substr(sqlerrm, 1, 200);
3174: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.LOAD_DM_FORECAST_FOR_SPF - ' || errbuf);
3175: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.LOAD_DM_FORECAST_FOR_SPF (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3176: END LOAD_DM_FORECAST_FOR_SPF;
3177:
3178: PROCEDURE COLLECT_SPARE_USAGE_HISTORY (
3179: errbuf OUT NOCOPY VARCHAR2,
3241:
3242: IF (x_retcode = -1) THEN
3243: retcode := -1;
3244: errbuf := x_errbuf;
3245: msd_dem_common_utilities.log_message ('Error(11): MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_USAGE_HISTORY - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3246: msd_dem_common_utilities.log_message ('Error while collecting Spare Usage History');
3247: RETURN;
3248: END IF;
3249:
3264:
3265: IF (x_retcode = 1) THEN
3266: retcode := 1;
3267: errbuf := x_errbuf;
3268: msd_dem_common_utilities.log_message ('Warning(7): MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_USAGE_HISTORY - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3269: msd_dem_common_utilities.log_message ('Error while analyzing staging table');
3270: END IF;*/
3271:
3272: END IF;
3270: END IF;*/
3271:
3272: END IF;
3273:
3274: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_USAGE_HISTORY - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3275: EXCEPTION
3276: WHEN OTHERS THEN
3277: retcode := -1;
3278: errbuf := substr(sqlerrm, 1, 200);
3275: EXCEPTION
3276: WHEN OTHERS THEN
3277: retcode := -1;
3278: errbuf := substr(sqlerrm, 1, 200);
3279: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_USAGE_HISTORY - ' || errbuf);
3280: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_USAGE_HISTORY (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3281: END COLLECT_SPARE_USAGE_HISTORY;
3282:
3283: -- Bug#12675064
3276: WHEN OTHERS THEN
3277: retcode := -1;
3278: errbuf := substr(sqlerrm, 1, 200);
3279: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_USAGE_HISTORY - ' || errbuf);
3280: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_USAGE_HISTORY (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3281: END COLLECT_SPARE_USAGE_HISTORY;
3282:
3283: -- Bug#12675064
3284: PROCEDURE COLLECT_SPARE_SHIPMENT_HISTORY (
3339:
3340: IF (x_retcode = -1) THEN
3341: retcode := -1;
3342: errbuf := x_errbuf;
3343: msd_dem_common_utilities.log_message ('Error(1): MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_SHIPMENT_HISTORY - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3344: msd_dem_common_utilities.log_message ('Error while collecting Spare Shipment History');
3345: RETURN;
3346: END IF;
3347:
3346: END IF;
3347:
3348: msd_dem_common_utilities.log_debug ('End collect Spare Shipment History - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3349:
3350: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_SHIPMENT_HISTORY - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3351: EXCEPTION
3352: WHEN OTHERS THEN
3353: retcode := -1;
3354: errbuf := substr(sqlerrm, 1, 200);
3351: EXCEPTION
3352: WHEN OTHERS THEN
3353: retcode := -1;
3354: errbuf := substr(sqlerrm, 1, 200);
3355: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_SHIPMENT_HISTORY - ' || errbuf);
3356: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_SHIPMENT_HISTORY (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3357: END COLLECT_SPARE_SHIPMENT_HISTORY;
3358:
3359: PROCEDURE DERIVE_SERVICE_BOM (
3352: WHEN OTHERS THEN
3353: retcode := -1;
3354: errbuf := substr(sqlerrm, 1, 200);
3355: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_SHIPMENT_HISTORY - ' || errbuf);
3356: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SPARE_SHIPMENT_HISTORY (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3357: END COLLECT_SPARE_SHIPMENT_HISTORY;
3358:
3359: PROCEDURE DERIVE_SERVICE_BOM (
3360: errbuf OUT NOCOPY VARCHAR2,
3394:
3395: l_req_table REQ_TABLE;
3396:
3397: BEGIN
3398: msd_dem_common_utilities.log_message('Begin - MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM');
3399:
3400: x_category_set_id := MSC_UTIL.GET_SERVICE_ITEMS_CATSET_ID;
3401: x_sql := 'select to_number(parameter_value) from msd_dem_setup_parameters ' ||
3402: ' where parameter_name like ''MSD_SPF_MASTER_ORG''';
3485: EXCEPTION
3486: WHEN OTHERS THEN
3487: retcode := -1;
3488: errbuf := substr(SQLERRM,1,150);
3489: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3490: msd_dem_common_utilities.log_message ('Error while submitting request for populating MSD_SPF_MODEL_BOM_COMPONENTS');
3491: msd_dem_common_utilities.log_message (errbuf);
3492: RETURN;
3493: END;
3521: EXCEPTION
3522: WHEN OTHERS THEN
3523: retcode := -1;
3524: errbuf := substr(SQLERRM,1,150);
3525: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3526: msd_dem_common_utilities.log_message ('Error while submitting request for populating MSD_SPF_MODEL_ORGS');
3527: msd_dem_common_utilities.log_message (errbuf);
3528: RETURN;
3529: END;
3544: IF (var_retcode = -1)
3545: THEN
3546: retcode := -1;
3547: errbuf := var_errbuf;
3548: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3549: msd_dem_common_utilities.log_message ('Error while waiting for requests to complete.');
3550: msd_dem_common_utilities.log_message (errbuf);
3551: RETURN;
3552: END IF;
3563:
3564: IF (var_retcode = 1) THEN
3565: retcode := 1;
3566: errbuf := var_errbuf;
3567: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3568: msd_dem_common_utilities.log_message ('Error while analyzing MSD_SPF_MODEL_BOM_COMPONENTS table');
3569: END IF;
3570:
3571: /* Populate MSD_SPF_MFG_BOM_TEMP with data from MFG BOM for products from MSD_SPF_MODEL_ORGS (FG-Latest Spare-Dates) */
3810: EXCEPTION
3811: WHEN OTHERS THEN
3812: retcode := -1;
3813: errbuf := substr(sqlerrm, 1, 200);
3814: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM - ' || errbuf);
3815: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3816: END DERIVE_SERVICE_BOM;
3817:
3818:
3811: WHEN OTHERS THEN
3812: retcode := -1;
3813: errbuf := substr(sqlerrm, 1, 200);
3814: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM - ' || errbuf);
3815: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.DERIVE_SERVICE_BOM (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
3816: END DERIVE_SERVICE_BOM;
3817:
3818:
3819:
3838: var_retcode VARCHAR2(100) DEFAULT 0;
3839:
3840: BEGIN
3841:
3842: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.collect_shipment_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3843:
3844: /* Log the input parameters to the log file */
3845: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
3846:
3875: IF (var_retcode = -1)
3876: THEN
3877: retcode := -1;
3878: errbuf := var_errbuf;
3879: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3880: msd_dem_common_utilities.log_message ('Error in call to truncate_spf_staging');
3881: RETURN;
3882: END IF;
3883:
3882: END IF;
3883:
3884:
3885: /* Call the main program to run the spf shipment data collection in various stages */ -- Bug#12675064
3886: /*msd_dem_common_utilities.log_debug ('Calling msd_spf_collect_history_data.launch_spf_shipment_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3887: launch_spf_shipment_data_ff (var_errbuf,
3888: var_retcode,
3889: var_runtime,
3890: p_sr_instance_id,
3897: p_time_out,
3898: p_synchronous);*/
3899:
3900: ---- Bug#12675064
3901: msd_dem_common_utilities.log_debug ('Calling msd_spf_collect_history_data.launch_spf_data_collection...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3902: launch_spf_data_collection (var_errbuf,
3903: var_retcode,
3904: var_runtime,
3905: p_sr_instance_id,
3928: IF (var_retcode = -1)
3929: THEN
3930: retcode := -1;
3931: errbuf := var_errbuf;
3932: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_shipment_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3933: msd_dem_common_utilities.log_message ('Error in call to launch_spf_shipment_data_ff');
3934: RETURN;
3935: END IF;
3936: IF (var_retcode = 1)
3936: IF (var_retcode = 1)
3937: THEN
3938: retcode := 1;
3939: errbuf := var_errbuf;
3940: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_shipment_data_ff - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3941: msd_dem_common_utilities.log_message ('Warning in call to launch_spf_shipment_data_ff');
3942: RETURN;
3943: END IF;
3944:
3941: msd_dem_common_utilities.log_message ('Warning in call to launch_spf_shipment_data_ff');
3942: RETURN;
3943: END IF;
3944:
3945: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.collect_shipment_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3946:
3947: EXCEPTION
3948: WHEN OTHERS THEN
3949: retcode := -1;
3947: EXCEPTION
3948: WHEN OTHERS THEN
3949: retcode := -1;
3950: errbuf := substr(SQLERRM,1,150);
3951: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_shipment_data_ff - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3952: msd_dem_common_utilities.log_message (errbuf);
3953: RETURN;
3954:
3955: END COLLECT_SHIPMENT_DATA_FF;
3977: var_retcode VARCHAR2(100) DEFAULT 0;
3978:
3979: BEGIN
3980:
3981: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.collect_usage_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3982:
3983: /* Log the input parameters to the log file */
3984: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
3985:
4014: IF (var_retcode = -1)
4015: THEN
4016: retcode := -1;
4017: errbuf := var_errbuf;
4018: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4019: msd_dem_common_utilities.log_message ('Error in call to truncate_spf_staging');
4020: RETURN;
4021: END IF;
4022:
4020: RETURN;
4021: END IF;
4022:
4023: /* Call the main program to run the spf data collection in various stages */
4024: msd_dem_common_utilities.log_debug ('Calling msd_spf_collect_history_data.collect_usage_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4025: launch_spf_data_collection (var_errbuf,
4026: var_retcode,
4027: var_runtime,
4028: p_sr_instance_id,
4050: IF (var_retcode = -1)
4051: THEN
4052: retcode := -1;
4053: errbuf := var_errbuf;
4054: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4055: msd_dem_common_utilities.log_message ('Error in call to launch_spf_data_collection');
4056: RETURN;
4057: END IF;
4058: IF (var_retcode = 1)
4058: IF (var_retcode = 1)
4059: THEN
4060: retcode := 1;
4061: errbuf := var_errbuf;
4062: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4063: msd_dem_common_utilities.log_message ('Warning in call to launch_spf_data_collection');
4064: END IF;
4065:
4066: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.collect_usage_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4062: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4063: msd_dem_common_utilities.log_message ('Warning in call to launch_spf_data_collection');
4064: END IF;
4065:
4066: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.collect_usage_data_ff...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4067:
4068: EXCEPTION
4069: WHEN OTHERS THEN
4070: retcode := -1;
4068: EXCEPTION
4069: WHEN OTHERS THEN
4070: retcode := -1;
4071: errbuf := substr(SQLERRM,1,150);
4072: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4073: msd_dem_common_utilities.log_message (errbuf);
4074: RETURN;
4075:
4076: END COLLECT_USAGE_DATA_FF;
4098: AND rownum < 10000;
4099:
4100: BEGIN
4101:
4102: msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.usage_data_pre_process...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4103:
4104: /* Log the input parameters to the log file */
4105: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
4106:
4120: THEN
4121:
4122: retcode := -1;
4123: errbuf := 'Unable to find SPF Master Organization. Hence exiting with error.';
4124: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.usage_data_pre_process - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4125: msd_dem_common_utilities.log_message (errbuf);
4126: RETURN;
4127:
4128: END IF;
4134: IF (var_total_rows = 0)
4135: THEN
4136: retcode := -1;
4137: errbuf := 'No rows found in msd_spf_st_spare_usage';
4138: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.collect_usage_data_ff - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4139: msd_dem_common_utilities.log_message (errbuf);
4140: RETURN;
4141: ELSE
4142: msd_dem_common_utilities.log_message('Total rows in msd_spf_st_spare_usage - ' || to_char(var_total_rows));
4309: THEN
4310:
4311: retcode := -1;
4312: errbuf := 'All the rows are invalid. Hence exiting with error.';
4313: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.usage_data_pre_process - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4314:
4315: ELSE
4316:
4317: var_good_rows := var_total_rows - var_bad_rows;
4322:
4323: msd_dem_common_utilities.log_message('Total invalid rows - ' || to_char(var_bad_rows));
4324: msd_dem_common_utilities.log_message ('');
4325: msd_dem_common_utilities.log_message ('');
4326: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.usage_data_pre_process - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4327: msd_dem_common_utilities.log_message ('Invalid rows found in usage history flat file load.');
4328: retcode := 1;
4329: errbuf := 'Invalid rows found in usage history flat file load.';
4330:
4378: msd_dem_common_utilities.log_message ('');
4379: msd_dem_common_utilities.log_message ('');
4380:
4381:
4382: msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.usage_data_pre_process...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4383:
4384: EXCEPTION
4385: WHEN OTHERS THEN
4386: retcode := -1;
4384: EXCEPTION
4385: WHEN OTHERS THEN
4386: retcode := -1;
4387: errbuf := substr(SQLERRM,1,150);
4388: msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.usage_data_pre_process - EXCEPTION ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4389: msd_dem_common_utilities.log_message (errbuf);
4390: RETURN;
4391:
4392: END USAGE_DATA_PRE_PROCESS;
4417:
4418: IF (x_retcode = -1) THEN
4419: retcode := -1;
4420: errbuf := x_errbuf;
4421: msd_dem_common_utilities.log_message ('Error: MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_SERIES_DATA_REQ - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4422: msd_dem_common_utilities.log_message ('Error in call to COLLECT_SERIES_DATA');
4423: RETURN;
4424: END IF;
4425: END COLLECT_SERIES_DATA_REQ;
4446: x_to_date date;
4447: x_errbuf1 varchar2(200) := null;
4448: x_retcode1 varchar2(100) := null;
4449: BEGIN
4450: msd_dem_common_utilities.log_debug ('Entering: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4451:
4452: x_sql := 'select datet from '|| g_schema ||'.inputs where datet >= :1 and rownum = 1 order by datet asc';
4453: execute immediate x_sql into x_from_date using p_from_date;
4454:
4712: msd_dem_common_utilities.log_debug(x_sql);
4713: execute immediate x_sql;
4714: END IF;
4715:
4716: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4717: EXCEPTION WHEN OTHERS THEN
4718: retcode := 1 ;
4719: errbuf := errbuf || substr(SQLERRM,1,150);
4720: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4716: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4717: EXCEPTION WHEN OTHERS THEN
4718: retcode := 1 ;
4719: errbuf := errbuf || substr(SQLERRM,1,150);
4720: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4721: msd_dem_common_utilities.log_debug (errbuf);
4722: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4723: RETURN;
4724: END SET_CMROEAM_PURGE_PROFILES;
4718: retcode := 1 ;
4719: errbuf := errbuf || substr(SQLERRM,1,150);
4720: msd_dem_common_utilities.log_message ('Warning: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4721: msd_dem_common_utilities.log_debug (errbuf);
4722: msd_dem_common_utilities.log_debug ('Exiting: MSD_SPF_COLLECT_HISTORY_DATA.SET_CMROEAM_PURGE_PROFILES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4723: RETURN;
4724: END SET_CMROEAM_PURGE_PROFILES;
4725:
4726:
4760:
4761: var_param_value varchar2(240) := null;
4762: var_key_value_pairs VARCHAR2(500) DEFAULT NULL;
4763: BEGIN
4764: msd_dem_common_utilities.log_debug ('Entering MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4765:
4766: --validate parameters
4767: msd_dem_common_utilities.ui_params_validation(x_errbuf, x_retcode,
4768: x_from_date, x_to_date,
4779:
4780: IF (x_retcode = -1) THEN
4781: retcode := -1;
4782: errbuf := x_errbuf;
4783: msd_dem_common_utilities.log_message ('Error: MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4784: msd_dem_common_utilities.log_message ('Error validating params for COLLECT_CMRO_DATA');
4785: RETURN;
4786: END IF;
4787:
4793: x_src_dblink);
4794: IF (x_retcode = -1) THEN
4795: retcode := -1;
4796: errbuf := substr(SQLERRM,1,150);
4797: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4798: msd_dem_common_utilities.log_message ('Error in call to MSD_DEM_COMMON_UTILITIES.GET_DBLINK');
4799: msd_dem_common_utilities.log_message (errbuf);
4800: RETURN;
4801: END IF;
4808: x_dest_dblink);
4809: IF (x_retcode = -1) THEN
4810: retcode := -1;
4811: errbuf := substr(SQLERRM,1,150);
4812: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4813: msd_dem_common_utilities.log_message ('Error in call to MSD_DEM_COMMON_UTILITIES.GET_DEST_DBLINK');
4814: msd_dem_common_utilities.log_message (errbuf);
4815: RETURN;
4816: END IF;
4819: truncate_spf_cmro_staging(x_errbuf, x_retcode);
4820: IF (x_retcode = -1) THEN
4821: retcode := -1;
4822: errbuf := x_errbuf;
4823: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4824: msd_dem_common_utilities.log_message ('Error in call to TRUNCATE_SPF_CMRO_STAGING');
4825: RETURN;
4826: END IF;
4827:
4861:
4862: IF (x_retcode = -1) THEN
4863: retcode := -1;
4864: errbuf := x_errbuf;
4865: msd_dem_common_utilities.log_message ('ERROR(1) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4866: msd_dem_common_utilities.log_message (errbuf);
4867: RETURN;
4868: END IF;
4869: EXCEPTION
4869: EXCEPTION
4870: WHEN OTHERS THEN
4871: retcode := -1;
4872: errbuf := substr(SQLERRM,1,200);
4873: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4874: msd_dem_common_utilities.log_message ('Error while submitting collection request on source');
4875: msd_dem_common_utilities.log_message (errbuf);
4876: RETURN;
4877: END;
4893: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out, x_src_dblink);
4894: IF (x_retcode = -1) THEN
4895: retcode := -1;
4896: errbuf := x_errbuf;
4897: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4898: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage I to complete.');
4899: msd_dem_common_utilities.log_message (errbuf);
4900: RETURN;
4901: END IF;
4902:
4903: IF (x_retcode = 1) THEN
4904: retcode := 1;
4905: errbuf := x_errbuf;
4906: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4907: msd_dem_common_utilities.log_message ('One or more requests in Stage I completed with warnings.');
4908: msd_dem_common_utilities.log_message (errbuf);
4909: END IF;
4910:
4942: EXCEPTION
4943: WHEN OTHERS THEN
4944: retcode := -1;
4945: errbuf := substr(SQLERRM,1,150);
4946: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4947: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_BOM');
4948: msd_dem_common_utilities.log_message (errbuf);
4949: RETURN;
4950: END;
4970: EXCEPTION
4971: WHEN OTHERS THEN
4972: retcode := -1;
4973: errbuf := substr(SQLERRM,1,150);
4974: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4975: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_SALES_TMPL');
4976: msd_dem_common_utilities.log_message (errbuf);
4977: RETURN;
4978: END;
4998: EXCEPTION
4999: WHEN OTHERS THEN
5000: retcode := -1;
5001: errbuf := substr(SQLERRM,1,150);
5002: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5003: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_DATA');
5004: msd_dem_common_utilities.log_message (errbuf);
5005: RETURN;
5006: END;
5039: EXCEPTION
5040: WHEN OTHERS THEN
5041: retcode := -1;
5042: errbuf := substr(SQLERRM,1,150);
5043: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5044: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_ITEM_TMPL');
5045: msd_dem_common_utilities.log_message (errbuf);
5046: RETURN;
5047: END;
5063: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out);
5064: IF (x_retcode = -1) THEN
5065: retcode := -1;
5066: errbuf := x_errbuf;
5067: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5068: msd_dem_common_utilities.log_message ('Error while waiting for requests for Step1 of Stage II to complete.');
5069: msd_dem_common_utilities.log_message (errbuf);
5070: RETURN;
5071: END IF;
5072:
5073: IF (x_retcode = 1) THEN
5074: retcode := 1;
5075: errbuf := x_errbuf;
5076: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5077: msd_dem_common_utilities.log_message ('One or more requests in Step1 of Stage II completed with warnings.');
5078: msd_dem_common_utilities.log_message (errbuf);
5079: END IF;
5080:
5100: EXCEPTION
5101: WHEN OTHERS THEN
5102: retcode := -1;
5103: errbuf := substr(SQLERRM,1,150);
5104: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5105: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_BASE_MODEL');
5106: msd_dem_common_utilities.log_message (errbuf);
5107: RETURN;
5108: END;
5128: EXCEPTION
5129: WHEN OTHERS THEN
5130: retcode := -1;
5131: errbuf := substr(SQLERRM,1,150);
5132: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5133: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_CHILD');
5134: msd_dem_common_utilities.log_message (errbuf);
5135: RETURN;
5136: END;
5156: EXCEPTION
5157: WHEN OTHERS THEN
5158: retcode := -1;
5159: errbuf := substr(SQLERRM,1,150);
5160: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5161: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_VISIT_TYPE');
5162: msd_dem_common_utilities.log_message (errbuf);
5163: RETURN;
5164: END;
5184: EXCEPTION
5185: WHEN OTHERS THEN
5186: retcode := -1;
5187: errbuf := substr(SQLERRM,1,150);
5188: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5189: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_VISIT_STAGE_TYPE');
5190: msd_dem_common_utilities.log_message (errbuf);
5191: RETURN;
5192: END;
5212: EXCEPTION
5213: WHEN OTHERS THEN
5214: retcode := -1;
5215: errbuf := substr(SQLERRM,1,150);
5216: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5217: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_MAINTENANCE_TYPE');
5218: msd_dem_common_utilities.log_message (errbuf);
5219: RETURN;
5220: END;
5240: EXCEPTION
5241: WHEN OTHERS THEN
5242: retcode := -1;
5243: errbuf := substr(SQLERRM,1,150);
5244: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5245: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_LEVEL');
5246: msd_dem_common_utilities.log_message (errbuf);
5247: RETURN;
5248: END;
5267: EXCEPTION
5268: WHEN OTHERS THEN
5269: retcode := -1;
5270: errbuf := substr(SQLERRM,1,150);
5271: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5272: msd_dem_common_utilities.log_message ('Error while submitting request for for Collect Level Types - Location');
5273: msd_dem_common_utilities.log_message (errbuf);
5274: RETURN;
5275: END;
5291: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out);
5292: IF (x_retcode = -1) THEN
5293: retcode := -1;
5294: errbuf := x_errbuf;
5295: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5296: msd_dem_common_utilities.log_message ('Error while waiting for requests for Step2 of Stage II to complete.');
5297: msd_dem_common_utilities.log_message (errbuf);
5298: RETURN;
5299: END IF;
5300:
5301: IF (x_retcode = 1) THEN
5302: retcode := 1;
5303: errbuf := x_errbuf;
5304: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5305: msd_dem_common_utilities.log_message ('One or more requests in Step2 of Stage II completed with warnings.');
5306: msd_dem_common_utilities.log_message (errbuf);
5307: END IF;
5308:
5368: EXCEPTION
5369: WHEN OTHERS THEN
5370: retcode := -1;
5371: errbuf := substr(SQLERRM,1,150);
5372: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5373: msd_dem_common_utilities.log_message ('Error while submitting request for populating EQ_SPF_CMRO_POPULATION');
5374: msd_dem_common_utilities.log_message (errbuf);
5375: RETURN;
5376: END;
5392: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out);
5393: IF (x_retcode = -1) THEN
5394: retcode := -1;
5395: errbuf := x_errbuf;
5396: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5397: msd_dem_common_utilities.log_message ('Error while waiting for requests for Step3 of Stage II to complete.');
5398: msd_dem_common_utilities.log_message (errbuf);
5399: RETURN;
5400: END IF;
5401:
5402: IF (x_retcode = 1) THEN
5403: retcode := 1;
5404: errbuf := x_errbuf;
5405: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5406: msd_dem_common_utilities.log_message ('One or more requests in Step3 of Stage II completed with warnings.');
5407: msd_dem_common_utilities.log_message (errbuf);
5408: END IF;
5409:
5423: set_cmroeam_purge_profiles(x_errbuf, x_retcode, 'CMRO', x_from_date, x_to_date);
5424: IF (x_retcode = -1) THEN
5425: retcode := -1;
5426: errbuf := x_errbuf;
5427: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5428: msd_dem_common_utilities.log_message ('Error when setting purge profiles.');
5429: msd_dem_common_utilities.log_message (errbuf);
5430: RETURN;
5431: END IF;
5432:
5433: IF (x_retcode = 1) THEN
5434: retcode := 1;
5435: errbuf := x_errbuf;
5436: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5437: msd_dem_common_utilities.log_message ('Warning when setting purge profiles.');
5438: msd_dem_common_utilities.log_message (errbuf);
5439: END IF;
5440: /* End Step4: Set purge profiles */
5464: EXCEPTION
5465: WHEN OTHERS THEN
5466: retcode := -1;
5467: errbuf := substr(SQLERRM,1,150);
5468: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5469: msd_dem_common_utilities.log_message ('Error while submitting request for launch download');
5470: msd_dem_common_utilities.log_message (errbuf);
5471: RETURN;
5472: END;
5489: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out);
5490: IF (x_retcode = -1) THEN
5491: retcode := -1;
5492: errbuf := x_errbuf;
5493: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5494: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage III to complete.');
5495: msd_dem_common_utilities.log_message (errbuf);
5496: RETURN;
5497: END IF;
5498:
5499: IF (x_retcode = 1) THEN
5500: retcode := 1;
5501: errbuf := x_errbuf;
5502: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5503: msd_dem_common_utilities.log_message ('One or more requests in Stage III completed with warnings.');
5504: msd_dem_common_utilities.log_message (errbuf);
5505: END IF;
5506:
5510: msd_dem_common_utilities.log_message ('');
5511: msd_dem_common_utilities.log_message ('');
5512: /* STAGE III *** END *** */
5513:
5514: msd_dem_common_utilities.log_debug ('Exiting MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5515: EXCEPTION
5516: WHEN OTHERS THEN
5517: retcode := -1;
5518: errbuf := substr(sqlerrm, 1, 200);
5515: EXCEPTION
5516: WHEN OTHERS THEN
5517: retcode := -1;
5518: errbuf := substr(sqlerrm, 1, 200);
5519: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ' || errbuf);
5520: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
5521: END COLLECT_CMRO_DATA;
5522:
5523:
5516: WHEN OTHERS THEN
5517: retcode := -1;
5518: errbuf := substr(sqlerrm, 1, 200);
5519: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ' || errbuf);
5520: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
5521: END COLLECT_CMRO_DATA;
5522:
5523:
5524: PROCEDURE COLLECT_FLEET_DATA (
5552: var_table_name varchar2(61) := NULL;
5553: var_request_id NUMBER DEFAULT NULL;
5554: var_request_num NUMBER DEFAULT NULL;
5555: BEGIN
5556: msd_dem_common_utilities.log_debug ('Entering MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5557:
5558: --validate parameters
5559: msd_dem_common_utilities.ui_params_validation(x_errbuf, x_retcode,
5560: x_from_date, x_to_date,
5572:
5573: IF (x_retcode = -1) THEN
5574: retcode := -1;
5575: errbuf := x_errbuf;
5576: msd_dem_common_utilities.log_message ('Error: MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5577: msd_dem_common_utilities.log_message ('Error validating params for COLLECT_FLEET_DATA');
5578: RETURN;
5579: END IF;
5580:
5586: x_src_dblink);
5587: IF (x_retcode = -1) THEN
5588: retcode := -1;
5589: errbuf := substr(SQLERRM,1,150);
5590: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5591: msd_dem_common_utilities.log_message ('Error in call to MSD_DEM_COMMON_UTILITIES.GET_DBLINK');
5592: msd_dem_common_utilities.log_message (errbuf);
5593: RETURN;
5594: END IF;
5601: x_dest_dblink);
5602: IF (x_retcode = -1) THEN
5603: retcode := -1;
5604: errbuf := substr(SQLERRM,1,150);
5605: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5606: msd_dem_common_utilities.log_message ('Error in call to MSD_DEM_COMMON_UTILITIES.GET_DEST_DBLINK');
5607: msd_dem_common_utilities.log_message (errbuf);
5608: RETURN;
5609: END IF;
5663:
5664: IF (x_retcode = -1) THEN
5665: retcode := -1;
5666: errbuf := x_errbuf;
5667: msd_dem_common_utilities.log_message ('ERROR(1) - msd_spf_collect_history_data.COLLECT_FLEET_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5668: msd_dem_common_utilities.log_message (errbuf);
5669: RETURN;
5670: END IF;
5671: EXCEPTION
5671: EXCEPTION
5672: WHEN OTHERS THEN
5673: retcode := -1;
5674: errbuf := substr(SQLERRM,1,200);
5675: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5676: msd_dem_common_utilities.log_message ('Error while submitting collection request on source');
5677: msd_dem_common_utilities.log_message (errbuf);
5678: RETURN;
5679: END;
5695: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out, x_src_dblink);
5696: IF (x_retcode = -1) THEN
5697: retcode := -1;
5698: errbuf := x_errbuf;
5699: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5700: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage I to complete.');
5701: msd_dem_common_utilities.log_message (errbuf);
5702: RETURN;
5703: END IF;
5704:
5705: IF (x_retcode = 1) THEN
5706: retcode := 1;
5707: errbuf := x_errbuf;
5708: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5709: msd_dem_common_utilities.log_message ('One or more requests in Stage I completed with warnings.');
5710: msd_dem_common_utilities.log_message (errbuf);
5711: END IF;
5712:
5716: set_cmroeam_purge_profiles(x_errbuf, x_retcode, 'FLEET', x_from_date, x_to_date);
5717: IF (x_retcode = -1) THEN
5718: retcode := -1;
5719: errbuf := x_errbuf;
5720: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5721: msd_dem_common_utilities.log_message ('Error when setting purge profiles.');
5722: msd_dem_common_utilities.log_message (errbuf);
5723: RETURN;
5724: END IF;
5725:
5726: IF (x_retcode = 1) THEN
5727: retcode := 1;
5728: errbuf := x_errbuf;
5729: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_CMRO_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5730: msd_dem_common_utilities.log_message ('Warning when setting purge profiles.');
5731: msd_dem_common_utilities.log_message (errbuf);
5732: END IF;
5733: /* End : Set purge profiles */
5768: EXCEPTION
5769: WHEN OTHERS THEN
5770: retcode := -1;
5771: errbuf := substr(SQLERRM,1,150);
5772: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5773: msd_dem_common_utilities.log_message ('Error while submitting request for launch download');
5774: msd_dem_common_utilities.log_message (errbuf);
5775: RETURN;
5776: END;
5793: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out);
5794: IF (x_retcode = -1) THEN
5795: retcode := -1;
5796: errbuf := x_errbuf;
5797: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5798: msd_dem_common_utilities.log_message ('Error while waiting for requests for Stage II to complete.');
5799: msd_dem_common_utilities.log_message (errbuf);
5800: RETURN;
5801: END IF;
5802:
5803: IF (x_retcode = 1) THEN
5804: retcode := 1;
5805: errbuf := x_errbuf;
5806: msd_dem_common_utilities.log_message ('MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5807: msd_dem_common_utilities.log_message ('One or more requests in Stage II completed with warnings.');
5808: msd_dem_common_utilities.log_message (errbuf);
5809: END IF;
5810:
5814: msd_dem_common_utilities.log_message ('');
5815: msd_dem_common_utilities.log_message ('');
5816: /* STAGE II *** END *** */
5817:
5818: msd_dem_common_utilities.log_debug ('Exiting MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5819: EXCEPTION
5820: WHEN OTHERS THEN
5821: retcode := -1;
5822: errbuf := substr(sqlerrm, 1, 200);
5819: EXCEPTION
5820: WHEN OTHERS THEN
5821: retcode := -1;
5822: errbuf := substr(sqlerrm, 1, 200);
5823: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ' || errbuf);
5824: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
5825: END COLLECT_FLEET_DATA;
5826:
5827:
5820: WHEN OTHERS THEN
5821: retcode := -1;
5822: errbuf := substr(sqlerrm, 1, 200);
5823: msd_dem_common_utilities.log_message('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA - ' || errbuf);
5824: msd_dem_common_utilities.log_debug('Error in MSD_SPF_COLLECT_HISTORY_DATA.COLLECT_FLEET_DATA (' || TO_CHAR(systimestamp, 'DD-MON-RRRR HH24:MI:SS') || ') - ' || errbuf );
5825: END COLLECT_FLEET_DATA;
5826:
5827:
5828: /*
5889:
5890:
5891: BEGIN
5892:
5893: msd_dem_common_utilities.log_debug ('Entering: msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5894:
5895: /* Log the input parameters to the ouput file */
5896: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
5897:
5924: IF (x_retcode = -1)
5925: THEN
5926: retcode := -1;
5927: errbuf := x_errbuf;
5928: msd_dem_common_utilities.log_message ('Error(1): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5929: msd_dem_common_utilities.log_message ('Error while validating UI parameters.');
5930: RETURN;
5931: END IF;
5932:
5944: IF (var_retcode = -1)
5945: THEN
5946: retcode := -1;
5947: errbuf := substr(SQLERRM,1,150);
5948: msd_dem_common_utilities.log_message ('ERROR(2) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5949: msd_dem_common_utilities.log_message ('Error in call to msd_dem_common_utilities.get_dblink');
5950: msd_dem_common_utilities.log_message (errbuf);
5951: RETURN;
5952: END IF;
5964: IF (var_retcode = -1)
5965: THEN
5966: retcode := -1;
5967: errbuf := substr(SQLERRM,1,150);
5968: msd_dem_common_utilities.log_message ('ERROR(3) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
5969: msd_dem_common_utilities.log_message ('Error in call to msd_dem_common_utilities.get_dest_dblink');
5970: msd_dem_common_utilities.log_message (errbuf);
5971: RETURN;
5972: END IF;
6024:
6025: IF (var_retcode = -1) THEN
6026: retcode := -1;
6027: errbuf := var_errbuf;
6028: msd_dem_common_utilities.log_message ('ERROR(4) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6029: msd_dem_common_utilities.log_message (errbuf);
6030: RETURN;
6031: END IF;
6032:
6033: EXCEPTION
6034: WHEN OTHERS THEN
6035: retcode := -1;
6036: errbuf := substr(SQLERRM,1,150);
6037: msd_dem_common_utilities.log_message ('Error(5): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6038: msd_dem_common_utilities.log_message ('Error while submitting request MSDSPFEAMCMRO on source');
6039: msd_dem_common_utilities.log_message (errbuf);
6040: RETURN;
6041: END;
6058: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out, var_D2S_dblink);
6059: IF (x_retcode = -1) THEN
6060: retcode := -1;
6061: errbuf := x_errbuf;
6062: msd_dem_common_utilities.log_message ('Error(6): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6063: msd_dem_common_utilities.log_message ('Error while waiting for request submitted on source.');
6064: msd_dem_common_utilities.log_message (errbuf);
6065: RETURN;
6066: END IF;
6067:
6068: IF (x_retcode = 1) THEN
6069: retcode := 1;
6070: errbuf := x_errbuf;
6071: msd_dem_common_utilities.log_message ('WARNING(1): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6072: msd_dem_common_utilities.log_message ('Request submitted on source completed with warnings.');
6073: msd_dem_common_utilities.log_message (errbuf);
6074: END IF;
6075:
6101: EXCEPTION
6102: WHEN OTHERS THEN
6103: retcode := -1;
6104: errbuf := substr(SQLERRM,1,150);
6105: msd_dem_common_utilities.log_message ('Error(7): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6106: msd_dem_common_utilities.log_message ('Error while submitting request for populating T_SRC_ITEM_TMPL');
6107: msd_dem_common_utilities.log_message (errbuf);
6108: RETURN;
6109: END;
6130: EXCEPTION
6131: WHEN OTHERS THEN
6132: retcode := -1;
6133: errbuf := substr(SQLERRM,1,150);
6134: msd_dem_common_utilities.log_message ('Error(8): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6135: msd_dem_common_utilities.log_message ('Error while submitting request for populating T_SRC_LOC_TMPL');
6136: msd_dem_common_utilities.log_message (errbuf);
6137: RETURN;
6138: END;
6156:
6157: IF (var_retcode = -1) THEN
6158: retcode := -1;
6159: errbuf := var_errbuf;
6160: msd_dem_common_utilities.log_message ('ERROR(9) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6161: msd_dem_common_utilities.log_message ('Error while waiting for requests to complete.');
6162: msd_dem_common_utilities.log_message (errbuf);
6163: RETURN;
6164: END IF;
6165:
6166: IF (var_retcode = 1) THEN
6167: retcode := 1;
6168: errbuf := var_errbuf;
6169: msd_dem_common_utilities.log_message ('WARNING(2) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6170: msd_dem_common_utilities.log_message ('One or more requests completed with warnings.');
6171: msd_dem_common_utilities.log_message (errbuf);
6172: END IF;
6173:
6192: EXCEPTION
6193: WHEN OTHERS THEN
6194: retcode := -1;
6195: errbuf := substr(SQLERRM,1,150);
6196: msd_dem_common_utilities.log_message ('Error(10): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6197: msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
6198: msd_dem_common_utilities.log_message (errbuf);
6199: msd_dem_common_utilities.log_message (' ');
6200: RETURN;
6206: check_requests_completion(x_errbuf, x_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out);
6207: IF (x_retcode = -1) THEN
6208: retcode := -1;
6209: errbuf := x_errbuf;
6210: msd_dem_common_utilities.log_message ('Error(11): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6211: msd_dem_common_utilities.log_message ('Error while waiting for requests to complete.');
6212: msd_dem_common_utilities.log_message (errbuf);
6213: RETURN;
6214: END IF;
6215:
6216: IF (x_retcode = 1) THEN
6217: retcode := 1;
6218: errbuf := x_errbuf;
6219: msd_dem_common_utilities.log_message ('WARNING(3) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6220: msd_dem_common_utilities.log_message ('One or more requests completed with warnings.');
6221: msd_dem_common_utilities.log_message (errbuf);
6222: END IF;
6223:
6229: EXCEPTION
6230: WHEN OTHERS THEN
6231: errbuf := substr(SQLERRM,1,150);
6232: retcode := -1 ;
6233: msd_dem_common_utilities.log_message ('Exception: msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6234: msd_dem_common_utilities.log_message (errbuf);
6235: msd_dem_common_utilities.log_message (' ');
6236:
6237: END Collect_eAM_Data ;
6302: var_retcode VARCHAR2(100) := NULL ;
6303:
6304: BEGIN
6305:
6306: msd_dem_common_utilities.log_debug ('Entering: msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6307:
6308: /* Log the input parameters to the ouput file */
6309: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
6310:
6333: ELSIF (x_hist_series = 'SH_RI_RD') THEN p_sh_ri_rd := 1 ; x_hist_col := 'EBS_SHIP_HIST_REQ_QTY_RD' ;
6334: ELSE
6335: retcode := -1;
6336: errbuf := 'INVALID history series. Please provide correct history series.' ;
6337: msd_dem_common_utilities.log_message ('Error(1): msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6338: msd_dem_common_utilities.log_message ('Error while validating history series.');
6339: msd_dem_common_utilities.log_message (' ');
6340: msd_dem_common_utilities.log_message ('Value for history series must be among the following:-');
6341: msd_dem_common_utilities.log_message ('BH_BI_BD / BH_BI_RD / BH_RI_BD / BH_RI_RD / SH_SI_SD / SH_SI_RD / SH_RI_SD / SH_RI_RD ');
6357: EXCEPTION
6358: WHEN OTHERS THEN
6359: retcode := -1;
6360: errbuf := substr(SQLERRM,1,150);
6361: msd_dem_common_utilities.log_message ('ERROR(2) - msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6362: msd_dem_common_utilities.log_message ('Error while submitting request for push setup parameters');
6363: msd_dem_common_utilities.log_message (errbuf);
6364: RETURN;
6365: END;
6383: check_requests_completion(var_errbuf, var_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out, NULL);
6384: IF (var_retcode = -1) THEN
6385: retcode := -1;
6386: errbuf := var_errbuf;
6387: msd_dem_common_utilities.log_message ('ERROR(3) - msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6388: msd_dem_common_utilities.log_message (errbuf);
6389: RETURN;
6390: END IF;
6391:
6391:
6392: IF (var_retcode = 1) THEN
6393: retcode := 1;
6394: errbuf := var_errbuf;
6395: msd_dem_common_utilities.log_message ('WARNING(1) - msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6396: msd_dem_common_utilities.log_message (errbuf);
6397: END IF;
6398:
6399: /*----- END - Submit Push Setup Parameters conc progam -----*/
6436: IF (x_retcode = -1)
6437: THEN
6438: retcode := -1;
6439: errbuf := x_errbuf;
6440: msd_dem_common_utilities.log_message ('Error(4): msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6441: msd_dem_common_utilities.log_message ('Error while calling collect_history_data.');
6442: msd_dem_common_utilities.log_message (' ');
6443: RETURN;
6444: END IF;
6473: EXCEPTION
6474: WHEN OTHERS THEN
6475: retcode := -1;
6476: errbuf := substr(SQLERRM,1,150);
6477: msd_dem_common_utilities.log_message ('Error(5): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6478: msd_dem_common_utilities.log_message ('Error while submitting request for populating T_SRC_ITEM_TMPL');
6479: msd_dem_common_utilities.log_message (errbuf);
6480: RETURN;
6481: END;
6507: EXCEPTION
6508: WHEN OTHERS THEN
6509: retcode := -1;
6510: errbuf := substr(SQLERRM,1,150);
6511: msd_dem_common_utilities.log_message ('Error(6): msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6512: msd_dem_common_utilities.log_message ('Error while submitting request for populating T_SRC_LOC_TMPL');
6513: msd_dem_common_utilities.log_message (errbuf);
6514: RETURN;
6515: END;
6532:
6533: IF (var_retcode = -1) THEN
6534: retcode := -1;
6535: errbuf := var_errbuf;
6536: msd_dem_common_utilities.log_message ('ERROR(7) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6537: msd_dem_common_utilities.log_message (errbuf);
6538: RETURN;
6539: END IF;
6540:
6540:
6541: IF (var_retcode = 1) THEN
6542: retcode := 1;
6543: errbuf := var_errbuf;
6544: msd_dem_common_utilities.log_message ('WARNING(2) - msd_spf_collect_history_data.Collect_eAM_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6545: msd_dem_common_utilities.log_message (errbuf);
6546: END IF;
6547:
6548: /*------- START - updates to t_src_item_tmpl table -------*/
6575: EXCEPTION
6576: WHEN OTHERS THEN
6577: retcode := -1;
6578: errbuf := substr(SQLERRM,1,150);
6579: msd_dem_common_utilities.log_message ('Error(8): msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6580: msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
6581: msd_dem_common_utilities.log_message (errbuf);
6582: msd_dem_common_utilities.log_message (' ');
6583: RETURN;
6588: EXCEPTION
6589: WHEN OTHERS THEN
6590: errbuf := substr(SQLERRM,1,150);
6591: retcode := -1 ;
6592: msd_dem_common_utilities.log_message ('Exception: msd_spf_collect_history_data.collect_nmp_hist_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6593: msd_dem_common_utilities.log_message (errbuf);
6594: msd_dem_common_utilities.log_message (' ');
6595:
6596: END collect_nmp_hist_data ;
6645: x_sql varchar2(200) := null;
6646:
6647: BEGIN
6648:
6649: msd_dem_common_utilities.log_debug ('Entering: msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6650:
6651: /* Log the input parameters to the log file */
6652: msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
6653:
6684:
6685: IF (var_retcode = -1) THEN
6686: retcode := -1;
6687: errbuf := var_errbuf;
6688: msd_dem_common_utilities.log_message ('ERROR(1) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6689: msd_dem_common_utilities.log_message (errbuf);
6690: RETURN;
6691: END IF;
6692: EXCEPTION
6692: EXCEPTION
6693: WHEN OTHERS THEN
6694: retcode := -1;
6695: errbuf := substr(SQLERRM,1,150);
6696: msd_dem_common_utilities.log_message ('ERROR(2) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6697: msd_dem_common_utilities.log_message ('Error while submitting request for push setup parameters.');
6698: msd_dem_common_utilities.log_message (errbuf);
6699: RETURN;
6700: END;
6715: check_requests_completion(var_errbuf, var_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out, p_dest_dblink);
6716: IF (var_retcode = -1) THEN
6717: retcode := -1;
6718: errbuf := var_errbuf;
6719: msd_dem_common_utilities.log_message ('ERROR(3) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6720: msd_dem_common_utilities.log_message (errbuf);
6721: RETURN;
6722: END IF;
6723:
6723:
6724: IF (var_retcode = 1) THEN
6725: retcode := 1;
6726: errbuf := var_errbuf;
6727: msd_dem_common_utilities.log_message ('WARNING(1) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6728: msd_dem_common_utilities.log_message (errbuf);
6729: END IF;
6730:
6731: /*----- END - Submit Push Setup Parameters conc progam on destination -----*/
6763:
6764: IF (var_retcode = -1) THEN
6765: retcode := -1;
6766: errbuf := var_errbuf;
6767: msd_dem_common_utilities.log_message ('ERROR(4) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6768: msd_dem_common_utilities.log_message (errbuf);
6769: RETURN;
6770: END IF;
6771: EXCEPTION
6771: EXCEPTION
6772: WHEN OTHERS THEN
6773: retcode := -1;
6774: errbuf := substr(SQLERRM,1,150);
6775: msd_dem_common_utilities.log_message ('ERROR(5) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6776: msd_dem_common_utilities.log_message ('Error while submitting request for collecting eAM Materials Usage.');
6777: msd_dem_common_utilities.log_message (errbuf);
6778: RETURN;
6779: END;
6810:
6811: IF (var_retcode = -1) THEN
6812: retcode := -1;
6813: errbuf := var_errbuf;
6814: msd_dem_common_utilities.log_message ('ERROR(4) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6815: msd_dem_common_utilities.log_message (errbuf);
6816: RETURN;
6817: END IF;
6818: EXCEPTION
6818: EXCEPTION
6819: WHEN OTHERS THEN
6820: retcode := -1;
6821: errbuf := substr(SQLERRM,1,150);
6822: msd_dem_common_utilities.log_message ('ERROR(5) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6823: msd_dem_common_utilities.log_message ('Error while submitting request for collecting eAM Resource Usage');
6824: msd_dem_common_utilities.log_message (errbuf);
6825: RETURN;
6826: END;
6842:
6843: IF (var_retcode = -1) THEN
6844: retcode := -1;
6845: errbuf := var_errbuf;
6846: msd_dem_common_utilities.log_message ('ERROR(6) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6847: msd_dem_common_utilities.log_message (errbuf);
6848: RETURN;
6849: END IF;
6850:
6850:
6851: IF (var_retcode = 1) THEN
6852: retcode := 1;
6853: errbuf := var_errbuf;
6854: msd_dem_common_utilities.log_message ('WARNING(2) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6855: msd_dem_common_utilities.log_message (errbuf);
6856: END IF;
6857:
6858: /*----- END - Submit series data collection progams on destination -----*/
6887:
6888: IF (var_retcode = -1) THEN
6889: retcode := -1;
6890: errbuf := var_errbuf;
6891: msd_dem_common_utilities.log_message ('ERROR(7) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6892: msd_dem_common_utilities.log_message (errbuf);
6893: RETURN;
6894: END IF;
6895: EXCEPTION
6895: EXCEPTION
6896: WHEN OTHERS THEN
6897: retcode := -1;
6898: errbuf := substr(SQLERRM,1,150);
6899: msd_dem_common_utilities.log_message ('ERROR(8) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6900: msd_dem_common_utilities.log_message ('Error while submitting request for collecting workorder history');
6901: msd_dem_common_utilities.log_message (errbuf);
6902: RETURN;
6903: END;
6934:
6935: IF (var_retcode = -1) THEN
6936: retcode := -1;
6937: errbuf := var_errbuf;
6938: msd_dem_common_utilities.log_message ('ERROR(9) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6939: msd_dem_common_utilities.log_message (errbuf);
6940: RETURN;
6941: END IF;
6942: EXCEPTION
6942: EXCEPTION
6943: WHEN OTHERS THEN
6944: retcode := -1;
6945: errbuf := substr(SQLERRM,1,150);
6946: msd_dem_common_utilities.log_message ('ERROR(10) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6947: msd_dem_common_utilities.log_message ('Error while submitting request for collecting material usage history');
6948: msd_dem_common_utilities.log_message (errbuf);
6949: RETURN;
6950: END;
6981:
6982: IF (var_retcode = -1) THEN
6983: retcode := -1;
6984: errbuf := var_errbuf;
6985: msd_dem_common_utilities.log_message ('ERROR(11) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6986: msd_dem_common_utilities.log_message (errbuf);
6987: RETURN;
6988: END IF;
6989: EXCEPTION
6989: EXCEPTION
6990: WHEN OTHERS THEN
6991: retcode := -1;
6992: errbuf := substr(SQLERRM,1,150);
6993: msd_dem_common_utilities.log_message ('ERROR(12) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
6994: msd_dem_common_utilities.log_message ('Error while submitting request for collecting Resource Usage history');
6995: msd_dem_common_utilities.log_message (errbuf);
6996: RETURN;
6997: END;
7013:
7014: IF (var_retcode = -1) THEN
7015: retcode := -1;
7016: errbuf := var_errbuf;
7017: msd_dem_common_utilities.log_message ('ERROR(13) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7018: msd_dem_common_utilities.log_message (errbuf);
7019: RETURN;
7020: END IF;
7021:
7021:
7022: IF (var_retcode = 1) THEN
7023: retcode := 1;
7024: errbuf := var_errbuf;
7025: msd_dem_common_utilities.log_message ('WARNING(3) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7026: msd_dem_common_utilities.log_message (errbuf);
7027: END IF;
7028:
7029: /*----- END - Submit series data collection progams on destination -----*/
7059:
7060: IF (var_retcode = -1) THEN
7061: retcode := -1;
7062: errbuf := var_errbuf;
7063: msd_dem_common_utilities.log_message ('ERROR(14) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7064: msd_dem_common_utilities.log_message (errbuf);
7065: RETURN;
7066: END IF;
7067: EXCEPTION
7067: EXCEPTION
7068: WHEN OTHERS THEN
7069: retcode := -1;
7070: errbuf := substr(SQLERRM,1,150);
7071: msd_dem_common_utilities.log_message ('ERROR(15) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7072: msd_dem_common_utilities.log_message ('Error while submitting request for collecting workorder history');
7073: msd_dem_common_utilities.log_message (errbuf);
7074: RETURN;
7075: END;
7091:
7092: IF (var_retcode = -1) THEN
7093: retcode := -1;
7094: errbuf := var_errbuf;
7095: msd_dem_common_utilities.log_message ('ERROR(16) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7096: msd_dem_common_utilities.log_message (errbuf);
7097: RETURN;
7098: END IF;
7099:
7099:
7100: IF (var_retcode = 1) THEN
7101: retcode := 1;
7102: errbuf := var_errbuf;
7103: msd_dem_common_utilities.log_message ('WARNING(4) - msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7104: msd_dem_common_utilities.log_message (errbuf);
7105: END IF;
7106:
7107: /*----- END - Submit series data collection progams on destination -----*/
7107: /*----- END - Submit series data collection progams on destination -----*/
7108:
7109: END IF;
7110:
7111: msd_dem_common_utilities.log_debug ('Exiting: msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7112:
7113: EXCEPTION
7114: WHEN OTHERS THEN
7115: errbuf := substr(SQLERRM,1,150);
7113: EXCEPTION
7114: WHEN OTHERS THEN
7115: errbuf := substr(SQLERRM,1,150);
7116: retcode := -1 ;
7117: msd_dem_common_utilities.log_message ('Exception: msd_spf_collect_history_data.Collect_Source_Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7118: msd_dem_common_utilities.log_message (errbuf);
7119: msd_dem_common_utilities.log_message (' ');
7120:
7121: END Collect_Source_Data ;
7134: x_schema VARCHAR2(100) := NULL;
7135: x_sql VARCHAR2(500) := NULL;
7136: BEGIN
7137:
7138: msd_dem_common_utilities.log_debug ('Entering: msd_spf_collect_history_data.purge_spf_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7139:
7140: x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
7141:
7142: /*Deleting records from T_EP_SPF table */
7194: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7195:
7196: msd_dem_common_utilities.log_message('SPF GL Data deleted successfully');
7197: msd_dem_common_utilities.log_message('Please bounce the demantra server');
7198: msd_dem_common_utilities.log_debug ('Exiting: msd_spf_collect_history_data.purge_spf_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7199: retcode := 0;
7200:
7201: EXCEPTION
7202: WHEN OTHERS THEN
7202: WHEN OTHERS THEN
7203: errbuf := substr(SQLERRM,1,200);
7204: retcode := -1;
7205:
7206: msd_dem_common_utilities.log_message ('Exception(1): msd_spf_collect_history_data.purge_spf_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
7207: msd_dem_common_utilities.log_message (errbuf);
7208: RETURN;
7209:
7210: END PURGE_SPF_GL_DATA;
7209:
7210: END PURGE_SPF_GL_DATA;
7211:
7212:
7213: END MSD_SPF_COLLECT_HISTORY_DATA;