[Home] [Help]
303: WSH_DEBUG_SV.log(l_module_name,'p_interfacing',p_interfacing);
304: END IF;
305: --
306:
307: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
308:
309: IF p_interfacing = 'OM' THEN
310: IF p_transfer_param = 'MSNT' THEN
311: OPEN dd_msnt_rec_om;
533: IF l_debug_on THEN
534: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
535: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
536: END IF;
537: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS_PVT');
538: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
539:
540: END transfer_serial_numbers_pvt;
541:
534: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
535: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
536: END IF;
537: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS_PVT');
538: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
539:
540: END transfer_serial_numbers_pvt;
541:
542: --========================================================================
577: WSH_DEBUG_SV.log(l_module_name,'p_interfacing',p_interfacing);
578: END IF;
579: --
580:
581: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
582:
583: -- Call transfer_serial_numbers_pvt
584:
585: -- Case I : Serial Records where 'From Serial Number' <> 'To Serial Number'
609: IF l_debug_on THEN
610: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
611: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
612: END IF;
613: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS');
614: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
615:
616: END transfer_serial_numbers;
617:
610: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
611: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
612: END IF;
613: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS');
614: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
615:
616: END transfer_serial_numbers;
617:
618: -- End of Changes for creating wrapper and new API transfer_serial_numbers_pvt
654: WSH_DEBUG_SV.log(l_module_name,'Submitting the request');
655: l_log_level := 1;
656: END IF;
657: --
658: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
659: -- bug 2308504: make sure to pass all parameters expected,
660: -- so that code output will go into the log file.
661: request_id := FND_REQUEST.submit_Request('WSH', 'WSHINTERFACE', '', '', FALSE,
662: 'ALL', -- mode
670: raise inv_inter_req_submission;
671: else
672: FND_MESSAGE.SET_NAME('WSH', 'WSH_DET_INV_INT_SUBMITTED');
673: FND_MESSAGE.SET_TOKEN('REQ_ID', to_char(request_id));
674: WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
675: END if;
676:
677: IF l_debug_on THEN
678: WSH_DEBUG_SV.pop(l_module_name);
679: END IF;
680: --
681: exception
682: WHEN inv_inter_req_submission THEN
683: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
684: fnd_message.set_name('WSH', 'WSH_DET_INV_INT_REQ_SUBMISSION');
685: WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
686: IF l_debug_on THEN
687: WSH_DEBUG_SV.logmsg(l_module_name,'INV_INTER_REQ_SUBMISSION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
681: exception
682: WHEN inv_inter_req_submission THEN
683: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
684: fnd_message.set_name('WSH', 'WSH_DET_INV_INT_REQ_SUBMISSION');
685: WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
686: IF l_debug_on THEN
687: WSH_DEBUG_SV.logmsg(l_module_name,'INV_INTER_REQ_SUBMISSION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
688: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INV_INTER_REQ_SUBMISSION');
689: END IF;
688: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INV_INTER_REQ_SUBMISSION');
689: END IF;
690: --
691: WHEN others THEN
692: wsh_util_core.default_handler('WSH_SHIP_CONFRIM_ACTIONS.SHIP_CONFIRM_A_TRIP_STOP',l_module_name);
693: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
694: IF l_debug_on THEN
695: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
696: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
689: END IF;
690: --
691: WHEN others THEN
692: wsh_util_core.default_handler('WSH_SHIP_CONFRIM_ACTIONS.SHIP_CONFIRM_A_TRIP_STOP',l_module_name);
693: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
694: IF l_debug_on THEN
695: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
696: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
697: END IF;
793: x_master_status := p_api_status;
794: END IF;
795: ELSE
796: -- unknown status...
797: WSH_UTIL_CORE.PrintMsg('ERROR: unknown status = '''
798: || p_api_status || '''');
799: x_master_status := 'ERROR';
800: END IF;
801: --
845: --
846: --========================================================================
847:
848: PROCEDURE handle_stop_lvl_splits(
849: x_split_stops IN OUT NOCOPY wsh_util_core.id_tab_type,
850: p_batch_id IN NUMBER,
851: x_stop_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
852: x_return_status OUT NOCOPY VARCHAR2)
853: IS
847:
848: PROCEDURE handle_stop_lvl_splits(
849: x_split_stops IN OUT NOCOPY wsh_util_core.id_tab_type,
850: p_batch_id IN NUMBER,
851: x_stop_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
852: x_return_status OUT NOCOPY VARCHAR2)
853: IS
854: --
855: l_debug_on BOOLEAN;
862: l_last NUMBER;
863: z NUMBER := 1;
864: l_found BOOLEAN;
865:
866: l_new_split_stops wsh_util_core.id_tab_type;
867: l_stop_tab wsh_util_core.id_tab_type;
868:
869:
870: CURSOR get_stops(p_batch_id NUMBER) is
863: z NUMBER := 1;
864: l_found BOOLEAN;
865:
866: l_new_split_stops wsh_util_core.id_tab_type;
867: l_stop_tab wsh_util_core.id_tab_type;
868:
869:
870: CURSOR get_stops(p_batch_id NUMBER) is
871: SELECT DISTINCT wts.stop_id
912: WSH_DEBUG_SV.log(l_module_name,'x_stop_tab.count', x_stop_tab.COUNT);
913: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
914: END IF;
915:
916: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
917:
918: l_stop_tab := x_stop_tab;
919: x_stop_tab.DELETE;
920:
998: END IF;
999:
1000: EXCEPTION
1001: WHEN OTHERS THEN
1002: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1003:
1004: IF l_debug_on THEN
1005: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1006: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1084: l_error_code NUMBER;
1085: l_error_text VARCHAR2(2000);
1086: l_temp BOOLEAN;
1087: l_stops_count NUMBER := 0;
1088: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1089: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1090: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1091: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1092: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1085: l_error_text VARCHAR2(2000);
1086: l_temp BOOLEAN;
1087: l_stops_count NUMBER := 0;
1088: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1089: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1090: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1091: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1092: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1093:
1086: l_temp BOOLEAN;
1087: l_stops_count NUMBER := 0;
1088: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1089: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1090: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1091: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1092: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1093:
1094: l_inv_interface NUMBER := 0;
1087: l_stops_count NUMBER := 0;
1088: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1089: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1090: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1091: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1092: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1093:
1094: l_inv_interface NUMBER := 0;
1095: l_om_interface NUMBER := 0;
1088: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1089: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1090: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1091: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1092: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1093:
1094: l_inv_interface NUMBER := 0;
1095: l_om_interface NUMBER := 0;
1096: l_dsno_interface NUMBER := 0;
1103: l_stop_per_batch_counter NUMBER := 1;
1104: l_batch_ready BOOLEAN := FALSE;
1105: l_stop_batch_id NUMBER;
1106: -- Stops that will be used for DSNO interface are put in l_dsno_stop_tab.
1107: l_dsno_stop_tab wsh_util_core.id_Tab_type;
1108: -- The stops being processed for OM interface are stored in l_stop_tab
1109: l_stop_tab wsh_util_core.id_Tab_type;
1110: l_tab_count NUMBER;
1111: l_err_stops_count NUMBER;
1105: l_stop_batch_id NUMBER;
1106: -- Stops that will be used for DSNO interface are put in l_dsno_stop_tab.
1107: l_dsno_stop_tab wsh_util_core.id_Tab_type;
1108: -- The stops being processed for OM interface are stored in l_stop_tab
1109: l_stop_tab wsh_util_core.id_Tab_type;
1110: l_tab_count NUMBER;
1111: l_err_stops_count NUMBER;
1112: l_completion_status_bkp VARCHAR2(30);
1113: l_stop_count NUMBER := 0;
1112: l_completion_status_bkp VARCHAR2(30);
1113: l_stop_count NUMBER := 0;
1114: l_api_completion_status_bkp VARCHAR2(30);
1115: l_return_status VARCHAR2(10);
1116: l_inv_batch_table WSH_UTIL_CORE.Id_Tab_Type;
1117: l_index number;
1118: x NUMBER;
1119: l_num_warnings NUMBER := 0;
1120: l_num_errors NUMBER := 0;
1119: l_num_warnings NUMBER := 0;
1120: l_num_errors NUMBER := 0;
1121: l_invoicing_method VARCHAR2(100);
1122: -- stops that are processed for INV interface are put in l_inv_stops.
1123: l_inv_stops wsh_util_core.id_tab_type;
1124: l_split_stops wsh_util_core.id_tab_type;
1125:
1126: -- bug 2657859 frontport bug 2630535: avoid deadlocks
1127: CURSOR lock_row ( p_stop_id in NUMBER, p_flag in VARCHAR2 ) IS
1120: l_num_errors NUMBER := 0;
1121: l_invoicing_method VARCHAR2(100);
1122: -- stops that are processed for INV interface are put in l_inv_stops.
1123: l_inv_stops wsh_util_core.id_tab_type;
1124: l_split_stops wsh_util_core.id_tab_type;
1125:
1126: -- bug 2657859 frontport bug 2630535: avoid deadlocks
1127: CURSOR lock_row ( p_stop_id in NUMBER, p_flag in VARCHAR2 ) IS
1128: SELECT stop_id
1241: AND wdl.pick_up_stop_id = p_stop_id
1242: AND wts.stop_id = wdl.pick_up_stop_id
1243: AND wts.stop_location_id = wnd.initial_pickup_location_id;
1244:
1245: l_del_entity_ids WSH_UTIL_CORE.column_tab_type;
1246: l_wf_rs VARCHAR2(1);
1247: l_override_wf VARCHAR2(1);
1248: l_purged_count NUMBER;
1249: e_trip_stop_wf_inprogress EXCEPTION;
1261: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INTERFACE_ALL';
1262: --
1263: BEGIN
1264: --
1265: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1266: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
1267: --
1268: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1269: --
1262: --
1263: BEGIN
1264: --
1265: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1266: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
1267: --
1268: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1269: --
1270: IF l_debug_on IS NULL
1339: IF l_debug_on THEN
1340: WSH_DEBUG_SV.log(l_module_name,'l_stop_id',l_stop_id);
1341: END IF;
1342: IF l_stop_id IS NULL THEN
1343: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: cannot find stop. Exiting.');
1344: l_completion_status := 'WARNING';
1345: goto interface_end;
1346: END IF;
1347: END IF;
1347: END IF;
1348:
1349:
1350: IF l_stop_id IS NULL THEN
1351: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing all eligible stops for '
1352: || l_interface_mode);
1353: ELSE
1354: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing stop_id '
1355: || TO_CHAR(l_stop_id)
1350: IF l_stop_id IS NULL THEN
1351: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing all eligible stops for '
1352: || l_interface_mode);
1353: ELSE
1354: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing stop_id '
1355: || TO_CHAR(l_stop_id)
1356: || ' for ' || l_interface_mode);
1357: END IF;
1358:
1595: p_batch_id => l_stop_batch_id,
1596: x_stop_tab => l_stop_tab,
1597: x_return_status => l_return_status);
1598:
1599: wsh_util_core.api_post_call(
1600: p_return_status => l_return_status,
1601: x_num_warnings => l_num_warnings,
1602: x_num_errors => l_num_errors);
1603:
1612: IF l_debug_on THEN
1613: WSH_DEBUG_SV.log(l_module_name,
1614: 'stop is locked');
1615: END IF;
1616: WSH_UTIL_CORE.Println('Interface_All: skipping locked stop_id ' || l_stop_id || ' is locked.');
1617: IF lock_row%ISOPEN THEN
1618: CLOSE lock_row;
1619: END IF;
1620: --IF l_debug_on THEN
1637:
1638: l_completion_status := 'ERROR';
1639: l_error_code := SQLCODE;
1640: l_error_text := SQLERRM;
1641: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_id);
1642: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
1643: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1644: errbuf := 'Interface trip stop failed with unexpected error';
1645: retcode := '2';
1638: l_completion_status := 'ERROR';
1639: l_error_code := SQLCODE;
1640: l_error_text := SQLERRM;
1641: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_id);
1642: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
1643: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1644: errbuf := 'Interface trip stop failed with unexpected error';
1645: retcode := '2';
1646: IF lock_row%ISOPEN THEN
1996:
1997: EXCEPTION
1998: WHEN trip_stop_locked THEN
1999: -- stop is locked; probably used by another process
2000: WSH_UTIL_CORE.PrintMsg('Locking issue: batch '|| l_stop_batch_id || ' needs pending_interface_flag updated to ' || l_new_flag);
2001: IF lock_row%ISOPEN THEN
2002: CLOSE lock_row;
2003: END IF;
2004: IF lock_batch%ISOPEN THEN
2013: END IF;
2014: l_completion_status := 'ERROR';
2015: l_error_code := SQLCODE;
2016: l_error_text := SQLERRM;
2017: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_batch_id);
2018: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2019: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2020: errbuf := 'Interface trip stop failed with unexpected error';
2021: retcode := '2';
2014: l_completion_status := 'ERROR';
2015: l_error_code := SQLCODE;
2016: l_error_text := SQLERRM;
2017: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_batch_id);
2018: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2019: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2020: errbuf := 'Interface trip stop failed with unexpected error';
2021: retcode := '2';
2022: IF lock_row%ISOPEN THEN
2035:
2036:
2037: <
2038: IF l_completion_status = 'ERROR' THEN
2039: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: Stopping because of ERROR.');
2040: IF l_debug_on THEN
2041: WSH_DEBUG_SV.log(l_module_name,
2042: 'InterfaceTripStop: Stopping because of ERROR.');
2043: END IF;
2049: IF l_debug_on THEN
2050: WSH_DEBUG_SV.log(l_module_name,
2051: 'Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2052: END IF;
2053: WSH_UTIL_CORE.Println('Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2054: WSH_UTIL_CORE.Println('Interface_All: skipping stop_id ' || l_stop_id );
2055: --==/
2056:
2057: WHEN e_continue THEN
2050: WSH_DEBUG_SV.log(l_module_name,
2051: 'Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2052: END IF;
2053: WSH_UTIL_CORE.Println('Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2054: WSH_UTIL_CORE.Println('Interface_All: skipping stop_id ' || l_stop_id );
2055: --==/
2056:
2057: WHEN e_continue THEN
2058: NULL;
2079: END IF;
2080: --
2081:
2082: IF l_stops_count = 0 THEN
2083: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: no stop is processed because no lines are eligble for interfacing.');
2084: ELSE
2085: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: total stops processed: '
2086: || l_stops_count);
2087:
2081:
2082: IF l_stops_count = 0 THEN
2083: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: no stop is processed because no lines are eligble for interfacing.');
2084: ELSE
2085: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: total stops processed: '
2086: || l_stops_count);
2087:
2088: FOR i IN 1..l_interface_names.COUNT LOOP
2089: IF l_stops_normal(i) > 0
2088: FOR i IN 1..l_interface_names.COUNT LOOP
2089: IF l_stops_normal(i) > 0
2090: OR l_stops_warning(i) > 0
2091: OR l_stops_interfaced(i) > 0 THEN
2092: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2093: || l_interface_names(i)
2094: || ' with status NORMAL: '
2095: || l_stops_normal(i));
2096: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2092: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2093: || l_interface_names(i)
2094: || ' with status NORMAL: '
2095: || l_stops_normal(i));
2096: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2097: || l_interface_names(i)
2098: || ' with status WARNING: '
2099: || l_stops_warning(i));
2100: WSH_UTIL_CORE.PrintMsg('Stops interfaced for '
2096: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2097: || l_interface_names(i)
2098: || ' with status WARNING: '
2099: || l_stops_warning(i));
2100: WSH_UTIL_CORE.PrintMsg('Stops interfaced for '
2101: || l_interface_names(i)
2102: || ': '
2103: || l_stops_interfaced(i));
2104: IF l_debug_on THEN
2169:
2170: COMMIT;
2171:
2172: IF l_recs > 0 THEN
2173: WSH_UTIL_CORE.PrintMsg('Cleaned up ' || to_char(l_recs) || ' stuck stops.');
2174: IF l_debug_on THEN
2175: WSH_DEBUG_SV.log(l_module_name,
2176: 'Cleaned up ' || to_char(l_recs) || ' stuck stops.');
2177: END IF;
2187: WHEN OTHERS THEN
2188: l_completion_status := 'ERROR';
2189: l_error_code := SQLCODE;
2190: l_error_text := SQLERRM;
2191: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error.');
2192: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2193: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2194: errbuf := 'Interface trip stop failed with unexpected error';
2195: retcode := '2';
2188: l_completion_status := 'ERROR';
2189: l_error_code := SQLCODE;
2190: l_error_text := SQLERRM;
2191: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error.');
2192: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2193: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2194: errbuf := 'Interface trip stop failed with unexpected error';
2195: retcode := '2';
2196: --
2327: WSH_DEBUG_SV.log(l_module_name,'p_mtl_lot_txn_if_rec.count',p_mtl_lot_txn_if_rec.source_line_id.count);
2328: WSH_DEBUG_SV.log(l_module_name,'p_def_inv_online',p_def_inv_online);
2329: END IF;
2330:
2331: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2332:
2333:
2334: SAVEPOINT sp_insert_inv_records;
2335:
2344: IF l_debug_on THEN
2345: WSH_DEBUG_SV.log(l_module_name,'WSH_TRX_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
2346: END IF;
2347:
2348: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
2349: raise fnd_api.g_exc_error;
2350: END IF;
2351: END IF;
2352:
2360: IF l_debug_on THEN
2361: WSH_DEBUG_SV.log(l_module_name,'WSH_TRXSN_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
2362: END IF;
2363:
2364: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
2365: raise fnd_api.g_exc_error;
2366: END IF;
2367:
2368: p_mtl_ser_txn_if_rec.source_code.delete;
2384: IF l_debug_on THEN
2385: WSH_DEBUG_SV.log(l_module_name,'WSH_TRXLOT_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
2386: END IF;
2387:
2388: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
2389: raise fnd_api.g_exc_error;
2390: END IF;
2391:
2392: p_mtl_lot_txn_if_rec.source_code.delete;
2410: END IF;
2411:
2412: EXCEPTION
2413: WHEN FND_API.G_EXC_ERROR THEN
2414: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2415:
2416: IF l_debug_on THEN
2417: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2418: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2420:
2421: ROLLBACK TO SAVEPOINT sp_insert_inv_records;
2422:
2423: WHEN OTHERS THEN
2424: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2425:
2426: IF l_debug_on THEN
2427: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is
2428: '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2553: l_requisition_line_id NUMBER :=NULL;
2554: l_dummy_ser_trx_interface_id number := NULL;
2555: l_trx_source_id NUMBER := NULL;
2556: l_account NUMBER := NULL;
2557: l_account_return_status VARCHAR2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2558: -- HW OPMCONV. Removed OPM local variables
2559:
2560: l_return_status varchar2(30);
2561: l_message_count NUMBER;
2846: WSH_DEBUG_SV.log(l_module_name,'batch_id',p_batch_id);
2847: WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_HEADER_ID',P_TRANSACTION_HEADER_ID);
2848: END IF;
2849:
2850: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2851: -- HW OPMCONV. Removed OPM variables
2852:
2853:
2854: --Bulk fetch c_details_for_interface INTO l_mtl_txn_if_rec;
2932:
2933: l_index := l_mtl_txn_if_rec.picking_line_id.FIRST;
2934: IF nvl(l_index,0) = 0 THEN
2935: -- perhaps the inventory transaction manager is processing this
2936: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2937: WSH_UTIL_CORE.PrintMsg('No Delivery Detail Found');
2938: IF l_debug_on THEN
2939: WSH_DEBUG_SV.log(l_module_name,'No Delivery Detail Found',l_index);
2940: WSH_DEBUG_SV.pop(l_module_name);
2933: l_index := l_mtl_txn_if_rec.picking_line_id.FIRST;
2934: IF nvl(l_index,0) = 0 THEN
2935: -- perhaps the inventory transaction manager is processing this
2936: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2937: WSH_UTIL_CORE.PrintMsg('No Delivery Detail Found');
2938: IF l_debug_on THEN
2939: WSH_DEBUG_SV.log(l_module_name,'No Delivery Detail Found',l_index);
2940: WSH_DEBUG_SV.pop(l_module_name);
2941: END IF;
2971:
2972: ---2:
2973: -- check for Actual Departure Date
2974: IF l_mtl_txn_if_rec.trx_date(l_index) IS NULL THEN
2975: WSH_UTIL_CORE.PrintMsg('Actual Departure Date of Trip Stop in batch '|| p_batch_id ||' is NULL');
2976: IF l_debug_on THEN
2977: WSH_DEBUG_SV.log(l_module_name, 'Actual Departure Date of Trip Stop is NULL in batch ',p_batch_id);
2978: END IF;
2979:
3108: OPEN c_order_line_info(l_mtl_txn_if_rec.source_line_id(l_index));
3109: FETCH c_order_line_info into l_order_line_info;
3110: IF (c_order_line_info%NOTFOUND) THEN
3111: CLOSE c_order_line_info;
3112: WSH_UTIL_CORE.PrintMsg('Sales order not valid');
3113: IF l_debug_on THEN
3114: WSH_DEBUG_SV.log(l_module_name,'Sales order not valid');
3115: END IF;
3116: raise e_line_error;
3132: OPEN c_po_info(l_order_line_info.source_document_line_id, l_order_line_info.source_document_id);
3133: FETCH c_po_info into l_po_info;
3134: IF c_po_info%NOTFOUND THEN
3135: CLOSE c_po_info;
3136: WSH_UTIL_CORE.PrintMsg('Requisition line not found');
3137: IF l_debug_on THEN
3138: WSH_DEBUG_SV.log(l_module_name,'Requisition line not found');
3139: END IF;
3140: raise e_line_error;
3250: END IF;
3251:
3252: IF ( l_account = -11 ) OR l_account IS NULL THEN
3253: IF l_account = -11 THEN
3254: WSH_UTIL_CORE.PRINTMsg ( 'Error: More than one Distribution accounts ' || l_account );
3255: ELSE
3256: WSH_UTIL_CORE.PRINTMsg ( 'No Distribution account ' || l_account );
3257: END IF;
3258: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3252: IF ( l_account = -11 ) OR l_account IS NULL THEN
3253: IF l_account = -11 THEN
3254: WSH_UTIL_CORE.PRINTMsg ( 'Error: More than one Distribution accounts ' || l_account );
3255: ELSE
3256: WSH_UTIL_CORE.PRINTMsg ( 'No Distribution account ' || l_account );
3257: END IF;
3258: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3259: wsh_shipping_params_pvt.get(
3260: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3254: WSH_UTIL_CORE.PRINTMsg ( 'Error: More than one Distribution accounts ' || l_account );
3255: ELSE
3256: WSH_UTIL_CORE.PRINTMsg ( 'No Distribution account ' || l_account );
3257: END IF;
3258: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3259: wsh_shipping_params_pvt.get(
3260: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3261: x_param_info => l_ship_params,
3262: x_return_status => l_sp_ret_status);
3263:
3264: IF l_debug_on THEN
3265: WSH_DEBUG_SV.log(l_module_name,'wsh_shipping_params_pvt.get l_sp_ret_status',l_sp_ret_status);
3266: END IF;
3267: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3268: wsh_util_core.printmsg('Unable to get shipping parameters for org '
3269: || l_mtl_txn_if_rec.organization_id(l_index));
3270:
3271: IF l_debug_on THEN
3264: IF l_debug_on THEN
3265: WSH_DEBUG_SV.log(l_module_name,'wsh_shipping_params_pvt.get l_sp_ret_status',l_sp_ret_status);
3266: END IF;
3267: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3268: wsh_util_core.printmsg('Unable to get shipping parameters for org '
3269: || l_mtl_txn_if_rec.organization_id(l_index));
3270:
3271: IF l_debug_on THEN
3272: WSH_DEBUG_SV.log(l_module_name,'Unable to get shipping parameters for org ',
3281: WSH_DEBUG_SV.logmsg(l_module_name, ' ACCOUNT_ID:' || L_ACCOUNT );
3282: END IF;
3283:
3284: IF ( l_account IS NULL ) THEN
3285: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
3286: l_mtl_txn_if_rec.organization_id(l_index));
3287: raise e_line_warning;
3288: END IF;
3289:
3325: -- FND_API.G_RET_STS_ERROR - on expected error
3326: -- FND_API.G_RET_STS_UNEXP_ERROR - on unexpected error
3327: );
3328:
3329: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3330: --Handle Return Status
3331: x_return_status := l_return_status;
3332:
3333: IF l_debug_on THEN
3374: WSH_DEBUG_SV.logmsg(l_module_name, 'RET_STATUS:'|| L_ACCOUNT_RETURN_STATUS ||
3375: ' ACCOUNT_ID:' || L_ACCOUNT );
3376: END IF;
3377:
3378: IF ( l_account_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3379: WSH_UTIL_CORE.PrintMsg('Unable to get account for delivery detail id ' ||
3380: l_mtl_txn_if_rec.picking_line_id(l_index));
3381: raise e_line_warning;
3382: END IF ;
3375: ' ACCOUNT_ID:' || L_ACCOUNT );
3376: END IF;
3377:
3378: IF ( l_account_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3379: WSH_UTIL_CORE.PrintMsg('Unable to get account for delivery detail id ' ||
3380: l_mtl_txn_if_rec.picking_line_id(l_index));
3381: raise e_line_warning;
3382: END IF ;
3383:
3388: OPEN c_convert_locId(l_mtl_txn_if_rec.ship_to_location_id(l_index));
3389: FETCH c_convert_locId INTO l_ship_to_location_id;
3390: IF c_convert_locId%NOTFOUND THEN
3391: FND_MESSAGE.SET_NAME('WSH','WSH_XC_INVALID_LOCATION');
3392: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
3393: CLOSE c_convert_locId;
3394: IF l_debug_on THEN
3395: WSH_DEBUG_SV.log(l_module_name, 'c_convert_locId%NOTFOUND',
3396: l_mtl_txn_if_rec.ship_to_location_id(l_index));
3426: WSH_DEBUG_SV.log(l_module_name,'l_account',l_account);
3427: END IF;
3428:
3429: IF l_account IS NULL THEN
3430: WSH_UTIL_CORE.PrintMsg('OKE_SHIPPING_EXT.Cost_Of_Sales_Account returns NULL value');
3431: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3432: wsh_shipping_params_pvt.get(
3433: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3434: x_param_info => l_ship_params,
3427: END IF;
3428:
3429: IF l_account IS NULL THEN
3430: WSH_UTIL_CORE.PrintMsg('OKE_SHIPPING_EXT.Cost_Of_Sales_Account returns NULL value');
3431: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3432: wsh_shipping_params_pvt.get(
3433: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3434: x_param_info => l_ship_params,
3435: x_return_status => l_sp_ret_status);
3432: wsh_shipping_params_pvt.get(
3433: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3434: x_param_info => l_ship_params,
3435: x_return_status => l_sp_ret_status);
3436: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3437: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
3438: l_mtl_txn_if_rec.organization_id(l_index));
3439: IF l_debug_on THEN
3440: WSH_DEBUG_SV.log(l_module_name,
3433: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3434: x_param_info => l_ship_params,
3435: x_return_status => l_sp_ret_status);
3436: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3437: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
3438: l_mtl_txn_if_rec.organization_id(l_index));
3439: IF l_debug_on THEN
3440: WSH_DEBUG_SV.log(l_module_name,
3441: 'Unable to get shipping parameters for org', l_mtl_txn_if_rec.organization_id(l_index));
3448: WSH_DEBUG_SV.logmsg(l_module_name, ' ACCOUNT_ID:' || L_ACCOUNT );
3449: END IF;
3450: --
3451: IF l_account IS NULL THEN
3452: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
3453: l_mtl_txn_if_rec.organization_id(l_index));
3454: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
3455: --
3456: IF l_debug_on THEN
3450: --
3451: IF l_account IS NULL THEN
3452: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
3453: l_mtl_txn_if_rec.organization_id(l_index));
3454: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
3455: --
3456: IF l_debug_on THEN
3457: WSH_DEBUG_SV.log(l_module_name, 'There is no default goods dispatched account for org'
3458: ,l_mtl_txn_if_rec.organization_id(l_index));
3476: wsh_shipping_params_pvt.get(
3477: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3478: x_param_info => l_ship_params,
3479: x_return_status => l_sp_ret_status);
3480: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3481: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
3482: l_mtl_txn_if_rec.organization_id(l_index));
3483: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3484: --
3477: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3478: x_param_info => l_ship_params,
3479: x_return_status => l_sp_ret_status);
3480: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3481: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
3482: l_mtl_txn_if_rec.organization_id(l_index));
3483: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3484: --
3485: IF l_debug_on THEN
3479: x_return_status => l_sp_ret_status);
3480: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3481: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
3482: l_mtl_txn_if_rec.organization_id(l_index));
3483: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3484: --
3485: IF l_debug_on THEN
3486: WSH_DEBUG_SV.log(l_module_name, 'Unable to get shipping parameters for org',
3487: l_mtl_txn_if_rec.organization_id(l_index));
3494: WSH_DEBUG_SV.logmsg(l_module_name, ' ACCOUNT_ID:' || L_ACCOUNT );
3495: END IF;
3496:
3497: IF ( l_account IS NULL ) THEN
3498: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
3499: l_mtl_txn_if_rec.organization_id(l_index));
3500: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
3501: --
3502: IF l_debug_on THEN
3496:
3497: IF ( l_account IS NULL ) THEN
3498: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
3499: l_mtl_txn_if_rec.organization_id(l_index));
3500: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
3501: --
3502: IF l_debug_on THEN
3503: WSH_DEBUG_SV.log(l_module_name, 'There is no default goods dispatched account for org',
3504: l_mtl_txn_if_rec.organization_id(l_index));
3787:
3788: IF l_debug_on THEN
3789: WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
3790: END IF;
3791: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3792: l_insert_inv_not_success := l_insert_inv_not_success + 1;
3793: END IF;
3794:
3795: IF l_debug_on THEN
3828:
3829: IF l_debug_on THEN
3830: WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
3831: END IF;
3832: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3833: l_insert_inv_not_success := l_insert_inv_not_success + 1;
3834: END IF;
3835: IF l_debug_on THEN
3836: WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_calls',l_insert_inv_calls);
3865:
3866: IF l_debug_on THEN
3867: WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
3868: END IF;
3869: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3870: l_insert_inv_not_success := l_insert_inv_not_success + 1;
3871: END IF;
3872: IF l_debug_on THEN
3873: WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_calls',l_insert_inv_calls);
3901:
3902: IF (l_insert_inv_not_success >= l_insert_inv_calls) THEN
3903: raise fnd_api.g_exc_error;
3904: ELSIF (l_insert_inv_not_success > 0 and l_insert_inv_not_success < l_insert_inv_calls) THEN
3905: RAISE wsh_util_core.g_exc_warning;
3906: END IF;
3907:
3908:
3909:
3910: IF (l_error_count >= l_mtl_txn_if_rec.picking_line_id.count) THEN
3911: raise fnd_api.g_exc_error;
3912: ELSIF ((l_error_count > 0 and l_error_count
3914: RAISE wsh_util_core.g_exc_warning;
3915: END IF;
3916:
3917: IF l_debug_on THEN
3918: WSH_DEBUG_SV.log(l_module_name, 'x_return_status',x_return_status);
3922: WSH_DEBUG_SV.pop(l_module_name);
3923: END IF;
3924:
3925: EXCEPTION
3926: WHEN wsh_util_core.g_exc_warning THEN
3927: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3928:
3929: IF l_debug_on THEN
3930: wsh_debug_sv.logmsg(l_module_name, 'wsh_util_core.g_exc_warning exception has occured.', wsh_debug_sv.c_excep_level);
3923: END IF;
3924:
3925: EXCEPTION
3926: WHEN wsh_util_core.g_exc_warning THEN
3927: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3928:
3929: IF l_debug_on THEN
3930: wsh_debug_sv.logmsg(l_module_name, 'wsh_util_core.g_exc_warning exception has occured.', wsh_debug_sv.c_excep_level);
3931: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
3926: WHEN wsh_util_core.g_exc_warning THEN
3927: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3928:
3929: IF l_debug_on THEN
3930: wsh_debug_sv.logmsg(l_module_name, 'wsh_util_core.g_exc_warning exception has occured.', wsh_debug_sv.c_excep_level);
3931: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
3932: END IF;
3933:
3934: WHEN fnd_api.g_exc_error THEN
3931: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
3932: END IF;
3933:
3934: WHEN fnd_api.g_exc_error THEN
3935: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3936:
3937: IF l_debug_on THEN
3938: wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
3939: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
3940: END IF;
3941:
3942:
3943: WHEN others THEN
3944: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3945: l_error_code := SQLCODE;
3946: l_error_text := SQLERRM;
3947: WSH_UTIL_CORE.PrintMsg(l_mtl_txn_if_rec.picking_line_id(l_index) ||': Interface detail to inventory failed with unexpected error');
3948: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
3943: WHEN others THEN
3944: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3945: l_error_code := SQLCODE;
3946: l_error_text := SQLERRM;
3947: WSH_UTIL_CORE.PrintMsg(l_mtl_txn_if_rec.picking_line_id(l_index) ||': Interface detail to inventory failed with unexpected error');
3948: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
3949:
3950: IF c_freight_code%ISOPEN THEN
3951: CLOSE c_freight_code;
3944: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3945: l_error_code := SQLCODE;
3946: l_error_text := SQLERRM;
3947: WSH_UTIL_CORE.PrintMsg(l_mtl_txn_if_rec.picking_line_id(l_index) ||': Interface detail to inventory failed with unexpected error');
3948: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
3949:
3950: IF c_freight_code%ISOPEN THEN
3951: CLOSE c_freight_code;
3952: END IF;
4017: WHERE batch_id = p_batch_id
4018: FOR UPDATE NOWAIT;
4019: Recinfo lock_row%ROWTYPE;
4020:
4021: l_stop_tab wsh_util_core.id_tab_type;
4022:
4023: -- bug 5736840
4024:
4025: CURSOR get_details (p_batch_id IN NUMBER) IS
4087:
4088: -- the batch should be fully interfaced to OM first.
4089:
4090: IF NOT OM_INTERFACED(p_batch_id) THEN
4091: wsh_util_core.printmsg('Batch ' || p_batch_id || ' is not yet fully interfaced to OM.');
4092: x_completion_status := 'WARNING';
4093:
4094: IF l_debug_on THEN
4095: WSH_DEBUG_SV.log(l_module_name,'x_completion_status',x_completion_status);
4129: transfer_serial_numbers ( p_batch_id => p_batch_id ,
4130: p_interfacing => 'INV',
4131: x_return_status => l_return_status );
4132:
4133: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4134: IF l_debug_on THEN
4135: WSH_DEBUG_SV.logmsg(l_module_name, 'Error encountered in call to TRANSFER_SERIAL_NUMBERS FOR BATCH' || TO_CHAR ( P_BATCH_ID ) );
4136: WSH_DEBUG_SV.log(l_module_name,'x_completion_status', x_completion_status);
4137: WSH_DEBUG_SV.pop(l_module_name);
4145: -- already successfully processed by the inventory transaction manager.
4146:
4147: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
4148:
4149: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
4150: WSH_UTIL_CORE.PrintMsg('inv_interface_trip_stop failed for batch '||p_batch_id ||':txn '||l_transaction_header_id);
4151: If (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4152: l_completion_status := 'ERROR';
4153: ELSE
4146:
4147: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
4148:
4149: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
4150: WSH_UTIL_CORE.PrintMsg('inv_interface_trip_stop failed for batch '||p_batch_id ||':txn '||l_transaction_header_id);
4151: If (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4152: l_completion_status := 'ERROR';
4153: ELSE
4154: l_completion_status := 'WARNING';
4147: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
4148:
4149: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
4150: WSH_UTIL_CORE.PrintMsg('inv_interface_trip_stop failed for batch '||p_batch_id ||':txn '||l_transaction_header_id);
4151: If (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4152: l_completion_status := 'ERROR';
4153: ELSE
4154: l_completion_status := 'WARNING';
4155: END IF;
4298: THEN
4299: --{
4300: fnd_message.set_name('WSH', 'WSH_INV_INTF_ERROR_LINES');
4301: l_num_warnings := nvl(l_num_warnings,0) + 1;
4302: WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_warning,l_module_name);
4303: --}
4304: -- HW OPMCONV - No need to check for l_opm_org_exist
4305: ELSIF (nvl(l_picking_lines_count,0) = 0
4306: and not(l_non_opm_org_exist)
4307: and nvl(l_def_inv_online,'N') <> 'Y') THEN
4308: --{
4309: fnd_message.set_name('WSH', 'WSH_INV_INTF_LINES_LOCKED');
4310: l_num_warnings := nvl(l_num_warnings,0) + 1;
4311: WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_warning,l_module_name);
4312: --}
4313: END IF;
4314: -- bug 3588371
4315:
4312: --}
4313: END IF;
4314: -- bug 3588371
4315:
4316: -- Bug#4736038: If return value is WSH_UTIL_CORE.G_RET_STS_ERROR,
4317: -- i) It is treated as warning so that other batches can be processed.
4318: -- ii) No need to call process_inv_online as it represents all DD's in a batch are failed.
4319: IF (l_inv_inter_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4320: IF (l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
4315:
4316: -- Bug#4736038: If return value is WSH_UTIL_CORE.G_RET_STS_ERROR,
4317: -- i) It is treated as warning so that other batches can be processed.
4318: -- ii) No need to call process_inv_online as it represents all DD's in a batch are failed.
4319: IF (l_inv_inter_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4320: IF (l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
4321:
4322: IF lock_row%ISOPEN THEN
4323: CLOSE lock_row;
4316: -- Bug#4736038: If return value is WSH_UTIL_CORE.G_RET_STS_ERROR,
4317: -- i) It is treated as warning so that other batches can be processed.
4318: -- ii) No need to call process_inv_online as it represents all DD's in a batch are failed.
4319: IF (l_inv_inter_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4320: IF (l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
4321:
4322: IF lock_row%ISOPEN THEN
4323: CLOSE lock_row;
4324: END IF;
4328: WSH_DEBUG_SV.log(l_module_name,'Batch_id '||p_batch_id||' pass Inventory interface with unexpected errors');
4329: WSH_DEBUG_SV.pop(l_module_name);
4330: END IF;
4331: return;
4332: ELSIF ( l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN
4333:
4334: IF lock_row%ISOPEN THEN
4335: CLOSE lock_row;
4336: END IF;
4354:
4355: IF (nvl(l_def_inv_online,'N') <> 'Y' and l_completion_status <> 'ERROR') THEN
4356: process_inv_online ( p_batch_id , l_transaction_header_id , l_return_status );
4357:
4358: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
4359: IF l_debug_on THEN
4360: WSH_DEBUG_SV.logmsg(l_module_name,'Batch '|| p_batch_id ||': TXN ' ||
4361: L_TRANSACTION_HEADER_ID || ' : PROCESS ONLINE FAILED AGAIN' );
4362: END IF;
4359: IF l_debug_on THEN
4360: WSH_DEBUG_SV.logmsg(l_module_name,'Batch '|| p_batch_id ||': TXN ' ||
4361: L_TRANSACTION_HEADER_ID || ' : PROCESS ONLINE FAILED AGAIN' );
4362: END IF;
4363: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4364: l_completion_status := 'ERROR';
4365: ELSE
4366: l_completion_status := 'WARNING';
4367: END IF;
4396: END IF;
4397: IF get_details%ISOPEN THEN
4398: CLOSE get_details;
4399: END IF;
4400: WSH_UTIL_CORE.PrintMsg('This Trip Stop is locked by some other process');
4401: x_completion_status := 'WARNING'; -- continue processing other stops
4402: --
4403: IF l_debug_on THEN
4404: WSH_DEBUG_SV.logmsg(l_module_name,'TRIP_STOP_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4411: END IF;
4412: l_completion_status := 'ERROR';
4413: l_error_code := SQLCODE;
4414: l_error_text := SQLERRM;
4415: WSH_UTIL_CORE.PrintMsg('Interface trip_stop to inventory failed with unexpected error');
4416: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4417: x_completion_status := l_completion_status;
4418: --
4419: IF l_debug_on THEN
4412: l_completion_status := 'ERROR';
4413: l_error_code := SQLCODE;
4414: l_error_text := SQLERRM;
4415: WSH_UTIL_CORE.PrintMsg('Interface trip_stop to inventory failed with unexpected error');
4416: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4417: x_completion_status := l_completion_status;
4418: --
4419: IF l_debug_on THEN
4420: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4678: x_msg_count => l_msg_count,
4679: x_msg_data => l_msg_data
4680: );
4681:
4682: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
4683: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4684: raise GET_TOLERANCE_FAILED;
4685: END if;
4686:
4679: x_msg_data => l_msg_data
4680: );
4681:
4682: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
4683: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4684: raise GET_TOLERANCE_FAILED;
4685: END if;
4686:
4687: IF l_minmaxoutrec.min_remaining_quantity <= 0 THEN
4695: ELSE
4696: x_ship_beyond_flag := 'F';
4697: END IF;
4698:
4699: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4700:
4701: IF l_debug_on THEN
4702: WSH_DEBUG_SV.log(l_module_name, 'SHIP_BEYOND_FLAG', X_SHIP_BEYOND_FLAG);
4703: WSH_DEBUG_SV.log(l_module_name, 'FULFILLED_FLAG', X_FULFILLED_FLAG);
4897: END IF;
4898: --
4899: exception
4900: WHEN others THEN
4901: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4902: WSH_UTIL_CORE.PrintMsg('UPDATE_INTERFACED_DETAILS : unexpected error ');
4903: WSH_UTIL_CORE.PrintMsg( SQLCODE || ' : ' || SQLERRM );
4904:
4905: IF l_debug_on THEN
4898: --
4899: exception
4900: WHEN others THEN
4901: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4902: WSH_UTIL_CORE.PrintMsg('UPDATE_INTERFACED_DETAILS : unexpected error ');
4903: WSH_UTIL_CORE.PrintMsg( SQLCODE || ' : ' || SQLERRM );
4904:
4905: IF l_debug_on THEN
4906: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4899: exception
4900: WHEN others THEN
4901: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4902: WSH_UTIL_CORE.PrintMsg('UPDATE_INTERFACED_DETAILS : unexpected error ');
4903: WSH_UTIL_CORE.PrintMsg( SQLCODE || ' : ' || SQLERRM );
4904:
4905: IF l_debug_on THEN
4906: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4907: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4997: --
4998: --========================================================================
4999:
5000: PROCEDURE Filter_Stops_From_Batch (p_batch_id IN NUMBER,
5001: p_stop_tab IN wsh_util_core.id_tab_type,
5002: x_num_stops_removed OUT NOCOPY NUMBER,
5003: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
5004: x_return_status OUT NOCOPY VARCHAR2)
5005: IS
4999:
5000: PROCEDURE Filter_Stops_From_Batch (p_batch_id IN NUMBER,
5001: p_stop_tab IN wsh_util_core.id_tab_type,
5002: x_num_stops_removed OUT NOCOPY NUMBER,
5003: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
5004: x_return_status OUT NOCOPY VARCHAR2)
5005: IS
5006:
5007: l_debug_on BOOLEAN;
5014: j NUMBER;
5015: l_all_stops_count NUMBER;
5016:
5017: l_found BOOLEAN;
5018: l_err_stops wsh_util_core.id_tab_type;
5019: l_dummy NUMBER;
5020:
5021: CURSOR c_lock_batch (p_batch_id NUMBER) IS
5022: SELECT batch_id
5060: WSH_DEBUG_SV.push(l_module_name);
5061: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
5062: END IF;
5063: --
5064: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5065: x_num_stops_removed := 0;
5066:
5067: SAVEPOINT s_Filter_Stops_From_Batch;
5068:
5163: EXCEPTION
5164:
5165: WHEN OTHERS THEN
5166: ROLLBACK TO s_Filter_Stops_From_Batch;
5167: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
5168: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.Filter_Stops_From_Batch');
5169:
5170: IF l_debug_on THEN
5171: wsh_debug_sv.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
5164:
5165: WHEN OTHERS THEN
5166: ROLLBACK TO s_Filter_Stops_From_Batch;
5167: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
5168: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.Filter_Stops_From_Batch');
5169:
5170: IF l_debug_on THEN
5171: wsh_debug_sv.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
5172: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
5209: --
5210: --========================================================================
5211:
5212: PROCEDURE oe_interface_trip_stop(p_batch_id IN NUMBER,
5213: p_stop_tab IN wsh_util_core.id_tab_type,
5214: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
5215: x_num_stops_removed OUT NOCOPY NUMBER,
5216: x_completion_status OUT NOCOPY VARCHAR2) IS
5217:
5210: --========================================================================
5211:
5212: PROCEDURE oe_interface_trip_stop(p_batch_id IN NUMBER,
5213: p_stop_tab IN wsh_util_core.id_tab_type,
5214: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
5215: x_num_stops_removed OUT NOCOPY NUMBER,
5216: x_completion_status OUT NOCOPY VARCHAR2) IS
5217:
5218: l_return_status varchar2(30);
5284: END IF;
5285:
5286:
5287: l_prev_return_status := l_return_status;
5288: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN --{
5289: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN --{
5290: raise e_Interface_Stop_to_om;
5291: END IF; --}
5292: ELSE --}{
5285:
5286:
5287: l_prev_return_status := l_return_status;
5288: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN --{
5289: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN --{
5290: raise e_Interface_Stop_to_om;
5291: END IF; --}
5292: ELSE --}{
5293: l_completion_status := 'INTERFACED' ;
5297: IF l_debug_on THEN
5298: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling TRANSFER_SERIAL_NUMBERS FOR ' || TO_CHAR ( p_batch_id ) );
5299: END IF;
5300:
5301: IF l_prev_return_status <> WSH_UTIL_CORE.G_RET_STS_ERROR THEN
5302: transfer_serial_numbers ( p_batch_id => p_batch_id ,
5303: p_interfacing => 'OM',
5304: x_return_status => l_return_status );
5305:
5302: transfer_serial_numbers ( p_batch_id => p_batch_id ,
5303: p_interfacing => 'OM',
5304: x_return_status => l_return_status );
5305:
5306: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5307: raise e_Interface_Stop_To_OM;
5308: end if;
5309: END IF;
5310:
5307: raise e_Interface_Stop_To_OM;
5308: end if;
5309: END IF;
5310:
5311: IF l_prev_return_status IN ( WSH_UTIL_CORE.G_RET_STS_WARNING,
5312: WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN --{
5313:
5314: -- filter out the failed stops from the batch.
5315:
5308: end if;
5309: END IF;
5310:
5311: IF l_prev_return_status IN ( WSH_UTIL_CORE.G_RET_STS_WARNING,
5312: WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN --{
5313:
5314: -- filter out the failed stops from the batch.
5315:
5316: Filter_Stops_From_Batch (p_batch_id => p_batch_id,
5318: x_num_stops_removed => x_num_stops_removed,
5319: x_stop_tab => x_stop_tab,
5320: x_return_status => l_dummy);
5321:
5322: WSH_UTIL_CORE.api_post_call(p_return_status =>l_dummy,
5323: x_num_warnings =>l_num_warnings,
5324: x_num_errors =>l_num_errors);
5325:
5326: -- if x_stop_tab contains less rows than p_stop_tab then this shows
5349: when trip_stop_locked Then
5350: IF lock_row%ISOPEN THEN
5351: CLOSE lock_row;
5352: END IF;
5353: WSH_UTIL_CORE.PrintMsg('This Trip Stop is locked by some other process');
5354: x_completion_status := 'WARNING'; -- continue processing other stops
5355: --
5356: IF l_debug_on THEN
5357: WSH_DEBUG_SV.logmsg(l_module_name,'TRIP_STOP_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5359: END IF;
5360: --
5361:
5362: WHEN e_Interface_Stop_To_OM then
5363: WSH_UTIL_CORE.PrintMsg('Failed to interface Batch ' || p_batch_id
5364: || ' to Order Management because API interface_header_to_OM failed');
5365: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
5366: x_completion_status := 'ERROR';
5367: ELSE
5361:
5362: WHEN e_Interface_Stop_To_OM then
5363: WSH_UTIL_CORE.PrintMsg('Failed to interface Batch ' || p_batch_id
5364: || ' to Order Management because API interface_header_to_OM failed');
5365: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
5366: x_completion_status := 'ERROR';
5367: ELSE
5368: x_completion_status := 'WARNING';
5369: END IF;
5379: --
5380:
5381: WHEN others then
5382:
5383: -- todo: Use wsh_util_core.default_handler and then get_message and println
5384: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || SQLERRM || to_char(SQLCODE));
5385: x_completion_status := 'ERROR';
5386: IF lock_row%ISOPEN THEN -- bug 2598688: avoid invalid cursor
5387: CLOSE lock_row;
5380:
5381: WHEN others then
5382:
5383: -- todo: Use wsh_util_core.default_handler and then get_message and println
5384: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || SQLERRM || to_char(SQLCODE));
5385: x_completion_status := 'ERROR';
5386: IF lock_row%ISOPEN THEN -- bug 2598688: avoid invalid cursor
5387: CLOSE lock_row;
5388: END IF;
5507:
5508: l_return_status varchar2(1);
5509: l_temp NUMBER;
5510: l_bulk boolean ;
5511: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5512: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5513: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5514: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5515: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5508: l_return_status varchar2(1);
5509: l_temp NUMBER;
5510: l_bulk boolean ;
5511: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5512: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5513: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5514: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5515: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5516: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5509: l_temp NUMBER;
5510: l_bulk boolean ;
5511: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5512: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5513: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5514: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5515: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5516: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5517: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
5510: l_bulk boolean ;
5511: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5512: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5513: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5514: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5515: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5516: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5517: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
5518: e_raise_others EXCEPTION;
5511: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5512: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5513: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5514: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5515: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5516: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5517: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
5518: e_raise_others EXCEPTION;
5519:
5512: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5513: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5514: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5515: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
5516: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
5517: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
5518: e_raise_others EXCEPTION;
5519:
5520: BEGIN
5550: WSH_DEBUG_SV.logmsg(l_module_name, ' Processing Model Line');
5551: END IF;
5552:
5553: --Check if bulk status is found for current top model line in cache
5554: WSH_UTIL_CORE.get_cached_value(
5555: p_cache_tbl => l_model_cache_tbl,
5556: p_cache_ext_tbl => l_model_cache_ext_tbl,
5557: p_value => l_bulk,
5558: p_key => p_setsmc_input_rec.top_model_line_id,
5559: p_action => 'GET',
5560: x_return_status => l_return_status) ;
5561:
5562: --IF (not found in cache) THEN
5563: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --{
5564: IF l_debug_on THEN
5565: WSH_DEBUG_SV.logmsg(l_module_name,
5566: ' Calling OE_Shipping_Integration_PUB.Get_SetSMC_Interface_Status');
5567: END IF;
5575: l_return_status);
5576: WSH_DEBUG_SV.log(l_module_name,'x_interface_status',
5577: l_setsmc_output_rec.x_interface_status);
5578: END IF;
5579: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5580: raise FND_API.G_EXC_ERROR;
5581: END IF;
5582:
5583: IF l_setsmc_output_rec.x_interface_status = 'Y' THEN --{
5596: l_bulk:=false;
5597: END IF; --}
5598:
5599: --Cache top_model_id and bulk status.
5600: WSH_UTIL_CORE.get_cached_value(
5601: p_cache_tbl => l_model_cache_tbl,
5602: p_cache_ext_tbl => l_model_cache_ext_tbl,
5603: p_value => l_bulk,
5604: p_key => p_setsmc_input_rec.top_model_line_id,
5604: p_key => p_setsmc_input_rec.top_model_line_id,
5605: p_action => 'PUT',
5606: x_return_status => l_return_status) ;
5607:
5608: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5609: raise FND_API.G_EXC_ERROR;
5610: END IF;
5611:
5612: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN --}{
5608: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5609: raise FND_API.G_EXC_ERROR;
5610: END IF;
5611:
5612: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN --}{
5613: raise FND_API.G_EXC_ERROR;
5614: END IF; --}
5615: END IF;
5616: -- Processing Model Line
5623: WSH_DEBUG_SV.logmsg(l_module_name, ' Processing Ship Set Line');
5624: END IF;
5625:
5626: --Check if bulk status is found for current ship_set line in cache
5627: WSH_UTIL_CORE.get_cached_value(
5628: p_cache_tbl => l_ship_cache_tbl,
5629: p_cache_ext_tbl => l_ship_cache_ext_tbl,
5630: p_value => l_bulk,
5631: p_key => p_setsmc_input_rec.ship_set_id,
5632: p_action => 'GET',
5633: x_return_status => l_return_status) ;
5634:
5635: --IF (not found in cache) THEN
5636: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5637: IF l_debug_on THEN
5638: WSH_DEBUG_SV.logmsg(l_module_name,
5639: ' Calling OE_Shipping_Integration_PUB.Get_SetSMC_Interface_Status');
5640: END IF;
5649: WSH_DEBUG_SV.log(l_module_name,'x_interface_status',
5650: l_setsmc_output_rec.x_interface_status);
5651: END IF;
5652:
5653: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5654: raise FND_API.G_EXC_ERROR;
5655: END IF;
5656: IF l_setsmc_output_rec.x_interface_status = 'Y' THEN --{
5657: OPEN ship_set_line_csr(p_batch_id,p_setsmc_input_rec.ship_set_id, p_source_header_id);
5667: ELSE --}{
5668: l_bulk:=false;
5669: END IF; --}
5670: --Cache top_model_id and bulk status.
5671: WSH_UTIL_CORE.get_cached_value(
5672: p_cache_tbl => l_ship_cache_tbl,
5673: p_cache_ext_tbl => l_ship_cache_ext_tbl,
5674: p_value => l_bulk,
5675: p_key => p_setsmc_input_rec.ship_set_id,
5675: p_key => p_setsmc_input_rec.ship_set_id,
5676: p_action => 'PUT',
5677: x_return_status => l_return_status) ;
5678:
5679: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5680: raise FND_API.G_EXC_ERROR;
5681: END IF;
5682: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5683: raise FND_API.G_EXC_ERROR;
5678:
5679: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5680: raise FND_API.G_EXC_ERROR;
5681: END IF;
5682: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5683: raise FND_API.G_EXC_ERROR;
5684: END IF;
5685: END IF;
5686: -- Processing Ship Set Line
5693: WSH_DEBUG_SV.logmsg(l_module_name, ' Processing Standard Line');
5694: END IF;
5695:
5696: --Check if bulk status is found for current ato_line in cache
5697: WSH_UTIL_CORE.get_cached_value(
5698: p_cache_tbl => l_std_cache_tbl,
5699: p_cache_ext_tbl => l_std_cache_ext_tbl,
5700: p_value => l_bulk,
5701: p_key => p_source_line_id,
5702: p_action => 'GET',
5703: x_return_status => l_return_status) ;
5704:
5705: --IF (not found in cache) THEN
5706: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5707: OPEN std_item_line_csr(p_batch_id,p_source_line_id);
5708: FETCH std_item_line_csr INTO l_temp;
5709:
5710: IF (std_item_line_csr%NOTFOUND) THEN
5714: END IF;
5715: CLOSE std_item_line_csr;
5716:
5717: --Cache top_model_id and bulk status.
5718: WSH_UTIL_CORE.get_cached_value(
5719: p_cache_tbl => l_std_cache_tbl,
5720: p_cache_ext_tbl => l_std_cache_ext_tbl,
5721: p_value => l_bulk,
5722: p_key => p_source_line_id,
5722: p_key => p_source_line_id,
5723: p_action => 'PUT',
5724: x_return_status => l_return_status) ;
5725:
5726: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5727: raise FND_API.G_EXC_ERROR;
5728: END IF;
5729: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5730: raise FND_API.G_EXC_ERROR;
5725:
5726: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5727: raise FND_API.G_EXC_ERROR;
5728: END IF;
5729: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5730: raise FND_API.G_EXC_ERROR;
5731: END IF;
5732: END IF;
5733: --Standard item
6005: WSH_DEBUG_SV.log(l_module_name,'p_bulk_req_line.count',p_bulk_req_line.line_id.count);
6006: WSH_DEBUG_SV.log(l_module_name,'x_charges_are_calculated',x_charges_are_calculated);
6007: END IF;
6008:
6009: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6010:
6011:
6012:
6013: IF (p_bulk_mode='N') THEN
6087: WSH_DEBUG_SV.log(l_module_name,'l_ship_adj_line_all.line_id.count',l_ship_adj_line_all.line_id.count);
6088: END IF;
6089:
6090:
6091: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
6092: RAISE process_freight_costs_failed;
6093: END IF;
6094:
6095: l_freight_costs_all.DELETE;
6283: WSH_DEBUG_SV.log(l_module_name,'l_return_status ',l_return_status);
6284: WSH_DEBUG_SV.logmsg(l_module_name, 'NO. OF OE MESSAGES :'||X_MSG_COUNT );
6285: END IF;
6286:
6287: WSH_UTIL_CORE.printmsg('no. of OE messages :'||x_msg_count);
6288:
6289: FOR k IN 1 .. nvl(x_msg_count,0)
6290: LOOP
6291: --
6300: IF l_debug_on THEN
6301: WSH_DEBUG_SV.logmsg(l_module_name, SUBSTR ( X_MSG_DATA , 1 , 255 ) );
6302: END IF;
6303: --
6304: WSH_UTIL_CORE.printmsg('Error msg: '||substr(x_msg_data,1,2000));
6305: END LOOP;
6306:
6307: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
6308: AND p_bulk_mode='N' THEN
6303: --
6304: WSH_UTIL_CORE.printmsg('Error msg: '||substr(x_msg_data,1,2000));
6305: END LOOP;
6306:
6307: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
6308: AND p_bulk_mode='N' THEN
6309:
6310: -- for non-bulk mode handle warnings as error;
6311:
6308: AND p_bulk_mode='N' THEN
6309:
6310: -- for non-bulk mode handle warnings as error;
6311:
6312: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6313:
6314: END IF;
6315:
6316:
6313:
6314: END IF;
6315:
6316:
6317: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
6318: IF l_debug_on THEN
6319: WSH_DEBUG_SV.logmsg(l_module_name,'SUCCESS');
6320: END IF;
6321:
6406: END IF;
6407: --hadcp
6408:
6409: commit;
6410: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6411: IF l_debug_on THEN
6412: WSH_DEBUG_SV.logmsg(l_module_name,'WARNING');
6413: END IF;
6414: l_warn_count := l_warn_count + 1;
6563: IF l_debug_on THEN
6564: WSH_DEBUG_SV.logmsg(l_module_name,'DCP Exception');
6565: END IF;
6566:
6567: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_WARNING, WSH_UTIL_CORE.G_RET_STS_SUCCESS)) THEN
6568: IF l_debug_on THEN
6569: WSH_DEBUG_SV.logmsg(l_module_name,'DCP Rollback');
6570: END IF;
6571:
6604: WSH_DEBUG_SV.log(l_module_name,'l_warn_count',l_warn_count);
6605: END IF;
6606:
6607: IF (l_error_count = l_loop_count ) THEN
6608: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6609: IF l_debug_on THEN
6610: WSH_DEBUG_SV.logmsg(l_module_name,'Process Order Error', WSH_DEBUG_SV.C_EXCEP_LEVEL);
6611: END IF;
6612:
6619: END IF;
6620: --hadcp
6621:
6622: ELSIF (l_warn_count > 0 OR (l_error_count > 0 and l_error_count < l_loop_count) ) THEN
6623: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6624: IF l_debug_on THEN
6625: WSH_DEBUG_SV.logmsg(l_module_name,'Process Order Warning'||WSH_DEBUG_SV.C_EXCEP_LEVEL);
6626: END IF;
6627: END IF;
6631: END IF;
6632:
6633: EXCEPTION
6634: WHEN process_freight_costs_failed THEN
6635: WSH_UTIL_CORE.PrintMsg('process_freight_costs_failed');
6636: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6637:
6638: IF l_debug_on THEN
6639: WSH_DEBUG_SV.logmsg(l_module_name,'PROCESS_FREIGHT_COSTS_FAILED exception has occured.',
6632:
6633: EXCEPTION
6634: WHEN process_freight_costs_failed THEN
6635: WSH_UTIL_CORE.PrintMsg('process_freight_costs_failed');
6636: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6637:
6638: IF l_debug_on THEN
6639: WSH_DEBUG_SV.logmsg(l_module_name,'PROCESS_FREIGHT_COSTS_FAILED exception has occured.',
6640: WSH_DEBUG_SV.C_EXCEP_LEVEL);
6653: --hadcp
6654:
6655:
6656: WHEN others THEN
6657: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6658: wsh_util_core.printMsg('API Process_Stop_To_OM failed with an unexpected error');
6659: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || sqlerrm);
6660:
6661: IF l_debug_on THEN
6654:
6655:
6656: WHEN others THEN
6657: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6658: wsh_util_core.printMsg('API Process_Stop_To_OM failed with an unexpected error');
6659: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || sqlerrm);
6660:
6661: IF l_debug_on THEN
6662: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN Process_Stop_To_OM' );
6655:
6656: WHEN others THEN
6657: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6658: wsh_util_core.printMsg('API Process_Stop_To_OM failed with an unexpected error');
6659: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || sqlerrm);
6660:
6661: IF l_debug_on THEN
6662: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN Process_Stop_To_OM' );
6663: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6709: WSH_DEBUG_SV.push(l_module_name);
6710: WSH_DEBUG_SV.log(l_module_name,'p_ship_line.fulfilled_flag.count', p_ship_line.fulfilled_flag.count);
6711: END IF;
6712:
6713: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6714:
6715:
6716: p_ship_line.fulfilled_flag.extend;
6717: p_ship_line.actual_shipment_date.extend;
6750: END IF;
6751:
6752: EXCEPTION
6753: WHEN others THEN
6754: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6755:
6756: IF l_debug_on THEN
6757: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6758: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6933: l_details VARCHAR2(4000) :=NULL;
6934: l_get_msg_count number;
6935: l_error_Code number;
6936: l_error_text varchar2(2000);
6937: l_remain_details_id WSH_UTIL_CORE.Id_Tab_Type;
6938: l_remain_detail_index NUMBER;
6939:
6940:
6941: l_delete_detail_id NUMBER;
6949: -- OM bug 2022029
6950: l_line_qtys OE_SHIP_CONFIRMATION_PUB.Req_Quantity_Tbl_Type;
6951:
6952: -- anxsharm for Load Tender
6953: l_trip_id_tab wsh_util_core.id_tab_type;
6954:
6955: -- sql repository performance bug 4891985 (>1M sharable memory)
6956: -- 1) changed wsh_delivery_assignments_v to wsh_delivery_assignments
6957: -- 2) restructured the query
7244: WSH_DEBUG_SV.push(l_module_name);
7245: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
7246: END IF;
7247:
7248: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7249:
7250:
7251:
7252: l_counter := 0;
7438: IF l_debug_on THEN
7439: WSH_DEBUG_SV.log(l_module_name, 'NON BULK Process_Stop_To_OM l_return_status',l_return_status);
7440: END IF;
7441:
7442: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7443: IF l_debug_on THEN
7444: WSH_DEBUG_SV.logmsg(l_module_name, 'Rolling back to the savepoint its_process_order_non_bulk as ITS for the order completed with an error');
7445: END IF;
7446: --ROLLBACK TO its_process_order_non_bulk;
7500: l_non_bulk_req_line.top_model_line_id.delete;
7501: l_non_bulk_req_line.inventory_item_id.delete;
7502: l_non_bulk_req_line.ship_from_org_id.delete;
7503:
7504: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
7505: x_num_warnings =>l_num_om_warnings,
7506: x_num_errors =>l_num_om_errors,
7507: p_raise_error_flag =>false);
7508:
7660: x_fulfilled_flag => l_fulfilled_flag,
7661: x_return_status => l_return_status);
7662: --haperf
7663:
7664: WSH_UTIL_CORE.api_post_call(p_return_status => l_return_status,
7665: x_num_warnings =>l_num_warnings,
7666: x_num_errors =>l_num_errors);
7667:
7668: IF l_debug_on THEN
7785:
7786: IF l_debug_on THEN
7787: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
7788: END IF;
7789: WSH_UTIL_CORE.api_post_call(p_return_status => l_return_status,
7790: x_num_warnings =>l_num_warnings,
7791: x_num_errors =>l_num_errors);
7792:
7793: END IF;
7829: x_fulfilled_flag => l_fulfilled_flag,
7830: x_return_status => l_return_status);
7831: --haperf
7832:
7833: WSH_UTIL_CORE.api_post_call(p_return_status => l_return_status,
7834: x_num_warnings =>l_num_warnings,
7835: x_num_errors =>l_num_errors);
7836:
7837:
7965: WSH_DEBUG_SV.log(l_module_name, 'Non Bulk Ship l_non_bulk_ship_count',l_non_bulk_ship_count);
7966: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
7967: END IF;
7968:
7969: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
7970: x_num_warnings =>l_num_warnings,
7971: x_num_errors =>l_num_errors);
7972:
7973:
8077: IF l_debug_on THEN
8078: WSH_DEBUG_SV.log(l_module_name, 'Process_Stop_To_OM l_return_status',l_return_status);
8079: END IF;
8080:
8081: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
8082: IF l_debug_on THEN
8083: WSH_DEBUG_SV.logmsg(l_module_name, 'Rolling back to the savepoint its_process_order_non_bulk as ITS for the order completed with an error 2');
8084: END IF;
8085: --ROLLBACK TO its_process_order_non_bulk;
8085: --ROLLBACK TO its_process_order_non_bulk;
8086:
8087: END IF;
8088:
8089: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
8090: raise fnd_api.g_exc_error;
8091: END IF;
8092:
8093:
8090: raise fnd_api.g_exc_error;
8091: END IF;
8092:
8093:
8094: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
8095: x_num_warnings =>l_num_om_warnings,
8096: x_num_errors =>l_num_om_errors,
8097: p_raise_error_flag =>false);
8098: END IF;
8115:
8116: IF l_debug_on THEN
8117: WSH_DEBUG_SV.log(l_module_name, 'Process_Stop_To_OM l_return_status',l_return_status);
8118: END IF;
8119: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
8120: raise fnd_api.g_exc_error;
8121: END IF;
8122:
8123: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
8119: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
8120: raise fnd_api.g_exc_error;
8121: END IF;
8122:
8123: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
8124: x_num_warnings =>l_num_om_warnings,
8125: x_num_errors =>l_num_om_errors,
8126: p_raise_error_flag =>false);
8127: END IF;
8138:
8139: IF ( (l_num_errors >= l_non_bulk_count and l_non_bulk_count > 0 ) OR (l_num_om_errors >= l_num_om_api_call and l_num_om_api_call > 0) ) THEN
8140: raise fnd_api.g_exc_error;
8141: ELSIF ( (l_num_errors > 0 ) OR (l_num_om_errors > 0 and l_num_om_api_call > 0) ) THEN
8142: raise wsh_util_core.g_exc_warning;
8143: ELSIF ( (l_num_warnings > 0 ) OR (l_num_om_warnings > 0 and l_num_om_api_call > 0) ) THEN
8144: raise wsh_util_core.g_exc_warning;
8145: ELSE
8146: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8140: raise fnd_api.g_exc_error;
8141: ELSIF ( (l_num_errors > 0 ) OR (l_num_om_errors > 0 and l_num_om_api_call > 0) ) THEN
8142: raise wsh_util_core.g_exc_warning;
8143: ELSIF ( (l_num_warnings > 0 ) OR (l_num_om_warnings > 0 and l_num_om_api_call > 0) ) THEN
8144: raise wsh_util_core.g_exc_warning;
8145: ELSE
8146: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8147: END IF;
8148:
8142: raise wsh_util_core.g_exc_warning;
8143: ELSIF ( (l_num_warnings > 0 ) OR (l_num_om_warnings > 0 and l_num_om_api_call > 0) ) THEN
8144: raise wsh_util_core.g_exc_warning;
8145: ELSE
8146: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8147: END IF;
8148:
8149:
8150: IF l_debug_on THEN
8154:
8155: EXCEPTION
8156:
8157: WHEN fnd_api.g_exc_error THEN
8158: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8159: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
8160: IF c_oe_interface%ISOPEN THEN
8161: close c_oe_interface;
8162: END IF;
8155: EXCEPTION
8156:
8157: WHEN fnd_api.g_exc_error THEN
8158: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8159: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
8160: IF c_oe_interface%ISOPEN THEN
8161: close c_oe_interface;
8162: END IF;
8163: IF lock_delivery_line%ISOPEN THEN
8184: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
8185: END IF;
8186: --
8187:
8188: WHEN wsh_util_core.g_exc_warning THEN
8189: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8190: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
8191: IF c_oe_interface%ISOPEN THEN
8192: close c_oe_interface;
8185: END IF;
8186: --
8187:
8188: WHEN wsh_util_core.g_exc_warning THEN
8189: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8190: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
8191: IF c_oe_interface%ISOPEN THEN
8192: close c_oe_interface;
8193: END IF;
8186: --
8187:
8188: WHEN wsh_util_core.g_exc_warning THEN
8189: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8190: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
8191: IF c_oe_interface%ISOPEN THEN
8192: close c_oe_interface;
8193: END IF;
8194: IF lock_delivery_line%ISOPEN THEN
8210: close c_picked_dd;
8211: END IF;
8212:
8213: IF l_debug_on THEN
8214: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_util_core.g_exc_warning exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
8215: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
8216: END IF;
8217:
8218: WHEN fnd_api.g_exc_unexpected_error THEN
8211: END IF;
8212:
8213: IF l_debug_on THEN
8214: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_util_core.g_exc_warning exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
8215: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
8216: END IF;
8217:
8218: WHEN fnd_api.g_exc_unexpected_error THEN
8219: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8215: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
8216: END IF;
8217:
8218: WHEN fnd_api.g_exc_unexpected_error THEN
8219: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8220: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
8221: IF c_oe_interface%ISOPEN THEN
8222: close c_oe_interface;
8223: END IF;
8216: END IF;
8217:
8218: WHEN fnd_api.g_exc_unexpected_error THEN
8219: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8220: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
8221: IF c_oe_interface%ISOPEN THEN
8222: close c_oe_interface;
8223: END IF;
8224: IF lock_delivery_line%ISOPEN THEN
8241: END IF;
8242:
8243: IF l_debug_on THEN
8244: WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_unexpected exception has occured: '||SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
8245: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
8246: END IF;
8247:
8248:
8249: WHEN line_locked THEN
8246: END IF;
8247:
8248:
8249: WHEN line_locked THEN
8250: wsh_util_core.printMsg('Error: Failure to Accrue the Lock for above line,Please try after sometime');
8251: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8252: IF c_oe_interface%ISOPEN THEN
8253: close c_oe_interface;
8254: END IF;
8247:
8248:
8249: WHEN line_locked THEN
8250: wsh_util_core.printMsg('Error: Failure to Accrue the Lock for above line,Please try after sometime');
8251: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8252: IF c_oe_interface%ISOPEN THEN
8253: close c_oe_interface;
8254: END IF;
8255: IF lock_delivery_line%ISOPEN THEN
8277: END IF;
8278: --
8279:
8280: WHEN others THEN
8281: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8282: l_error_code := SQLCODE;
8283: l_error_text := SQLERRM;
8284: -- bug 2657859 frontport bug 2685584: close open cursors
8285: IF c_oe_interface%ISOPEN THEN
8302: END IF;
8303: IF c_picked_dd%ISOPEN THEN
8304: close c_picked_dd;
8305: END IF;
8306: wsh_util_core.printMsg('API interface_line_to_OM failed with an unexpected error');
8307: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8308: --
8309: IF l_debug_on THEN
8310: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN INTERFACE_HEADER_TO_OM ' );
8303: IF c_picked_dd%ISOPEN THEN
8304: close c_picked_dd;
8305: END IF;
8306: wsh_util_core.printMsg('API interface_line_to_OM failed with an unexpected error');
8307: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8308: --
8309: IF l_debug_on THEN
8310: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN INTERFACE_HEADER_TO_OM ' );
8311: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM
8370: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
8371: WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_HEADER_ID',P_TRANSACTION_HEADER_ID);
8372: END IF;
8373: --
8374: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8375: -- mark all 'N' as 'P'
8376:
8377: /* record is already processed by inventory */
8378: update wsh_delivery_details
8433: /* what should we do if the online processer is hanging
8434: because manager is not aviable. User needs to be informed */
8435:
8436: if (l_outcome <> TRUE ) THEN
8437: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
8438: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
8439: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
8440: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
8441: IF l_debug_on THEN
8434: because manager is not aviable. User needs to be informed */
8435:
8436: if (l_outcome <> TRUE ) THEN
8437: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
8438: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
8439: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
8440: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
8441: IF l_debug_on THEN
8442: WSH_DEBUG_SV.log(l_module_name,'Error Code:',x_error_code);
8435:
8436: if (l_outcome <> TRUE ) THEN
8437: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
8438: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
8439: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
8440: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
8441: IF l_debug_on THEN
8442: WSH_DEBUG_SV.log(l_module_name,'Error Code:',x_error_code);
8443: WSH_DEBUG_SV.log(l_module_name,'Error Explanation::',
8436: if (l_outcome <> TRUE ) THEN
8437: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
8438: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
8439: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
8440: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
8441: IF l_debug_on THEN
8442: WSH_DEBUG_SV.log(l_module_name,'Error Code:',x_error_code);
8443: WSH_DEBUG_SV.log(l_module_name,'Error Explanation::',
8444: x_error_explanation);
8448: FOR i in 1 .. l_process_online_msg_count
8449: LOOP
8450: l_process_online_message := fnd_msg_pub.get(i,'T');
8451: l_process_online_message := replace(l_process_online_message,fnd_global.local_chr(0), ' ');
8452: WSH_UTIL_CORE.PrintMsg(l_process_online_message);
8453: IF l_debug_on THEN
8454: WSH_DEBUG_SV.log(l_module_name,'Error txt:',
8455: SUBSTR(l_process_online_message,1,200));
8456: END IF;
8456: END IF;
8457: END LOOP;
8458: end if;
8459:
8460: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8461: --
8462: IF l_debug_on THEN
8463: WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
8464: WSH_DEBUG_SV.pop(l_module_name);
8470: -- update to 'Y' where where inv_interfaced_flag = 'P' and dd_id in mmt and trx_hdr matches and mtl.delivery_id matches
8471:
8472: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
8473:
8474: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
8475: x_return_status := l_return_status ;
8476: WSH_UTIL_CORE.PrintMsg('process_inv_online failed for batch '|| p_batch_id ||': txn '
8477: || p_transaction_header_id );
8478: --
8472: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
8473:
8474: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
8475: x_return_status := l_return_status ;
8476: WSH_UTIL_CORE.PrintMsg('process_inv_online failed for batch '|| p_batch_id ||': txn '
8477: || p_transaction_header_id );
8478: --
8479: IF l_debug_on THEN
8480: WSH_DEBUG_SV.log(l_module_name,'process_inv_online failed for Batch '
8491: END IF;
8492: --
8493: EXCEPTION
8494: WHEN others THEN
8495: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8496: l_error_code := SQLCODE;
8497: l_error_text := SQLERRM;
8498: WSH_UTIL_CORE.PrintMsg(p_transaction_header_id ||': process_inv_online failed ');
8499: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8494: WHEN others THEN
8495: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8496: l_error_code := SQLCODE;
8497: l_error_text := SQLERRM;
8498: WSH_UTIL_CORE.PrintMsg(p_transaction_header_id ||': process_inv_online failed ');
8499: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8500: --
8501: IF l_debug_on THEN
8502: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8495: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8496: l_error_code := SQLCODE;
8497: l_error_text := SQLERRM;
8498: WSH_UTIL_CORE.PrintMsg(p_transaction_header_id ||': process_inv_online failed ');
8499: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8500: --
8501: IF l_debug_on THEN
8502: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8503: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8572: --
8573: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
8574: END IF;
8575: --
8576: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8577: OPEN c_details(p_delivery_detail_id);
8578: FETCH c_details INTO l_detail_rec;
8579: CLOSE c_details;
8580:
8666: RETURN(l_account);
8667:
8668: EXCEPTION
8669: WHEN NO_DEF_GOODS_DISPATCHED_ACCT THEN
8670: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8671: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
8672: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
8673: -- bug 2657859 frontport bug 2685584: close open cursors
8674: IF c_details%ISOPEN THEN
8667:
8668: EXCEPTION
8669: WHEN NO_DEF_GOODS_DISPATCHED_ACCT THEN
8670: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8671: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
8672: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
8673: -- bug 2657859 frontport bug 2685584: close open cursors
8674: IF c_details%ISOPEN THEN
8675: close c_details;
8668: EXCEPTION
8669: WHEN NO_DEF_GOODS_DISPATCHED_ACCT THEN
8670: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8671: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
8672: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
8673: -- bug 2657859 frontport bug 2685584: close open cursors
8674: IF c_details%ISOPEN THEN
8675: close c_details;
8676: END IF;
8676: END IF;
8677: IF c_dispatch_account%ISOPEN THEN
8678: close c_dispatch_account;
8679: END IF;
8680: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8681: --
8682: IF l_debug_on THEN
8683: WSH_DEBUG_SV.logmsg(l_module_name,'NO_DEF_GOODS_DISPATCHED_ACCT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
8684: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DEF_GOODS_DISPATCHED_ACCT');
8687: RETURN NULL;
8688:
8689: -- bug 2657859 frontport bug 2685584: catch other exceptions
8690: WHEN others THEN
8691: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8692: l_error_code := SQLCODE;
8693: l_error_text := SQLERRM;
8694: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
8695: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
8690: WHEN others THEN
8691: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8692: l_error_code := SQLCODE;
8693: l_error_text := SQLERRM;
8694: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
8695: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
8696: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8697: IF c_details%ISOPEN THEN
8698: close c_details;
8691: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8692: l_error_code := SQLCODE;
8693: l_error_text := SQLERRM;
8694: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
8695: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
8696: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8697: IF c_details%ISOPEN THEN
8698: close c_details;
8699: END IF;
8692: l_error_code := SQLCODE;
8693: l_error_text := SQLERRM;
8694: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
8695: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
8696: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
8697: IF c_details%ISOPEN THEN
8698: close c_details;
8699: END IF;
8700: IF c_dispatch_account%ISOPEN THEN
8699: END IF;
8700: IF c_dispatch_account%ISOPEN THEN
8701: close c_dispatch_account;
8702: END IF;
8703: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8704: IF l_debug_on THEN
8705: WSH_DEBUG_SV.logmsg(l_module_name,'Unhandled exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
8706: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8707: END IF;
8957: || 'INTERFACE_ALL_WRP';
8958: --
8959: l_req_data VARCHAR2(100);
8960: l_num_requests NUMBER;
8961: l_child_req_ids wsh_util_core.id_tab_type;
8962: l_request_id NUMBER;
8963: l_this_request NUMBER;
8964: j NUMBER;
8965: l_dummy BOOLEAN;
8982: e_invalid_number EXCEPTION;
8983:
8984: BEGIN
8985: --
8986: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
8987: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
8988: --
8989: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
8990: --
8983:
8984: BEGIN
8985: --
8986: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
8987: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
8988: --
8989: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
8990: --
8991: IF l_debug_on IS NULL
9146: WHEN OTHERS THEN
9147: l_completion_status := 'ERROR';
9148: l_error_code := SQLCODE;
9149: l_error_text := SQLERRM;
9150: WSH_UTIL_CORE.PrintMsg('Interface_ALL_wrp failed with unexpected error.');
9151: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
9152: l_dummy := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
9153: errbuf := 'Interface trip stop failed with unexpected error';
9154: retcode := '2';
9147: l_completion_status := 'ERROR';
9148: l_error_code := SQLCODE;
9149: l_error_text := SQLERRM;
9150: WSH_UTIL_CORE.PrintMsg('Interface_ALL_wrp failed with unexpected error.');
9151: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
9152: l_dummy := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
9153: errbuf := 'Interface trip stop failed with unexpected error';
9154: retcode := '2';
9155: --