[Home] [Help]
618: WSH_DEBUG_SV.log(l_module_name,'p_interfacing',p_interfacing);
619: END IF;
620: --
621:
622: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
623:
624: IF p_interfacing = 'OM' THEN
625: IF p_transfer_param = 'MSNT' THEN
626: OPEN dd_msnt_rec_om;
976: IF l_debug_on THEN
977: 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);
978: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
979: END IF;
980: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS_PVT');
981: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
982:
983: END transfer_serial_numbers_pvt;
984:
977: 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);
978: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
979: END IF;
980: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS_PVT');
981: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
982:
983: END transfer_serial_numbers_pvt;
984:
985: --========================================================================
1020: WSH_DEBUG_SV.log(l_module_name,'p_interfacing',p_interfacing);
1021: END IF;
1022: --
1023:
1024: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1025:
1026: -- Call transfer_serial_numbers_pvt
1027:
1028: -- Case I : Serial Records where 'From Serial Number' <> 'To Serial Number'
1052: IF l_debug_on THEN
1053: 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);
1054: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1055: END IF;
1056: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS');
1057: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1058:
1059: END transfer_serial_numbers;
1060:
1053: 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);
1054: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1055: END IF;
1056: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.TRANSFER_SERIAL_NUMBERS');
1057: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1058:
1059: END transfer_serial_numbers;
1060:
1061: -- End of Changes for creating wrapper and new API transfer_serial_numbers_pvt
1097: WSH_DEBUG_SV.log(l_module_name,'Submitting the request');
1098: l_log_level := 1;
1099: END IF;
1100: --
1101: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1102: -- bug 2308504: make sure to pass all parameters expected,
1103: -- so that code output will go into the log file.
1104: request_id := FND_REQUEST.submit_Request('WSH', 'WSHINTERFACE', '', '', FALSE,
1105: 'ALL', -- mode
1113: raise inv_inter_req_submission;
1114: else
1115: FND_MESSAGE.SET_NAME('WSH', 'WSH_DET_INV_INT_SUBMITTED');
1116: FND_MESSAGE.SET_TOKEN('REQ_ID', to_char(request_id));
1117: WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1118: END if;
1119:
1120: IF l_debug_on THEN
1121: WSH_DEBUG_SV.pop(l_module_name);
1122: END IF;
1123: --
1124: exception
1125: WHEN inv_inter_req_submission THEN
1126: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1127: fnd_message.set_name('WSH', 'WSH_DET_INV_INT_REQ_SUBMISSION');
1128: WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1129: IF l_debug_on THEN
1130: WSH_DEBUG_SV.logmsg(l_module_name,'INV_INTER_REQ_SUBMISSION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1124: exception
1125: WHEN inv_inter_req_submission THEN
1126: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1127: fnd_message.set_name('WSH', 'WSH_DET_INV_INT_REQ_SUBMISSION');
1128: WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1129: IF l_debug_on THEN
1130: WSH_DEBUG_SV.logmsg(l_module_name,'INV_INTER_REQ_SUBMISSION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1131: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INV_INTER_REQ_SUBMISSION');
1132: END IF;
1131: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INV_INTER_REQ_SUBMISSION');
1132: END IF;
1133: --
1134: WHEN others THEN
1135: wsh_util_core.default_handler('WSH_SHIP_CONFRIM_ACTIONS.SHIP_CONFIRM_A_TRIP_STOP',l_module_name);
1136: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1137: IF l_debug_on THEN
1138: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1139: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1132: END IF;
1133: --
1134: WHEN others THEN
1135: wsh_util_core.default_handler('WSH_SHIP_CONFRIM_ACTIONS.SHIP_CONFIRM_A_TRIP_STOP',l_module_name);
1136: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1137: IF l_debug_on THEN
1138: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1139: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1140: END IF;
1236: x_master_status := p_api_status;
1237: END IF;
1238: ELSE
1239: -- unknown status...
1240: WSH_UTIL_CORE.PrintMsg('ERROR: unknown status = '''
1241: || p_api_status || '''');
1242: x_master_status := 'ERROR';
1243: END IF;
1244: --
1288: --
1289: --========================================================================
1290:
1291: PROCEDURE handle_stop_lvl_splits(
1292: x_split_stops IN OUT NOCOPY wsh_util_core.id_tab_type,
1293: p_batch_id IN NUMBER,
1294: x_stop_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
1295: x_return_status OUT NOCOPY VARCHAR2)
1296: IS
1290:
1291: PROCEDURE handle_stop_lvl_splits(
1292: x_split_stops IN OUT NOCOPY wsh_util_core.id_tab_type,
1293: p_batch_id IN NUMBER,
1294: x_stop_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
1295: x_return_status OUT NOCOPY VARCHAR2)
1296: IS
1297: --
1298: l_debug_on BOOLEAN;
1305: l_last NUMBER;
1306: z NUMBER := 1;
1307: l_found BOOLEAN;
1308:
1309: l_new_split_stops wsh_util_core.id_tab_type;
1310: l_stop_tab wsh_util_core.id_tab_type;
1311:
1312:
1313: CURSOR get_stops(p_batch_id NUMBER) is
1306: z NUMBER := 1;
1307: l_found BOOLEAN;
1308:
1309: l_new_split_stops wsh_util_core.id_tab_type;
1310: l_stop_tab wsh_util_core.id_tab_type;
1311:
1312:
1313: CURSOR get_stops(p_batch_id NUMBER) is
1314: SELECT DISTINCT wts.stop_id
1355: WSH_DEBUG_SV.log(l_module_name,'x_stop_tab.count', x_stop_tab.COUNT);
1356: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
1357: END IF;
1358:
1359: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1360:
1361: l_stop_tab := x_stop_tab;
1362: x_stop_tab.DELETE;
1363:
1441: END IF;
1442:
1443: EXCEPTION
1444: WHEN OTHERS THEN
1445: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1446:
1447: IF l_debug_on THEN
1448: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1449: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1527: l_error_code NUMBER;
1528: l_error_text VARCHAR2(2000);
1529: l_temp BOOLEAN;
1530: l_stops_count NUMBER := 0;
1531: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1532: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1533: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1534: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1535: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1528: l_error_text VARCHAR2(2000);
1529: l_temp BOOLEAN;
1530: l_stops_count NUMBER := 0;
1531: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1532: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1533: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1534: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1535: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1536:
1529: l_temp BOOLEAN;
1530: l_stops_count NUMBER := 0;
1531: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1532: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1533: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1534: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1535: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1536:
1537: l_inv_interface NUMBER := 0;
1530: l_stops_count NUMBER := 0;
1531: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1532: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1533: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1534: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1535: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1536:
1537: l_inv_interface NUMBER := 0;
1538: l_om_interface NUMBER := 0;
1531: l_interface_names WSH_UTIL_CORE.Column_Tab_Type;
1532: l_stops_normal WSH_UTIL_CORE.Id_Tab_Type;
1533: l_stops_warning WSH_UTIL_CORE.Id_Tab_Type;
1534: l_stops_interfaced WSH_UTIL_CORE.Id_Tab_Type;
1535: l_err_stops WSH_UTIL_CORE.Id_Tab_Type;
1536:
1537: l_inv_interface NUMBER := 0;
1538: l_om_interface NUMBER := 0;
1539: l_dsno_interface NUMBER := 0;
1546: l_stop_per_batch_counter NUMBER := 1;
1547: l_batch_ready BOOLEAN := FALSE;
1548: l_stop_batch_id NUMBER;
1549: -- Stops that will be used for DSNO interface are put in l_dsno_stop_tab.
1550: l_dsno_stop_tab wsh_util_core.id_Tab_type;
1551: -- The stops being processed for OM interface are stored in l_stop_tab
1552: l_stop_tab wsh_util_core.id_Tab_type;
1553: l_tab_count NUMBER;
1554: l_err_stops_count NUMBER;
1548: l_stop_batch_id NUMBER;
1549: -- Stops that will be used for DSNO interface are put in l_dsno_stop_tab.
1550: l_dsno_stop_tab wsh_util_core.id_Tab_type;
1551: -- The stops being processed for OM interface are stored in l_stop_tab
1552: l_stop_tab wsh_util_core.id_Tab_type;
1553: l_tab_count NUMBER;
1554: l_err_stops_count NUMBER;
1555: l_completion_status_bkp VARCHAR2(30);
1556: l_stop_count NUMBER := 0;
1555: l_completion_status_bkp VARCHAR2(30);
1556: l_stop_count NUMBER := 0;
1557: l_api_completion_status_bkp VARCHAR2(30);
1558: l_return_status VARCHAR2(10);
1559: l_inv_batch_table WSH_UTIL_CORE.Id_Tab_Type;
1560: l_index number;
1561: x NUMBER;
1562: l_num_warnings NUMBER := 0;
1563: l_num_errors NUMBER := 0;
1562: l_num_warnings NUMBER := 0;
1563: l_num_errors NUMBER := 0;
1564: l_invoicing_method VARCHAR2(100);
1565: -- stops that are processed for INV interface are put in l_inv_stops.
1566: l_inv_stops wsh_util_core.id_tab_type;
1567: l_split_stops wsh_util_core.id_tab_type;
1568:
1569: -- bug 2657859 frontport bug 2630535: avoid deadlocks
1570: CURSOR lock_row ( p_stop_id in NUMBER, p_flag in VARCHAR2 ) IS
1563: l_num_errors NUMBER := 0;
1564: l_invoicing_method VARCHAR2(100);
1565: -- stops that are processed for INV interface are put in l_inv_stops.
1566: l_inv_stops wsh_util_core.id_tab_type;
1567: l_split_stops wsh_util_core.id_tab_type;
1568:
1569: -- bug 2657859 frontport bug 2630535: avoid deadlocks
1570: CURSOR lock_row ( p_stop_id in NUMBER, p_flag in VARCHAR2 ) IS
1571: SELECT stop_id
1684: AND wdl.pick_up_stop_id = p_stop_id
1685: AND wts.stop_id = wdl.pick_up_stop_id
1686: AND wts.stop_location_id = wnd.initial_pickup_location_id;
1687:
1688: l_del_entity_ids WSH_UTIL_CORE.column_tab_type;
1689: l_wf_rs VARCHAR2(1);
1690: l_override_wf VARCHAR2(1);
1691: l_purged_count NUMBER;
1692: e_trip_stop_wf_inprogress EXCEPTION;
1704: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INTERFACE_ALL';
1705: --
1706: BEGIN
1707: --
1708: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1709: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
1710: --
1711: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1712: --
1705: --
1706: BEGIN
1707: --
1708: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1709: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
1710: --
1711: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1712: --
1713: IF l_debug_on IS NULL
1802: IF l_debug_on THEN
1803: WSH_DEBUG_SV.log(l_module_name,'l_stop_id',l_stop_id);
1804: END IF;
1805: IF l_stop_id IS NULL THEN
1806: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: cannot find stop. Exiting.');
1807: l_completion_status := 'WARNING';
1808: goto interface_end;
1809: END IF;
1810: END IF;
1810: END IF;
1811:
1812:
1813: IF l_stop_id IS NULL THEN
1814: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing all eligible stops for '
1815: || l_interface_mode);
1816: ELSE
1817: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing stop_id '
1818: || TO_CHAR(l_stop_id)
1813: IF l_stop_id IS NULL THEN
1814: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing all eligible stops for '
1815: || l_interface_mode);
1816: ELSE
1817: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: processing stop_id '
1818: || TO_CHAR(l_stop_id)
1819: || ' for ' || l_interface_mode);
1820: END IF;
1821:
2058: p_batch_id => l_stop_batch_id,
2059: x_stop_tab => l_stop_tab,
2060: x_return_status => l_return_status);
2061:
2062: wsh_util_core.api_post_call(
2063: p_return_status => l_return_status,
2064: x_num_warnings => l_num_warnings,
2065: x_num_errors => l_num_errors);
2066:
2075: IF l_debug_on THEN
2076: WSH_DEBUG_SV.log(l_module_name,
2077: 'stop is locked');
2078: END IF;
2079: WSH_UTIL_CORE.Println('Interface_All: skipping locked stop_id ' || l_stop_id || ' is locked.');
2080: IF lock_row%ISOPEN THEN
2081: CLOSE lock_row;
2082: END IF;
2083: --IF l_debug_on THEN
2100:
2101: l_completion_status := 'ERROR';
2102: l_error_code := SQLCODE;
2103: l_error_text := SQLERRM;
2104: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_id);
2105: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2106: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2107: errbuf := 'Interface trip stop failed with unexpected error';
2108: retcode := '2';
2101: l_completion_status := 'ERROR';
2102: l_error_code := SQLCODE;
2103: l_error_text := SQLERRM;
2104: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_id);
2105: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2106: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2107: errbuf := 'Interface trip stop failed with unexpected error';
2108: retcode := '2';
2109: IF lock_row%ISOPEN THEN
2485:
2486: EXCEPTION
2487: WHEN trip_stop_locked THEN
2488: -- stop is locked; probably used by another process
2489: WSH_UTIL_CORE.PrintMsg('Locking issue: batch '|| l_stop_batch_id || ' needs pending_interface_flag updated to ' || l_new_flag);
2490: IF lock_row%ISOPEN THEN
2491: CLOSE lock_row;
2492: END IF;
2493: IF lock_batch%ISOPEN THEN
2502: END IF;
2503: l_completion_status := 'ERROR';
2504: l_error_code := SQLCODE;
2505: l_error_text := SQLERRM;
2506: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_batch_id);
2507: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2508: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2509: errbuf := 'Interface trip stop failed with unexpected error';
2510: retcode := '2';
2503: l_completion_status := 'ERROR';
2504: l_error_code := SQLCODE;
2505: l_error_text := SQLERRM;
2506: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error in ' || l_action || ' ' || l_stop_batch_id);
2507: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2508: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2509: errbuf := 'Interface trip stop failed with unexpected error';
2510: retcode := '2';
2511: IF lock_row%ISOPEN THEN
2524:
2525:
2526: <
2527: IF l_completion_status = 'ERROR' THEN
2528: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: Stopping because of ERROR.');
2529: IF l_debug_on THEN
2530: WSH_DEBUG_SV.log(l_module_name,
2531: 'InterfaceTripStop: Stopping because of ERROR.');
2532: END IF;
2538: IF l_debug_on THEN
2539: WSH_DEBUG_SV.log(l_module_name,
2540: 'Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2541: END IF;
2542: WSH_UTIL_CORE.Println('Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2543: WSH_UTIL_CORE.Println('Interface_All: skipping stop_id ' || l_stop_id );
2544: --==/
2545:
2546: WHEN e_continue THEN
2539: WSH_DEBUG_SV.log(l_module_name,
2540: 'Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2541: END IF;
2542: WSH_UTIL_CORE.Println('Ship to Deliver Workflow is enabled for one or more deliveries related to this Stop');
2543: WSH_UTIL_CORE.Println('Interface_All: skipping stop_id ' || l_stop_id );
2544: --==/
2545:
2546: WHEN e_continue THEN
2547: NULL;
2568: END IF;
2569: --
2570:
2571: IF l_stops_count = 0 THEN
2572: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: no stop is processed because no lines are eligble for interfacing.');
2573: ELSE
2574: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: total stops processed: '
2575: || l_stops_count);
2576:
2570:
2571: IF l_stops_count = 0 THEN
2572: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: no stop is processed because no lines are eligble for interfacing.');
2573: ELSE
2574: WSH_UTIL_CORE.PrintMsg('InterfaceTripStop: total stops processed: '
2575: || l_stops_count);
2576:
2577: FOR i IN 1..l_interface_names.COUNT LOOP
2578: IF l_stops_normal(i) > 0
2577: FOR i IN 1..l_interface_names.COUNT LOOP
2578: IF l_stops_normal(i) > 0
2579: OR l_stops_warning(i) > 0
2580: OR l_stops_interfaced(i) > 0 THEN
2581: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2582: || l_interface_names(i)
2583: || ' with status NORMAL: '
2584: || l_stops_normal(i));
2585: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2581: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2582: || l_interface_names(i)
2583: || ' with status NORMAL: '
2584: || l_stops_normal(i));
2585: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2586: || l_interface_names(i)
2587: || ' with status WARNING: '
2588: || l_stops_warning(i));
2589: WSH_UTIL_CORE.PrintMsg('Stops interfaced for '
2585: WSH_UTIL_CORE.PrintMsg('Stops processed for '
2586: || l_interface_names(i)
2587: || ' with status WARNING: '
2588: || l_stops_warning(i));
2589: WSH_UTIL_CORE.PrintMsg('Stops interfaced for '
2590: || l_interface_names(i)
2591: || ': '
2592: || l_stops_interfaced(i));
2593: IF l_debug_on THEN
2658:
2659: COMMIT;
2660:
2661: IF l_recs > 0 THEN
2662: WSH_UTIL_CORE.PrintMsg('Cleaned up ' || to_char(l_recs) || ' stuck stops.');
2663: IF l_debug_on THEN
2664: WSH_DEBUG_SV.log(l_module_name,
2665: 'Cleaned up ' || to_char(l_recs) || ' stuck stops.');
2666: END IF;
2676: WHEN OTHERS THEN
2677: l_completion_status := 'ERROR';
2678: l_error_code := SQLCODE;
2679: l_error_text := SQLERRM;
2680: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error.');
2681: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2682: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2683: errbuf := 'Interface trip stop failed with unexpected error';
2684: retcode := '2';
2677: l_completion_status := 'ERROR';
2678: l_error_code := SQLCODE;
2679: l_error_text := SQLERRM;
2680: WSH_UTIL_CORE.PrintMsg('Interface_ALL failed with unexpected error.');
2681: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2682: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2683: errbuf := 'Interface trip stop failed with unexpected error';
2684: retcode := '2';
2685: --
2816: WSH_DEBUG_SV.log(l_module_name,'p_mtl_lot_txn_if_rec.count',p_mtl_lot_txn_if_rec.source_line_id.count);
2817: WSH_DEBUG_SV.log(l_module_name,'p_def_inv_online',p_def_inv_online);
2818: END IF;
2819:
2820: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2821:
2822:
2823: SAVEPOINT sp_insert_inv_records;
2824:
2833: IF l_debug_on THEN
2834: WSH_DEBUG_SV.log(l_module_name,'WSH_TRX_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
2835: END IF;
2836:
2837: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
2838: raise fnd_api.g_exc_error;
2839: END IF;
2840: END IF;
2841:
2849: IF l_debug_on THEN
2850: WSH_DEBUG_SV.log(l_module_name,'WSH_TRXSN_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
2851: END IF;
2852:
2853: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
2854: raise fnd_api.g_exc_error;
2855: END IF;
2856:
2857: p_mtl_ser_txn_if_rec.source_code.delete;
2873: IF l_debug_on THEN
2874: WSH_DEBUG_SV.log(l_module_name,'WSH_TRXLOT_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
2875: END IF;
2876:
2877: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
2878: raise fnd_api.g_exc_error;
2879: END IF;
2880:
2881: p_mtl_lot_txn_if_rec.source_code.delete;
2899: END IF;
2900:
2901: EXCEPTION
2902: WHEN FND_API.G_EXC_ERROR THEN
2903: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2904:
2905: IF l_debug_on THEN
2906: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2907: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2909:
2910: ROLLBACK TO SAVEPOINT sp_insert_inv_records;
2911:
2912: WHEN OTHERS THEN
2913: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2914:
2915: IF l_debug_on THEN
2916: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is
2917: '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3162: l_requisition_line_id NUMBER :=NULL;
3163: l_dummy_ser_trx_interface_id number := NULL;
3164: l_trx_source_id NUMBER := NULL;
3165: l_account NUMBER := NULL;
3166: l_account_return_status VARCHAR2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3167: -- HW OPMCONV. Removed OPM local variables
3168:
3169: l_return_status varchar2(30);
3170: l_message_count NUMBER;
3456: WSH_DEBUG_SV.log(l_module_name,'batch_id',p_batch_id);
3457: WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_HEADER_ID',P_TRANSACTION_HEADER_ID);
3458: END IF;
3459:
3460: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3461: -- HW OPMCONV. Removed OPM variables
3462:
3463:
3464: --Bulk fetch c_details_for_interface INTO l_mtl_txn_if_rec;
3542:
3543: l_index := l_mtl_txn_if_rec.picking_line_id.FIRST;
3544: IF nvl(l_index,0) = 0 THEN
3545: -- perhaps the inventory transaction manager is processing this
3546: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3547: IF l_debug_on THEN
3548: WSH_DEBUG_SV.log(l_module_name,'No Delivery Detail Found',l_index);
3549: WSH_DEBUG_SV.pop(l_module_name);
3550: END IF;
3580:
3581: ---2:
3582: -- check for Actual Departure Date
3583: IF l_mtl_txn_if_rec.trx_date(l_index) IS NULL THEN
3584: WSH_UTIL_CORE.PrintMsg('Actual Departure Date of Trip Stop in batch '|| p_batch_id ||' is NULL');
3585: IF l_debug_on THEN
3586: WSH_DEBUG_SV.log(l_module_name, 'Actual Departure Date of Trip Stop is NULL in batch ',p_batch_id);
3587: END IF;
3588:
3590: END IF;
3591: ---2:
3592: --Bug 9611416 check for Actual Departure Date > sysdate
3593: IF l_mtl_txn_if_rec.trx_date(l_index) > sysdate THEN
3594: WSH_UTIL_CORE.PrintMsg('Actual Departure Date of Trip Stop in batch '|| p_batch_id ||' is a future date');
3595: IF l_debug_on THEN
3596: WSH_DEBUG_SV.log(l_module_name, 'Actual Departure Date of Trip Stop is a future date',l_mtl_txn_if_rec.trx_date(l_index));
3597: WSH_DEBUG_SV.log(l_module_name, 'Delivery Detail id ',l_mtl_txn_if_rec.picking_line_id(l_index));
3598: END IF;
3727: OPEN c_order_line_info(l_mtl_txn_if_rec.source_line_id(l_index));
3728: FETCH c_order_line_info into l_order_line_info;
3729: IF (c_order_line_info%NOTFOUND) THEN
3730: CLOSE c_order_line_info;
3731: WSH_UTIL_CORE.PrintMsg('Sales order not valid');
3732: IF l_debug_on THEN
3733: WSH_DEBUG_SV.log(l_module_name,'Sales order not valid');
3734: END IF;
3735: raise e_line_error;
3751: OPEN c_po_info(l_order_line_info.source_document_line_id, l_order_line_info.source_document_id);
3752: FETCH c_po_info into l_po_info;
3753: IF c_po_info%NOTFOUND THEN
3754: CLOSE c_po_info;
3755: WSH_UTIL_CORE.PrintMsg('Requisition line not found');
3756: IF l_debug_on THEN
3757: WSH_DEBUG_SV.log(l_module_name,'Requisition line not found');
3758: END IF;
3759: raise e_line_error;
3869: END IF;
3870:
3871: IF ( l_account = -11 ) OR l_account IS NULL THEN
3872: IF l_account = -11 THEN
3873: WSH_UTIL_CORE.PRINTMsg ( 'Error: More than one Distribution accounts ' || l_account );
3874: ELSE
3875: WSH_UTIL_CORE.PRINTMsg ( 'No Distribution account ' || l_account );
3876: END IF;
3877: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3871: IF ( l_account = -11 ) OR l_account IS NULL THEN
3872: IF l_account = -11 THEN
3873: WSH_UTIL_CORE.PRINTMsg ( 'Error: More than one Distribution accounts ' || l_account );
3874: ELSE
3875: WSH_UTIL_CORE.PRINTMsg ( 'No Distribution account ' || l_account );
3876: END IF;
3877: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3878: wsh_shipping_params_pvt.get(
3879: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3873: WSH_UTIL_CORE.PRINTMsg ( 'Error: More than one Distribution accounts ' || l_account );
3874: ELSE
3875: WSH_UTIL_CORE.PRINTMsg ( 'No Distribution account ' || l_account );
3876: END IF;
3877: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
3878: wsh_shipping_params_pvt.get(
3879: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
3880: x_param_info => l_ship_params,
3881: x_return_status => l_sp_ret_status);
3882:
3883: IF l_debug_on THEN
3884: WSH_DEBUG_SV.log(l_module_name,'wsh_shipping_params_pvt.get l_sp_ret_status',l_sp_ret_status);
3885: END IF;
3886: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3887: wsh_util_core.printmsg('Unable to get shipping parameters for org '
3888: || l_mtl_txn_if_rec.organization_id(l_index));
3889:
3890: IF l_debug_on THEN
3883: IF l_debug_on THEN
3884: WSH_DEBUG_SV.log(l_module_name,'wsh_shipping_params_pvt.get l_sp_ret_status',l_sp_ret_status);
3885: END IF;
3886: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3887: wsh_util_core.printmsg('Unable to get shipping parameters for org '
3888: || l_mtl_txn_if_rec.organization_id(l_index));
3889:
3890: IF l_debug_on THEN
3891: WSH_DEBUG_SV.log(l_module_name,'Unable to get shipping parameters for org ',
3900: WSH_DEBUG_SV.logmsg(l_module_name, ' ACCOUNT_ID:' || L_ACCOUNT );
3901: END IF;
3902:
3903: IF ( l_account IS NULL ) THEN
3904: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
3905: l_mtl_txn_if_rec.organization_id(l_index));
3906: raise e_line_warning;
3907: END IF;
3908:
3944: -- FND_API.G_RET_STS_ERROR - on expected error
3945: -- FND_API.G_RET_STS_UNEXP_ERROR - on unexpected error
3946: );
3947:
3948: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3949: --Handle Return Status
3950: x_return_status := l_return_status;
3951:
3952: IF l_debug_on THEN
3993: WSH_DEBUG_SV.logmsg(l_module_name, 'RET_STATUS:'|| L_ACCOUNT_RETURN_STATUS ||
3994: ' ACCOUNT_ID:' || L_ACCOUNT );
3995: END IF;
3996:
3997: IF ( l_account_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3998: WSH_UTIL_CORE.PrintMsg('Unable to get account for delivery detail id ' ||
3999: l_mtl_txn_if_rec.picking_line_id(l_index));
4000: raise e_line_warning;
4001: END IF ;
3994: ' ACCOUNT_ID:' || L_ACCOUNT );
3995: END IF;
3996:
3997: IF ( l_account_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3998: WSH_UTIL_CORE.PrintMsg('Unable to get account for delivery detail id ' ||
3999: l_mtl_txn_if_rec.picking_line_id(l_index));
4000: raise e_line_warning;
4001: END IF ;
4002:
4007: OPEN c_convert_locId(l_mtl_txn_if_rec.ship_to_location_id(l_index));
4008: FETCH c_convert_locId INTO l_ship_to_location_id;
4009: IF c_convert_locId%NOTFOUND THEN
4010: FND_MESSAGE.SET_NAME('WSH','WSH_XC_INVALID_LOCATION');
4011: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
4012: CLOSE c_convert_locId;
4013: IF l_debug_on THEN
4014: WSH_DEBUG_SV.log(l_module_name, 'c_convert_locId%NOTFOUND',
4015: l_mtl_txn_if_rec.ship_to_location_id(l_index));
4045: WSH_DEBUG_SV.log(l_module_name,'l_account',l_account);
4046: END IF;
4047:
4048: IF l_account IS NULL THEN
4049: WSH_UTIL_CORE.PrintMsg('OKE_SHIPPING_EXT.Cost_Of_Sales_Account returns NULL value');
4050: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
4051: wsh_shipping_params_pvt.get(
4052: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
4053: x_param_info => l_ship_params,
4046: END IF;
4047:
4048: IF l_account IS NULL THEN
4049: WSH_UTIL_CORE.PrintMsg('OKE_SHIPPING_EXT.Cost_Of_Sales_Account returns NULL value');
4050: WSH_UTIL_CORE.PRINTMsg ('Use default distribution account defined for the organization');
4051: wsh_shipping_params_pvt.get(
4052: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
4053: x_param_info => l_ship_params,
4054: x_return_status => l_sp_ret_status);
4051: wsh_shipping_params_pvt.get(
4052: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
4053: x_param_info => l_ship_params,
4054: x_return_status => l_sp_ret_status);
4055: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4056: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
4057: l_mtl_txn_if_rec.organization_id(l_index));
4058: IF l_debug_on THEN
4059: WSH_DEBUG_SV.log(l_module_name,
4052: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
4053: x_param_info => l_ship_params,
4054: x_return_status => l_sp_ret_status);
4055: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4056: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
4057: l_mtl_txn_if_rec.organization_id(l_index));
4058: IF l_debug_on THEN
4059: WSH_DEBUG_SV.log(l_module_name,
4060: 'Unable to get shipping parameters for org', l_mtl_txn_if_rec.organization_id(l_index));
4067: WSH_DEBUG_SV.logmsg(l_module_name, ' ACCOUNT_ID:' || L_ACCOUNT );
4068: END IF;
4069: --
4070: IF l_account IS NULL THEN
4071: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
4072: l_mtl_txn_if_rec.organization_id(l_index));
4073: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
4074: --
4075: IF l_debug_on THEN
4069: --
4070: IF l_account IS NULL THEN
4071: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
4072: l_mtl_txn_if_rec.organization_id(l_index));
4073: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
4074: --
4075: IF l_debug_on THEN
4076: WSH_DEBUG_SV.log(l_module_name, 'There is no default goods dispatched account for org'
4077: ,l_mtl_txn_if_rec.organization_id(l_index));
4095: wsh_shipping_params_pvt.get(
4096: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
4097: x_param_info => l_ship_params,
4098: x_return_status => l_sp_ret_status);
4099: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4100: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
4101: l_mtl_txn_if_rec.organization_id(l_index));
4102: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4103: --
4096: p_organization_id => l_mtl_txn_if_rec.organization_id(l_index),
4097: x_param_info => l_ship_params,
4098: x_return_status => l_sp_ret_status);
4099: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4100: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
4101: l_mtl_txn_if_rec.organization_id(l_index));
4102: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4103: --
4104: IF l_debug_on THEN
4098: x_return_status => l_sp_ret_status);
4099: IF l_sp_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4100: wsh_util_core.printmsg('Unable to get shipping parameters for org ' ||
4101: l_mtl_txn_if_rec.organization_id(l_index));
4102: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4103: --
4104: IF l_debug_on THEN
4105: WSH_DEBUG_SV.log(l_module_name, 'Unable to get shipping parameters for org',
4106: l_mtl_txn_if_rec.organization_id(l_index));
4113: WSH_DEBUG_SV.logmsg(l_module_name, ' ACCOUNT_ID:' || L_ACCOUNT );
4114: END IF;
4115:
4116: IF ( l_account IS NULL ) THEN
4117: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
4118: l_mtl_txn_if_rec.organization_id(l_index));
4119: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
4120: --
4121: IF l_debug_on THEN
4115:
4116: IF ( l_account IS NULL ) THEN
4117: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account for org ' ||
4118: l_mtl_txn_if_rec.organization_id(l_index));
4119: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
4120: --
4121: IF l_debug_on THEN
4122: WSH_DEBUG_SV.log(l_module_name, 'There is no default goods dispatched account for org',
4123: l_mtl_txn_if_rec.organization_id(l_index));
4694:
4695: IF l_debug_on THEN
4696: WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
4697: END IF;
4698: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4699: l_insert_inv_not_success := l_insert_inv_not_success + 1;
4700: END IF;
4701:
4702: IF l_debug_on THEN
4735:
4736: IF l_debug_on THEN
4737: WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
4738: END IF;
4739: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4740: l_insert_inv_not_success := l_insert_inv_not_success + 1;
4741: END IF;
4742: IF l_debug_on THEN
4743: WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_calls',l_insert_inv_calls);
4772:
4773: IF l_debug_on THEN
4774: WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
4775: END IF;
4776: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4777: l_insert_inv_not_success := l_insert_inv_not_success + 1;
4778: END IF;
4779: IF l_debug_on THEN
4780: WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_calls',l_insert_inv_calls);
4813:
4814: IF (l_insert_inv_not_success >= l_insert_inv_calls) THEN
4815: raise fnd_api.g_exc_error;
4816: ELSIF (l_insert_inv_not_success > 0 and l_insert_inv_not_success < l_insert_inv_calls) THEN
4817: RAISE wsh_util_core.g_exc_warning;
4818: END IF;
4819:
4820:
4821:
4822: IF (l_error_count >= l_mtl_txn_if_rec.picking_line_id.count) THEN
4823: raise fnd_api.g_exc_error;
4824: ELSIF ((l_error_count > 0 and l_error_count
4826: RAISE wsh_util_core.g_exc_warning;
4827: END IF;
4828:
4829: IF l_debug_on THEN
4830: WSH_DEBUG_SV.log(l_module_name, 'x_return_status',x_return_status);
4834: WSH_DEBUG_SV.pop(l_module_name);
4835: END IF;
4836:
4837: EXCEPTION
4838: WHEN wsh_util_core.g_exc_warning THEN
4839: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4840:
4841: IF l_debug_on THEN
4842: wsh_debug_sv.logmsg(l_module_name, 'wsh_util_core.g_exc_warning exception has occured.', wsh_debug_sv.c_excep_level);
4835: END IF;
4836:
4837: EXCEPTION
4838: WHEN wsh_util_core.g_exc_warning THEN
4839: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4840:
4841: IF l_debug_on THEN
4842: wsh_debug_sv.logmsg(l_module_name, 'wsh_util_core.g_exc_warning exception has occured.', wsh_debug_sv.c_excep_level);
4843: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4838: WHEN wsh_util_core.g_exc_warning THEN
4839: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4840:
4841: IF l_debug_on THEN
4842: wsh_debug_sv.logmsg(l_module_name, 'wsh_util_core.g_exc_warning exception has occured.', wsh_debug_sv.c_excep_level);
4843: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4844: END IF;
4845:
4846: WHEN fnd_api.g_exc_error THEN
4843: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4844: END IF;
4845:
4846: WHEN fnd_api.g_exc_error THEN
4847: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4848:
4849: IF l_debug_on THEN
4850: wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
4851: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4852: END IF;
4853:
4854:
4855: WHEN others THEN
4856: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4857: l_error_code := SQLCODE;
4858: l_error_text := SQLERRM;
4859: WSH_UTIL_CORE.PrintMsg(l_mtl_txn_if_rec.picking_line_id(l_index) ||': Interface detail to inventory failed with unexpected error');
4860: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4855: WHEN others THEN
4856: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4857: l_error_code := SQLCODE;
4858: l_error_text := SQLERRM;
4859: WSH_UTIL_CORE.PrintMsg(l_mtl_txn_if_rec.picking_line_id(l_index) ||': Interface detail to inventory failed with unexpected error');
4860: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4861:
4862: IF c_freight_code%ISOPEN THEN
4863: CLOSE c_freight_code;
4856: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4857: l_error_code := SQLCODE;
4858: l_error_text := SQLERRM;
4859: WSH_UTIL_CORE.PrintMsg(l_mtl_txn_if_rec.picking_line_id(l_index) ||': Interface detail to inventory failed with unexpected error');
4860: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4861:
4862: IF c_freight_code%ISOPEN THEN
4863: CLOSE c_freight_code;
4864: END IF;
4929: WHERE batch_id = p_batch_id
4930: FOR UPDATE NOWAIT;
4931: Recinfo lock_row%ROWTYPE;
4932:
4933: l_stop_tab wsh_util_core.id_tab_type;
4934:
4935: -- bug 5736840
4936:
4937: CURSOR get_details (p_batch_id IN NUMBER) IS
5028:
5029: -- the batch should be fully interfaced to OM first.
5030:
5031: IF NOT OM_INTERFACED(p_batch_id) THEN
5032: wsh_util_core.printmsg('Batch ' || p_batch_id || ' is not yet fully interfaced to OM.');
5033: x_completion_status := 'WARNING';
5034:
5035: IF l_debug_on THEN
5036: WSH_DEBUG_SV.log(l_module_name,'x_completion_status',x_completion_status);
5070: transfer_serial_numbers ( p_batch_id => p_batch_id ,
5071: p_interfacing => 'INV',
5072: x_return_status => l_return_status );
5073:
5074: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5075: IF l_debug_on THEN
5076: WSH_DEBUG_SV.logmsg(l_module_name, 'Error encountered in call to TRANSFER_SERIAL_NUMBERS FOR BATCH' || TO_CHAR ( P_BATCH_ID ) );
5077: WSH_DEBUG_SV.log(l_module_name,'x_completion_status', x_completion_status);
5078: WSH_DEBUG_SV.pop(l_module_name);
5086: -- already successfully processed by the inventory transaction manager.
5087:
5088: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
5089:
5090: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
5091: WSH_UTIL_CORE.PrintMsg('inv_interface_trip_stop failed for batch '||p_batch_id ||':txn '||l_transaction_header_id);
5092: If (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5093: l_completion_status := 'ERROR';
5094: ELSE
5087:
5088: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
5089:
5090: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
5091: WSH_UTIL_CORE.PrintMsg('inv_interface_trip_stop failed for batch '||p_batch_id ||':txn '||l_transaction_header_id);
5092: If (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5093: l_completion_status := 'ERROR';
5094: ELSE
5095: l_completion_status := 'WARNING';
5088: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
5089:
5090: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
5091: WSH_UTIL_CORE.PrintMsg('inv_interface_trip_stop failed for batch '||p_batch_id ||':txn '||l_transaction_header_id);
5092: If (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5093: l_completion_status := 'ERROR';
5094: ELSE
5095: l_completion_status := 'WARNING';
5096: END IF;
5100: WSH_DEBUG_SV.log(l_module_name,'l_completion_status', l_completion_status);
5101: END IF;
5102: end if;
5103: -- RTV changes
5104: l_rcv_interf_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5105: l_rcv_completion_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5106:
5107: OPEN c_get_rtv_deliveries (p_batch_id);
5108: LOOP --{
5101: END IF;
5102: end if;
5103: -- RTV changes
5104: l_rcv_interf_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5105: l_rcv_completion_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5106:
5107: OPEN c_get_rtv_deliveries (p_batch_id);
5108: LOOP --{
5109: FETCH c_get_rtv_deliveries into l_rtv_delivery_id;
5113: WSH_DEBUG_SV.logmsg(l_module_name, 'RCV Delivery_id '|| l_rtv_delivery_id);
5114: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit RCV_WSH_INTERFACE_PKG.interface_to_rcv',WSH_DEBUG_SV.C_PROC_LEVEL);
5115: END IF;
5116:
5117: l_rcv_interf_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5118:
5119: RCV_WSH_INTERFACE_PKG.interface_to_rcv( p_delivery_id => l_rtv_delivery_id,
5120: p_return_status => l_rcv_interf_status);
5121:
5122: IF l_debug_on THEN
5123: WSH_DEBUG_SV.log(l_module_name,'Return status after calling RCV API ',l_rcv_interf_status);
5124: END IF;
5125:
5126: IF (l_rcv_interf_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
5127: l_rcv_interf_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
5128: l_rcv_completion_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5129: END IF;
5130:
5123: WSH_DEBUG_SV.log(l_module_name,'Return status after calling RCV API ',l_rcv_interf_status);
5124: END IF;
5125:
5126: IF (l_rcv_interf_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
5127: l_rcv_interf_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
5128: l_rcv_completion_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5129: END IF;
5130:
5131: END LOOP; --}
5124: END IF;
5125:
5126: IF (l_rcv_interf_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
5127: l_rcv_interf_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
5128: l_rcv_completion_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5129: END IF;
5130:
5131: END LOOP; --}
5132: CLOSE c_get_rtv_deliveries;
5276: THEN
5277: --{
5278: fnd_message.set_name('WSH', 'WSH_INV_INTF_ERROR_LINES');
5279: l_num_warnings := nvl(l_num_warnings,0) + 1;
5280: WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_warning,l_module_name);
5281: --}
5282: -- HW OPMCONV - No need to check for l_opm_org_exist
5283: ELSIF (nvl(l_picking_lines_count,0) = 0
5284: and not(l_non_opm_org_exist)
5285: and nvl(l_def_inv_online,'N') <> 'Y') THEN
5286: --{
5287: fnd_message.set_name('WSH', 'WSH_INV_INTF_LINES_LOCKED');
5288: l_num_warnings := nvl(l_num_warnings,0) + 1;
5289: WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_warning,l_module_name);
5290: --}
5291: END IF;
5292: -- bug 3588371
5293:
5290: --}
5291: END IF;
5292: -- bug 3588371
5293:
5294: -- Bug#4736038: If return value is WSH_UTIL_CORE.G_RET_STS_ERROR,
5295: -- i) It is treated as warning so that other batches can be processed.
5296: -- ii) No need to call process_inv_online as it represents all DD's in a batch are failed.
5297: IF (l_inv_inter_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5298: IF (l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
5293:
5294: -- Bug#4736038: If return value is WSH_UTIL_CORE.G_RET_STS_ERROR,
5295: -- i) It is treated as warning so that other batches can be processed.
5296: -- ii) No need to call process_inv_online as it represents all DD's in a batch are failed.
5297: IF (l_inv_inter_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5298: IF (l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
5299:
5300: IF lock_row%ISOPEN THEN
5301: CLOSE lock_row;
5294: -- Bug#4736038: If return value is WSH_UTIL_CORE.G_RET_STS_ERROR,
5295: -- i) It is treated as warning so that other batches can be processed.
5296: -- ii) No need to call process_inv_online as it represents all DD's in a batch are failed.
5297: IF (l_inv_inter_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5298: IF (l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
5299:
5300: IF lock_row%ISOPEN THEN
5301: CLOSE lock_row;
5302: END IF;
5306: WSH_DEBUG_SV.log(l_module_name,'Batch_id '||p_batch_id||' pass Inventory interface with unexpected errors');
5307: WSH_DEBUG_SV.pop(l_module_name);
5308: END IF;
5309: return;
5310: ELSIF ( l_inv_inter_status = WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN
5311:
5312: IF lock_row%ISOPEN THEN
5313: CLOSE lock_row;
5314: END IF;
5332:
5333: IF (nvl(l_def_inv_online,'N') <> 'Y' and l_completion_status <> 'ERROR') THEN
5334: process_inv_online ( p_batch_id , l_transaction_header_id , l_return_status );
5335:
5336: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
5337: IF l_debug_on THEN
5338: WSH_DEBUG_SV.logmsg(l_module_name,'Batch '|| p_batch_id ||': TXN ' ||
5339: L_TRANSACTION_HEADER_ID || ' : PROCESS ONLINE FAILED AGAIN' );
5340: END IF;
5337: IF l_debug_on THEN
5338: WSH_DEBUG_SV.logmsg(l_module_name,'Batch '|| p_batch_id ||': TXN ' ||
5339: L_TRANSACTION_HEADER_ID || ' : PROCESS ONLINE FAILED AGAIN' );
5340: END IF;
5341: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5342: l_completion_status := 'ERROR';
5343: ELSE
5344: l_completion_status := 'WARNING';
5345: END IF;
5351: close lock_row;
5352: END IF;
5353:
5354: -- RTV changes
5355: if ( l_rcv_completion_status = WSH_UTIL_CORE.G_RET_STS_ERROR) then
5356: x_completion_status := 'ERROR';
5357: else
5358: x_completion_status := l_completion_status;
5359: end if;
5381: END IF;
5382: IF get_details%ISOPEN THEN
5383: CLOSE get_details;
5384: END IF;
5385: WSH_UTIL_CORE.PrintMsg('This Trip Stop is locked by some other process');
5386: x_completion_status := 'WARNING'; -- continue processing other stops
5387: --
5388: IF l_debug_on THEN
5389: WSH_DEBUG_SV.logmsg(l_module_name,'TRIP_STOP_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5396: END IF;
5397: l_completion_status := 'ERROR';
5398: l_error_code := SQLCODE;
5399: l_error_text := SQLERRM;
5400: WSH_UTIL_CORE.PrintMsg('Interface trip_stop to inventory failed with unexpected error');
5401: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
5402: x_completion_status := l_completion_status;
5403: --
5404: IF l_debug_on THEN
5397: l_completion_status := 'ERROR';
5398: l_error_code := SQLCODE;
5399: l_error_text := SQLERRM;
5400: WSH_UTIL_CORE.PrintMsg('Interface trip_stop to inventory failed with unexpected error');
5401: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
5402: x_completion_status := l_completion_status;
5403: --
5404: IF l_debug_on THEN
5405: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5674: x_msg_count => l_msg_count,
5675: x_msg_data => l_msg_data
5676: );
5677:
5678: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5679: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5680: raise GET_TOLERANCE_FAILED;
5681: END if;
5682:
5675: x_msg_data => l_msg_data
5676: );
5677:
5678: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5679: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5680: raise GET_TOLERANCE_FAILED;
5681: END if;
5682:
5683: -- muom
5682:
5683: -- muom
5684: -- If Fulfillment Base is Secondary, then derive fulfilled_flag/ship_beyond_flag from get_min_max_tolerance_quantity api.
5685: -- If Fulfillment Base is Primary, then continue to do as before
5686: IF WSH_UTIL_CORE.Get_Line_Fulfillment_Base('OE', p_source_line_id) = 'S' THEN
5687:
5688: IF l_minmaxoutrec.min_remaining_quantity2 <= 0 THEN
5689: x_fulfilled_flag := 'T';
5690: ELSE
5726: x_ship_beyond_flag := 'F';
5727: END IF;
5728: END IF;
5729:
5730: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5731:
5732: IF l_debug_on THEN
5733: WSH_DEBUG_SV.log(l_module_name, 'SHIP_BEYOND_FLAG', X_SHIP_BEYOND_FLAG);
5734: WSH_DEBUG_SV.log(l_module_name, 'FULFILLED_FLAG', X_FULFILLED_FLAG);
5785: l_num_warnings NUMBER;
5786: l_num_errors NUMBER;
5787: l_prev_line_set_id NUMBER:= -99;
5788: l_cancel_unpicked_details VARCHAR(1);
5789: l_remain_details_id WSH_UTIL_CORE.Id_Tab_Type;
5790: l_line_id NUMBER;
5791: l_dummy VARCHAR2(1);
5792: l_summary VARCHAR2(2000) :=NULL;
5793: l_error_Code NUMBER ;
5853: IF l_debug_on IS NULL THEN
5854: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5855: END IF;
5856:
5857: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5858:
5859: IF l_debug_on THEN
5860: WSH_DEBUG_SV.push(l_module_name);
5861: END IF;
5866: l_process_flag := FND_API.G_TRUE;
5867: END IF;
5868:
5869: -- muom
5870: l_fulfill_base := WSH_UTIL_CORE.Get_Line_Fulfillment_Base('OE', p_oe_interface_rec.source_line_id);
5871:
5872: IF l_debug_on THEN
5873: WSH_DEBUG_SV.logmsg(l_module_name,'l_process_flag ' || l_process_flag);
5874: END IF;
5974: x_ship_beyond_flag => l_ship_beyond_flag,
5975: x_fulfilled_flag => l_fulfilled_flag,
5976: x_return_status => l_return_status);
5977: --haperf
5978: WSH_UTIL_CORE.api_post_call( p_return_status => l_return_status,
5979: x_num_warnings =>l_num_warnings,
5980: x_num_errors =>l_num_errors);
5981:
5982: IF l_debug_on THEN
6099: x_return_status => l_return_status);
6100: IF l_debug_on THEN
6101: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6102: END IF;
6103: WSH_UTIL_CORE.api_post_call( p_return_status => l_return_status,
6104: x_num_warnings =>l_num_warnings,
6105: x_num_errors =>l_num_errors);
6106: --}
6107: END IF;
6143: x_ship_beyond_flag => l_ship_beyond_flag,
6144: x_fulfilled_flag => l_fulfilled_flag,
6145: x_return_status => l_return_status);
6146: --haperf
6147: WSH_UTIL_CORE.api_post_call( p_return_status => l_return_status,
6148: x_num_warnings =>l_num_warnings,
6149: x_num_errors =>l_num_errors);
6150:
6151: IF (l_ship_beyond_flag = 'T') THEN
6174: END IF; --End if of tolerance check
6175:
6176: x_fulfilled_flag := l_fulfilled_flag ;
6177:
6178: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
6179: x_return_status := l_return_status;
6180: END IF;
6181:
6182: IF l_debug_on THEN
6186:
6187: EXCEPTION
6188:
6189: WHEN fnd_api.g_exc_error THEN
6190: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6191: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
6192:
6193: IF c_remain_detail_id%ISOPEN THEN
6194: close c_remain_detail_id;
6187: EXCEPTION
6188:
6189: WHEN fnd_api.g_exc_error THEN
6190: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6191: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
6192:
6193: IF c_remain_detail_id%ISOPEN THEN
6194: close c_remain_detail_id;
6195: END IF;
6208: END IF;
6209:
6210:
6211: WHEN others THEN
6212: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6213: l_error_code := SQLCODE;
6214: l_error_text := SQLERRM;
6215:
6216: IF c_remain_detail_id%ISOPEN THEN
6224: IF c_picked_dd%ISOPEN THEN
6225: close c_picked_dd;
6226: END IF;
6227:
6228: wsh_util_core.printMsg('API Handle_Tolerances failed with an unexpected error');
6229: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
6230: --
6231: IF l_debug_on THEN
6232: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN Handle_Tolerances ' );
6225: close c_picked_dd;
6226: END IF;
6227:
6228: wsh_util_core.printMsg('API Handle_Tolerances failed with an unexpected error');
6229: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
6230: --
6231: IF l_debug_on THEN
6232: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN Handle_Tolerances ' );
6233: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6422: END IF;
6423: --
6424: exception
6425: WHEN others THEN
6426: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6427: WSH_UTIL_CORE.PrintMsg('UPDATE_INTERFACED_DETAILS : unexpected error ');
6428: WSH_UTIL_CORE.PrintMsg( SQLCODE || ' : ' || SQLERRM );
6429:
6430: IF l_debug_on THEN
6423: --
6424: exception
6425: WHEN others THEN
6426: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6427: WSH_UTIL_CORE.PrintMsg('UPDATE_INTERFACED_DETAILS : unexpected error ');
6428: WSH_UTIL_CORE.PrintMsg( SQLCODE || ' : ' || SQLERRM );
6429:
6430: IF l_debug_on THEN
6431: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6424: exception
6425: WHEN others THEN
6426: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6427: WSH_UTIL_CORE.PrintMsg('UPDATE_INTERFACED_DETAILS : unexpected error ');
6428: WSH_UTIL_CORE.PrintMsg( SQLCODE || ' : ' || SQLERRM );
6429:
6430: IF l_debug_on THEN
6431: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6432: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6522: --
6523: --========================================================================
6524:
6525: PROCEDURE Filter_Stops_From_Batch (p_batch_id IN NUMBER,
6526: p_stop_tab IN wsh_util_core.id_tab_type,
6527: x_num_stops_removed OUT NOCOPY NUMBER,
6528: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
6529: x_return_status OUT NOCOPY VARCHAR2)
6530: IS
6524:
6525: PROCEDURE Filter_Stops_From_Batch (p_batch_id IN NUMBER,
6526: p_stop_tab IN wsh_util_core.id_tab_type,
6527: x_num_stops_removed OUT NOCOPY NUMBER,
6528: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
6529: x_return_status OUT NOCOPY VARCHAR2)
6530: IS
6531:
6532: l_debug_on BOOLEAN;
6539: j NUMBER;
6540: l_all_stops_count NUMBER;
6541:
6542: l_found BOOLEAN;
6543: l_err_stops wsh_util_core.id_tab_type;
6544: l_dummy NUMBER;
6545:
6546: CURSOR c_lock_batch (p_batch_id NUMBER) IS
6547: SELECT batch_id
6585: WSH_DEBUG_SV.push(l_module_name);
6586: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
6587: END IF;
6588: --
6589: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6590: x_num_stops_removed := 0;
6591:
6592: SAVEPOINT s_Filter_Stops_From_Batch;
6593:
6688: EXCEPTION
6689:
6690: WHEN OTHERS THEN
6691: ROLLBACK TO s_Filter_Stops_From_Batch;
6692: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
6693: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.Filter_Stops_From_Batch');
6694:
6695: IF l_debug_on THEN
6696: wsh_debug_sv.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
6689:
6690: WHEN OTHERS THEN
6691: ROLLBACK TO s_Filter_Stops_From_Batch;
6692: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
6693: wsh_util_core.default_handler('WSH_SHIP_CONFIRM_ACTIONS.Filter_Stops_From_Batch');
6694:
6695: IF l_debug_on THEN
6696: wsh_debug_sv.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
6697: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
6734: --
6735: --========================================================================
6736:
6737: PROCEDURE oe_interface_trip_stop(p_batch_id IN NUMBER,
6738: p_stop_tab IN wsh_util_core.id_tab_type,
6739: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
6740: x_num_stops_removed OUT NOCOPY NUMBER,
6741: x_completion_status OUT NOCOPY VARCHAR2) IS
6742:
6735: --========================================================================
6736:
6737: PROCEDURE oe_interface_trip_stop(p_batch_id IN NUMBER,
6738: p_stop_tab IN wsh_util_core.id_tab_type,
6739: x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
6740: x_num_stops_removed OUT NOCOPY NUMBER,
6741: x_completion_status OUT NOCOPY VARCHAR2) IS
6742:
6743: l_return_status varchar2(30);
6809: END IF;
6810:
6811:
6812: l_prev_return_status := l_return_status;
6813: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN --{
6814: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN --{
6815: raise e_Interface_Stop_to_om;
6816: END IF; --}
6817: ELSE --}{
6810:
6811:
6812: l_prev_return_status := l_return_status;
6813: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN --{
6814: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN --{
6815: raise e_Interface_Stop_to_om;
6816: END IF; --}
6817: ELSE --}{
6818: l_completion_status := 'INTERFACED' ;
6822: IF l_debug_on THEN
6823: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling TRANSFER_SERIAL_NUMBERS FOR ' || TO_CHAR ( p_batch_id ) );
6824: END IF;
6825:
6826: IF l_prev_return_status <> WSH_UTIL_CORE.G_RET_STS_ERROR THEN
6827: transfer_serial_numbers ( p_batch_id => p_batch_id ,
6828: p_interfacing => 'OM',
6829: x_return_status => l_return_status );
6830:
6827: transfer_serial_numbers ( p_batch_id => p_batch_id ,
6828: p_interfacing => 'OM',
6829: x_return_status => l_return_status );
6830:
6831: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
6832: raise e_Interface_Stop_To_OM;
6833: end if;
6834: END IF;
6835:
6832: raise e_Interface_Stop_To_OM;
6833: end if;
6834: END IF;
6835:
6836: IF l_prev_return_status IN ( WSH_UTIL_CORE.G_RET_STS_WARNING,
6837: WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN --{
6838:
6839: -- filter out the failed stops from the batch.
6840:
6833: end if;
6834: END IF;
6835:
6836: IF l_prev_return_status IN ( WSH_UTIL_CORE.G_RET_STS_WARNING,
6837: WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN --{
6838:
6839: -- filter out the failed stops from the batch.
6840:
6841: Filter_Stops_From_Batch (p_batch_id => p_batch_id,
6843: x_num_stops_removed => x_num_stops_removed,
6844: x_stop_tab => x_stop_tab,
6845: x_return_status => l_dummy);
6846:
6847: WSH_UTIL_CORE.api_post_call(p_return_status =>l_dummy,
6848: x_num_warnings =>l_num_warnings,
6849: x_num_errors =>l_num_errors);
6850:
6851: -- if x_stop_tab contains less rows than p_stop_tab then this shows
6874: when trip_stop_locked Then
6875: IF lock_row%ISOPEN THEN
6876: CLOSE lock_row;
6877: END IF;
6878: WSH_UTIL_CORE.PrintMsg('This Trip Stop is locked by some other process');
6879: x_completion_status := 'WARNING'; -- continue processing other stops
6880: --
6881: IF l_debug_on THEN
6882: WSH_DEBUG_SV.logmsg(l_module_name,'TRIP_STOP_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6884: END IF;
6885: --
6886:
6887: WHEN e_Interface_Stop_To_OM then
6888: WSH_UTIL_CORE.PrintMsg('Failed to interface Batch ' || p_batch_id
6889: || ' to Order Management because API interface_header_to_OM failed');
6890: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
6891: x_completion_status := 'ERROR';
6892: ELSE
6886:
6887: WHEN e_Interface_Stop_To_OM then
6888: WSH_UTIL_CORE.PrintMsg('Failed to interface Batch ' || p_batch_id
6889: || ' to Order Management because API interface_header_to_OM failed');
6890: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
6891: x_completion_status := 'ERROR';
6892: ELSE
6893: x_completion_status := 'WARNING';
6894: END IF;
6904: --
6905:
6906: WHEN others then
6907:
6908: -- todo: Use wsh_util_core.default_handler and then get_message and println
6909: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || SQLERRM || to_char(SQLCODE));
6910: x_completion_status := 'ERROR';
6911: IF lock_row%ISOPEN THEN -- bug 2598688: avoid invalid cursor
6912: CLOSE lock_row;
6905:
6906: WHEN others then
6907:
6908: -- todo: Use wsh_util_core.default_handler and then get_message and println
6909: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || SQLERRM || to_char(SQLCODE));
6910: x_completion_status := 'ERROR';
6911: IF lock_row%ISOPEN THEN -- bug 2598688: avoid invalid cursor
6912: CLOSE lock_row;
6913: END IF;
7032:
7033: l_return_status varchar2(1);
7034: l_temp NUMBER;
7035: l_bulk boolean ;
7036: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7037: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7038: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7039: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7040: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7033: l_return_status varchar2(1);
7034: l_temp NUMBER;
7035: l_bulk boolean ;
7036: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7037: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7038: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7039: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7040: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7041: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7034: l_temp NUMBER;
7035: l_bulk boolean ;
7036: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7037: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7038: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7039: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7040: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7041: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7042: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
7035: l_bulk boolean ;
7036: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7037: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7038: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7039: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7040: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7041: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7042: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
7043: e_raise_others EXCEPTION;
7036: l_model_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7037: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7038: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7039: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7040: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7041: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7042: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
7043: e_raise_others EXCEPTION;
7044:
7037: l_model_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7038: l_ship_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7039: l_ship_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7040: l_std_cache_tbl WSH_UTIL_CORE.boolean_tab_type;
7041: l_std_cache_ext_tbl WSH_UTIL_CORE.boolean_tab_type;
7042: l_setsmc_output_rec OE_Shipping_Integration_PUB.Setsmc_Output_Rec_Type;
7043: e_raise_others EXCEPTION;
7044:
7045: BEGIN
7075: WSH_DEBUG_SV.logmsg(l_module_name, ' Processing Model Line');
7076: END IF;
7077:
7078: --Check if bulk status is found for current top model line in cache
7079: WSH_UTIL_CORE.get_cached_value(
7080: p_cache_tbl => l_model_cache_tbl,
7081: p_cache_ext_tbl => l_model_cache_ext_tbl,
7082: p_value => l_bulk,
7083: p_key => p_setsmc_input_rec.top_model_line_id,
7084: p_action => 'GET',
7085: x_return_status => l_return_status) ;
7086:
7087: --IF (not found in cache) THEN
7088: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --{
7089: IF l_debug_on THEN
7090: WSH_DEBUG_SV.logmsg(l_module_name,
7091: ' Calling OE_Shipping_Integration_PUB.Get_SetSMC_Interface_Status');
7092: END IF;
7100: l_return_status);
7101: WSH_DEBUG_SV.log(l_module_name,'x_interface_status',
7102: l_setsmc_output_rec.x_interface_status);
7103: END IF;
7104: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
7105: raise FND_API.G_EXC_ERROR;
7106: END IF;
7107:
7108: IF l_setsmc_output_rec.x_interface_status = 'Y' THEN --{
7121: l_bulk:=false;
7122: END IF; --}
7123:
7124: --Cache top_model_id and bulk status.
7125: WSH_UTIL_CORE.get_cached_value(
7126: p_cache_tbl => l_model_cache_tbl,
7127: p_cache_ext_tbl => l_model_cache_ext_tbl,
7128: p_value => l_bulk,
7129: p_key => p_setsmc_input_rec.top_model_line_id,
7129: p_key => p_setsmc_input_rec.top_model_line_id,
7130: p_action => 'PUT',
7131: x_return_status => l_return_status) ;
7132:
7133: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7134: raise FND_API.G_EXC_ERROR;
7135: END IF;
7136:
7137: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN --}{
7133: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7134: raise FND_API.G_EXC_ERROR;
7135: END IF;
7136:
7137: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN --}{
7138: raise FND_API.G_EXC_ERROR;
7139: END IF; --}
7140: END IF;
7141: -- Processing Model Line
7148: WSH_DEBUG_SV.logmsg(l_module_name, ' Processing Ship Set Line');
7149: END IF;
7150:
7151: --Check if bulk status is found for current ship_set line in cache
7152: WSH_UTIL_CORE.get_cached_value(
7153: p_cache_tbl => l_ship_cache_tbl,
7154: p_cache_ext_tbl => l_ship_cache_ext_tbl,
7155: p_value => l_bulk,
7156: p_key => p_setsmc_input_rec.ship_set_id,
7157: p_action => 'GET',
7158: x_return_status => l_return_status) ;
7159:
7160: --IF (not found in cache) THEN
7161: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
7162: IF l_debug_on THEN
7163: WSH_DEBUG_SV.logmsg(l_module_name,
7164: ' Calling OE_Shipping_Integration_PUB.Get_SetSMC_Interface_Status');
7165: END IF;
7174: WSH_DEBUG_SV.log(l_module_name,'x_interface_status',
7175: l_setsmc_output_rec.x_interface_status);
7176: END IF;
7177:
7178: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
7179: raise FND_API.G_EXC_ERROR;
7180: END IF;
7181: IF l_setsmc_output_rec.x_interface_status = 'Y' THEN --{
7182: OPEN ship_set_line_csr(p_batch_id,p_setsmc_input_rec.ship_set_id, p_source_header_id);
7192: ELSE --}{
7193: l_bulk:=false;
7194: END IF; --}
7195: --Cache top_model_id and bulk status.
7196: WSH_UTIL_CORE.get_cached_value(
7197: p_cache_tbl => l_ship_cache_tbl,
7198: p_cache_ext_tbl => l_ship_cache_ext_tbl,
7199: p_value => l_bulk,
7200: p_key => p_setsmc_input_rec.ship_set_id,
7200: p_key => p_setsmc_input_rec.ship_set_id,
7201: p_action => 'PUT',
7202: x_return_status => l_return_status) ;
7203:
7204: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7205: raise FND_API.G_EXC_ERROR;
7206: END IF;
7207: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7208: raise FND_API.G_EXC_ERROR;
7203:
7204: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7205: raise FND_API.G_EXC_ERROR;
7206: END IF;
7207: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7208: raise FND_API.G_EXC_ERROR;
7209: END IF;
7210: END IF;
7211: -- Processing Ship Set Line
7218: WSH_DEBUG_SV.logmsg(l_module_name, ' Processing Standard Line');
7219: END IF;
7220:
7221: --Check if bulk status is found for current ato_line in cache
7222: WSH_UTIL_CORE.get_cached_value(
7223: p_cache_tbl => l_std_cache_tbl,
7224: p_cache_ext_tbl => l_std_cache_ext_tbl,
7225: p_value => l_bulk,
7226: p_key => p_source_line_id,
7227: p_action => 'GET',
7228: x_return_status => l_return_status) ;
7229:
7230: --IF (not found in cache) THEN
7231: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
7232: OPEN std_item_line_csr(p_batch_id,p_source_line_id);
7233: FETCH std_item_line_csr INTO l_temp;
7234:
7235: IF (std_item_line_csr%NOTFOUND) THEN
7239: END IF;
7240: CLOSE std_item_line_csr;
7241:
7242: --Cache top_model_id and bulk status.
7243: WSH_UTIL_CORE.get_cached_value(
7244: p_cache_tbl => l_std_cache_tbl,
7245: p_cache_ext_tbl => l_std_cache_ext_tbl,
7246: p_value => l_bulk,
7247: p_key => p_source_line_id,
7247: p_key => p_source_line_id,
7248: p_action => 'PUT',
7249: x_return_status => l_return_status) ;
7250:
7251: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7252: raise FND_API.G_EXC_ERROR;
7253: END IF;
7254: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7255: raise FND_API.G_EXC_ERROR;
7250:
7251: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7252: raise FND_API.G_EXC_ERROR;
7253: END IF;
7254: ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7255: raise FND_API.G_EXC_ERROR;
7256: END IF;
7257: END IF;
7258: --Standard item
7534: WSH_DEBUG_SV.log(l_module_name,'p_bulk_req_line.count',p_bulk_req_line.line_id.count);
7535: WSH_DEBUG_SV.log(l_module_name,'x_charges_are_calculated',x_charges_are_calculated);
7536: END IF;
7537:
7538: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7539:
7540:
7541:
7542: IF (p_bulk_mode='N') THEN
7616: WSH_DEBUG_SV.log(l_module_name,'l_ship_adj_line_all.line_id.count',l_ship_adj_line_all.line_id.count);
7617: END IF;
7618:
7619:
7620: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
7621: RAISE process_freight_costs_failed;
7622: END IF;
7623:
7624: l_freight_costs_all.DELETE;
7831: IF l_debug_on THEN
7832: WSH_DEBUG_SV.logmsg(l_module_name, SUBSTR ( X_MSG_DATA , 1 , 255 ) );
7833: END IF;
7834: --
7835: WSH_UTIL_CORE.printmsg('Error msg: '||substr(x_msg_data,1,2000));
7836: END LOOP;
7837:
7838: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
7839: AND p_bulk_mode='N' THEN
7834: --
7835: WSH_UTIL_CORE.printmsg('Error msg: '||substr(x_msg_data,1,2000));
7836: END LOOP;
7837:
7838: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
7839: AND p_bulk_mode='N' THEN
7840:
7841: -- for non-bulk mode handle warnings as error;
7842:
7839: AND p_bulk_mode='N' THEN
7840:
7841: -- for non-bulk mode handle warnings as error;
7842:
7843: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7844:
7845: END IF;
7846:
7847:
7844:
7845: END IF;
7846:
7847:
7848: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
7849: IF l_debug_on THEN
7850: WSH_DEBUG_SV.logmsg(l_module_name,'SUCCESS');
7851: END IF;
7852:
7946: END IF;
7947: --hadcp
7948:
7949: commit;
7950: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
7951: IF l_debug_on THEN
7952: WSH_DEBUG_SV.logmsg(l_module_name,'WARNING');
7953: END IF;
7954: l_warn_count := l_warn_count + 1;
8107: IF l_debug_on THEN
8108: WSH_DEBUG_SV.logmsg(l_module_name,'DCP Exception');
8109: END IF;
8110:
8111: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_WARNING, WSH_UTIL_CORE.G_RET_STS_SUCCESS)) THEN
8112: IF l_debug_on THEN
8113: WSH_DEBUG_SV.logmsg(l_module_name,'DCP Rollback');
8114: END IF;
8115:
8148: WSH_DEBUG_SV.log(l_module_name,'l_warn_count',l_warn_count);
8149: END IF;
8150:
8151: IF (l_error_count = l_loop_count ) THEN
8152: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8153: IF l_debug_on THEN
8154: WSH_DEBUG_SV.logmsg(l_module_name,'Process Order Error', WSH_DEBUG_SV.C_EXCEP_LEVEL);
8155: END IF;
8156:
8163: END IF;
8164: --hadcp
8165:
8166: ELSIF (l_warn_count > 0 OR (l_error_count > 0 and l_error_count < l_loop_count) ) THEN
8167: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8168: IF l_debug_on THEN
8169: WSH_DEBUG_SV.logmsg(l_module_name,'Process Order Warning'||WSH_DEBUG_SV.C_EXCEP_LEVEL);
8170: END IF;
8171: END IF;
8175: END IF;
8176:
8177: EXCEPTION
8178: WHEN process_freight_costs_failed THEN
8179: WSH_UTIL_CORE.PrintMsg('process_freight_costs_failed');
8180: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8181:
8182: IF l_debug_on THEN
8183: WSH_DEBUG_SV.logmsg(l_module_name,'PROCESS_FREIGHT_COSTS_FAILED exception has occured.',
8176:
8177: EXCEPTION
8178: WHEN process_freight_costs_failed THEN
8179: WSH_UTIL_CORE.PrintMsg('process_freight_costs_failed');
8180: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8181:
8182: IF l_debug_on THEN
8183: WSH_DEBUG_SV.logmsg(l_module_name,'PROCESS_FREIGHT_COSTS_FAILED exception has occured.',
8184: WSH_DEBUG_SV.C_EXCEP_LEVEL);
8197: --hadcp
8198:
8199:
8200: WHEN others THEN
8201: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8202: wsh_util_core.printMsg('API Process_Stop_To_OM failed with an unexpected error');
8203: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || sqlerrm);
8204:
8205: IF l_debug_on THEN
8198:
8199:
8200: WHEN others THEN
8201: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8202: wsh_util_core.printMsg('API Process_Stop_To_OM failed with an unexpected error');
8203: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || sqlerrm);
8204:
8205: IF l_debug_on THEN
8206: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN Process_Stop_To_OM' );
8199:
8200: WHEN others THEN
8201: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8202: wsh_util_core.printMsg('API Process_Stop_To_OM failed with an unexpected error');
8203: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || sqlerrm);
8204:
8205: IF l_debug_on THEN
8206: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN Process_Stop_To_OM' );
8207: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8253: WSH_DEBUG_SV.push(l_module_name);
8254: WSH_DEBUG_SV.log(l_module_name,'p_ship_line.fulfilled_flag.count', p_ship_line.fulfilled_flag.count);
8255: END IF;
8256:
8257: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8258:
8259:
8260: p_ship_line.fulfilled_flag.extend;
8261: p_ship_line.actual_shipment_date.extend;
8295: END IF;
8296:
8297: EXCEPTION
8298: WHEN others THEN
8299: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8300:
8301: IF l_debug_on THEN
8302: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8303: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8486: l_details VARCHAR2(4000) :=NULL;
8487: l_get_msg_count number;
8488: l_error_Code number;
8489: l_error_text varchar2(2000);
8490: l_remain_details_id WSH_UTIL_CORE.Id_Tab_Type;
8491: l_remain_detail_index NUMBER;
8492:
8493:
8494: l_delete_detail_id NUMBER;
8502: -- OM bug 2022029
8503: l_line_qtys OE_SHIP_CONFIRMATION_PUB.Req_Quantity_Tbl_Type;
8504:
8505: -- anxsharm for Load Tender
8506: l_trip_id_tab wsh_util_core.id_tab_type;
8507:
8508: -- sql repository performance bug 4891985 (>1M sharable memory)
8509: -- 1) changed wsh_delivery_assignments_v to wsh_delivery_assignments
8510: -- 2) restructured the query
8608: sum( nvl(dd.shipped_quantity2, 0 )) shipping_quantity2,
8609: -- muom
8610: -- Derive shipped_quantity (to pass to OM) only when Fufillment Base is Secondary
8611: sum( decode(dd.requested_quantity_uom2, null, null,
8612: decode(WSH_UTIL_CORE.Get_Line_Fulfillment_Base(dd.source_code, dd.source_line_id),
8613: 'S', WSH_WV_UTILS.CONVERT_UOM(from_uom => dd.requested_quantity_uom2,
8614: to_uom => ol.order_quantity_uom,
8615: quantity => nvl(dd.shipped_quantity2,0),
8616: item_id => dd.inventory_item_id)))) shipped_quantity
8688: sum( nvl(dd.shipped_quantity2, 0 )) total_shipped_quantity2,
8689: -- muom
8690: -- Derive shipped_quantity (to pass to OM) only when Fufillment Base is Secondary
8691: sum( decode(dd.requested_quantity_uom2, null, null,
8692: decode(WSH_UTIL_CORE.Get_Line_Fulfillment_Base(dd.source_code, dd.source_line_id),
8693: 'S',WSH_WV_UTILS.CONVERT_UOM(
8694: from_uom => ol.ordered_quantity_uom2,
8695: to_uom => ol.order_quantity_uom,
8696: quantity => nvl(dd.shipped_quantity2, 0 ),
8824: WSH_DEBUG_SV.push(l_module_name);
8825: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
8826: END IF;
8827:
8828: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8829:
8830:
8831:
8832: l_counter := 0;
9024: IF l_debug_on THEN
9025: WSH_DEBUG_SV.log(l_module_name, 'NON BULK Process_Stop_To_OM l_return_status',l_return_status);
9026: END IF;
9027:
9028: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
9029: IF l_debug_on THEN
9030: 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');
9031: END IF;
9032: --ROLLBACK TO its_process_order_non_bulk;
9088: l_non_bulk_req_line.top_model_line_id.delete;
9089: l_non_bulk_req_line.inventory_item_id.delete;
9090: l_non_bulk_req_line.ship_from_org_id.delete;
9091:
9092: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
9093: x_num_warnings =>l_num_om_warnings,
9094: x_num_errors =>l_num_om_errors,
9095: p_raise_error_flag =>false);
9096:
9175: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIP_CONFRIM_ACTIONS.Handle_Tolerances',WSH_DEBUG_SV.C_PROC_LEVEL);
9176: END IF;
9177:
9178: -- muom
9179: l_fulfill_base := WSH_UTIL_CORE.Get_Line_Fulfillment_Base('OE', oe_interface_rec.source_line_id);
9180:
9181: --Standllone created new API ,Moved common code inside Handle_Tolerances API
9182: -- to be used by existing code as well as Standalone code
9183: Handle_Tolerances ( p_batch_id => p_batch_id,
9189: IF l_debug_on THEN
9190: WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_SHIP_CONFRIM_ACTIONS.Handle_Tolerances ',l_return_status);
9191: END IF;
9192:
9193: WSH_UTIL_CORE.api_post_call( p_return_status => l_return_status,
9194: x_num_warnings =>l_num_warnings,
9195: x_num_errors =>l_num_errors);
9196:
9197: -- muom
9340: WSH_DEBUG_SV.log(l_module_name, 'Non Bulk Ship l_non_bulk_ship_count',l_non_bulk_ship_count);
9341: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
9342: END IF;
9343:
9344: WSH_UTIL_CORE.api_post_call
9345: ( p_return_status =>l_return_status
9346: , x_num_warnings =>l_num_warnings
9347: , x_num_errors =>l_num_errors);
9348:
9455: IF l_debug_on THEN
9456: WSH_DEBUG_SV.log(l_module_name, 'Process_Stop_To_OM l_return_status',l_return_status);
9457: END IF;
9458:
9459: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
9460: IF l_debug_on THEN
9461: 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');
9462: END IF;
9463: --ROLLBACK TO its_process_order_non_bulk;
9463: --ROLLBACK TO its_process_order_non_bulk;
9464:
9465: END IF;
9466:
9467: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
9468: raise fnd_api.g_exc_error;
9469: END IF;
9470:
9471:
9468: raise fnd_api.g_exc_error;
9469: END IF;
9470:
9471:
9472: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
9473: x_num_warnings =>l_num_om_warnings,
9474: x_num_errors =>l_num_om_errors,
9475: p_raise_error_flag =>false);
9476: END IF;
9493:
9494: IF l_debug_on THEN
9495: WSH_DEBUG_SV.log(l_module_name, 'Process_Stop_To_OM l_return_status',l_return_status);
9496: END IF;
9497: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
9498: raise fnd_api.g_exc_error;
9499: END IF;
9500:
9501: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
9497: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
9498: raise fnd_api.g_exc_error;
9499: END IF;
9500:
9501: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
9502: x_num_warnings =>l_num_om_warnings,
9503: x_num_errors =>l_num_om_errors,
9504: p_raise_error_flag =>false);
9505: END IF;
9516:
9517: 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
9518: raise fnd_api.g_exc_error;
9519: ELSIF ( (l_num_errors > 0 ) OR (l_num_om_errors > 0 and l_num_om_api_call > 0) ) THEN
9520: raise wsh_util_core.g_exc_warning;
9521: ELSIF ( (l_num_warnings > 0 ) OR (l_num_om_warnings > 0 and l_num_om_api_call > 0) ) THEN
9522: raise wsh_util_core.g_exc_warning;
9523: ELSE
9524: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9518: raise fnd_api.g_exc_error;
9519: ELSIF ( (l_num_errors > 0 ) OR (l_num_om_errors > 0 and l_num_om_api_call > 0) ) THEN
9520: raise wsh_util_core.g_exc_warning;
9521: ELSIF ( (l_num_warnings > 0 ) OR (l_num_om_warnings > 0 and l_num_om_api_call > 0) ) THEN
9522: raise wsh_util_core.g_exc_warning;
9523: ELSE
9524: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9525: END IF;
9526:
9520: raise wsh_util_core.g_exc_warning;
9521: ELSIF ( (l_num_warnings > 0 ) OR (l_num_om_warnings > 0 and l_num_om_api_call > 0) ) THEN
9522: raise wsh_util_core.g_exc_warning;
9523: ELSE
9524: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9525: END IF;
9526:
9527:
9528: IF l_debug_on THEN
9532:
9533: EXCEPTION
9534:
9535: WHEN fnd_api.g_exc_error THEN
9536: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9537: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
9538: IF c_oe_interface%ISOPEN THEN
9539: close c_oe_interface;
9540: END IF;
9533: EXCEPTION
9534:
9535: WHEN fnd_api.g_exc_error THEN
9536: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9537: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
9538: IF c_oe_interface%ISOPEN THEN
9539: close c_oe_interface;
9540: END IF;
9541: IF lock_delivery_line%ISOPEN THEN
9562: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
9563: END IF;
9564: --
9565:
9566: WHEN wsh_util_core.g_exc_warning THEN
9567: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9568: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
9569: IF c_oe_interface%ISOPEN THEN
9570: close c_oe_interface;
9563: END IF;
9564: --
9565:
9566: WHEN wsh_util_core.g_exc_warning THEN
9567: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9568: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
9569: IF c_oe_interface%ISOPEN THEN
9570: close c_oe_interface;
9571: END IF;
9564: --
9565:
9566: WHEN wsh_util_core.g_exc_warning THEN
9567: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9568: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
9569: IF c_oe_interface%ISOPEN THEN
9570: close c_oe_interface;
9571: END IF;
9572: IF lock_delivery_line%ISOPEN THEN
9588: close c_picked_dd;
9589: END IF;
9590:
9591: IF l_debug_on THEN
9592: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_util_core.g_exc_warning exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
9593: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
9594: END IF;
9595:
9596: WHEN fnd_api.g_exc_unexpected_error THEN
9589: END IF;
9590:
9591: IF l_debug_on THEN
9592: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_util_core.g_exc_warning exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
9593: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
9594: END IF;
9595:
9596: WHEN fnd_api.g_exc_unexpected_error THEN
9597: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9593: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
9594: END IF;
9595:
9596: WHEN fnd_api.g_exc_unexpected_error THEN
9597: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9598: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
9599: IF c_oe_interface%ISOPEN THEN
9600: close c_oe_interface;
9601: END IF;
9594: END IF;
9595:
9596: WHEN fnd_api.g_exc_unexpected_error THEN
9597: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9598: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
9599: IF c_oe_interface%ISOPEN THEN
9600: close c_oe_interface;
9601: END IF;
9602: IF lock_delivery_line%ISOPEN THEN
9619: END IF;
9620:
9621: IF l_debug_on THEN
9622: WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_unexpected exception has occured: '||SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
9623: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.g_exc_warning');
9624: END IF;
9625:
9626:
9627: WHEN line_locked THEN
9624: END IF;
9625:
9626:
9627: WHEN line_locked THEN
9628: wsh_util_core.printMsg('Error: Failure to Accrue the Lock for above line,Please try after sometime');
9629: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9630: IF c_oe_interface%ISOPEN THEN
9631: close c_oe_interface;
9632: END IF;
9625:
9626:
9627: WHEN line_locked THEN
9628: wsh_util_core.printMsg('Error: Failure to Accrue the Lock for above line,Please try after sometime');
9629: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9630: IF c_oe_interface%ISOPEN THEN
9631: close c_oe_interface;
9632: END IF;
9633: IF lock_delivery_line%ISOPEN THEN
9655: END IF;
9656: --
9657:
9658: WHEN others THEN
9659: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9660: l_error_code := SQLCODE;
9661: l_error_text := SQLERRM;
9662: -- bug 2657859 frontport bug 2685584: close open cursors
9663: IF c_oe_interface%ISOPEN THEN
9680: END IF;
9681: IF c_picked_dd%ISOPEN THEN
9682: close c_picked_dd;
9683: END IF;
9684: wsh_util_core.printMsg('API interface_line_to_OM failed with an unexpected error');
9685: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
9686: --
9687: IF l_debug_on THEN
9688: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN INTERFACE_HEADER_TO_OM ' );
9681: IF c_picked_dd%ISOPEN THEN
9682: close c_picked_dd;
9683: END IF;
9684: wsh_util_core.printMsg('API interface_line_to_OM failed with an unexpected error');
9685: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
9686: --
9687: IF l_debug_on THEN
9688: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN INTERFACE_HEADER_TO_OM ' );
9689: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM
9748: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
9749: WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_HEADER_ID',P_TRANSACTION_HEADER_ID);
9750: END IF;
9751: --
9752: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9753: -- mark all 'N' as 'P'
9754:
9755: /* record is already processed by inventory */
9756: update wsh_delivery_details
9818: /* what should we do if the online processer is hanging
9819: because manager is not aviable. User needs to be informed */
9820:
9821: if (l_outcome <> TRUE ) THEN
9822: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
9823: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
9824: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
9825: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
9826: IF l_debug_on THEN
9819: because manager is not aviable. User needs to be informed */
9820:
9821: if (l_outcome <> TRUE ) THEN
9822: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
9823: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
9824: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
9825: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
9826: IF l_debug_on THEN
9827: WSH_DEBUG_SV.log(l_module_name,'Error Code:',x_error_code);
9820:
9821: if (l_outcome <> TRUE ) THEN
9822: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
9823: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
9824: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
9825: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
9826: IF l_debug_on THEN
9827: WSH_DEBUG_SV.log(l_module_name,'Error Code:',x_error_code);
9828: WSH_DEBUG_SV.log(l_module_name,'Error Explanation::',
9821: if (l_outcome <> TRUE ) THEN
9822: WSH_UTIL_CORE.printMsg('MTL_ONLINE_TRANSACTION_PUB.process_online returns false');
9823: WSH_UTIL_CORE.printMsg('Error Code:' || x_error_code);
9824: WSH_UTIL_CORE.printMsg('Error Explanation:' || x_error_explanation);
9825: WSH_UTIL_CORE.printMsg('Retrieving messages from the stack');
9826: IF l_debug_on THEN
9827: WSH_DEBUG_SV.log(l_module_name,'Error Code:',x_error_code);
9828: WSH_DEBUG_SV.log(l_module_name,'Error Explanation::',
9829: x_error_explanation);
9833: FOR i in 1 .. l_process_online_msg_count
9834: LOOP
9835: l_process_online_message := fnd_msg_pub.get(i,'T');
9836: l_process_online_message := replace(l_process_online_message,fnd_global.local_chr(0), ' ');
9837: WSH_UTIL_CORE.PrintMsg(l_process_online_message);
9838: IF l_debug_on THEN
9839: WSH_DEBUG_SV.log(l_module_name,'Error txt:',
9840: SUBSTR(l_process_online_message,1,200));
9841: END IF;
9841: END IF;
9842: END LOOP;
9843: end if;
9844:
9845: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9846: --
9847: IF l_debug_on THEN
9848: WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
9849: WSH_DEBUG_SV.pop(l_module_name);
9855: -- update to 'Y' where where inv_interfaced_flag = 'P' and dd_id in mmt and trx_hdr matches and mtl.delivery_id matches
9856:
9857: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
9858:
9859: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
9860: x_return_status := l_return_status ;
9861: WSH_UTIL_CORE.PrintMsg('process_inv_online failed for batch '|| p_batch_id ||': txn '
9862: || p_transaction_header_id );
9863: --
9857: Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
9858:
9859: if (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
9860: x_return_status := l_return_status ;
9861: WSH_UTIL_CORE.PrintMsg('process_inv_online failed for batch '|| p_batch_id ||': txn '
9862: || p_transaction_header_id );
9863: --
9864: IF l_debug_on THEN
9865: WSH_DEBUG_SV.log(l_module_name,'process_inv_online failed for Batch '
9876: END IF;
9877: --
9878: EXCEPTION
9879: WHEN others THEN
9880: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9881: l_error_code := SQLCODE;
9882: l_error_text := SQLERRM;
9883: WSH_UTIL_CORE.PrintMsg(p_transaction_header_id ||': process_inv_online failed ');
9884: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
9879: WHEN others THEN
9880: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9881: l_error_code := SQLCODE;
9882: l_error_text := SQLERRM;
9883: WSH_UTIL_CORE.PrintMsg(p_transaction_header_id ||': process_inv_online failed ');
9884: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
9885: --
9886: IF l_debug_on THEN
9887: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
9880: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
9881: l_error_code := SQLCODE;
9882: l_error_text := SQLERRM;
9883: WSH_UTIL_CORE.PrintMsg(p_transaction_header_id ||': process_inv_online failed ');
9884: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
9885: --
9886: IF l_debug_on THEN
9887: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
9888: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
9957: --
9958: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
9959: END IF;
9960: --
9961: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9962: OPEN c_details(p_delivery_detail_id);
9963: FETCH c_details INTO l_detail_rec;
9964: CLOSE c_details;
9965:
10051: RETURN(l_account);
10052:
10053: EXCEPTION
10054: WHEN NO_DEF_GOODS_DISPATCHED_ACCT THEN
10055: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
10056: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
10057: -- bug 2657859 frontport bug 2685584: close open cursors
10058: IF c_details%ISOPEN THEN
10059: close c_details;
10052:
10053: EXCEPTION
10054: WHEN NO_DEF_GOODS_DISPATCHED_ACCT THEN
10055: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
10056: WSH_UTIL_CORE.PrintMsg('There is no default goods dispatched account');
10057: -- bug 2657859 frontport bug 2685584: close open cursors
10058: IF c_details%ISOPEN THEN
10059: close c_details;
10060: END IF;
10060: END IF;
10061: IF c_dispatch_account%ISOPEN THEN
10062: close c_dispatch_account;
10063: END IF;
10064: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
10065: --
10066: IF l_debug_on THEN
10067: WSH_DEBUG_SV.logmsg(l_module_name,'NO_DEF_GOODS_DISPATCHED_ACCT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
10068: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DEF_GOODS_DISPATCHED_ACCT');
10071: RETURN NULL;
10072:
10073: -- bug 2657859 frontport bug 2685584: catch other exceptions
10074: WHEN others THEN
10075: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10076: l_error_code := SQLCODE;
10077: l_error_text := SQLERRM;
10078: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
10079: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
10074: WHEN others THEN
10075: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10076: l_error_code := SQLCODE;
10077: l_error_text := SQLERRM;
10078: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
10079: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
10080: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
10081: IF c_details%ISOPEN THEN
10082: close c_details;
10075: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10076: l_error_code := SQLCODE;
10077: l_error_text := SQLERRM;
10078: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
10079: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
10080: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
10081: IF c_details%ISOPEN THEN
10082: close c_details;
10083: END IF;
10076: l_error_code := SQLCODE;
10077: l_error_text := SQLERRM;
10078: WSH_UTIL_CORE.PrintMsg(p_delivery_detail_id ||': get_account failed ');
10079: WSH_UTIL_CORE.PrintMsg('The unexpected error code is ' || l_error_code);
10080: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
10081: IF c_details%ISOPEN THEN
10082: close c_details;
10083: END IF;
10084: IF c_dispatch_account%ISOPEN THEN
10083: END IF;
10084: IF c_dispatch_account%ISOPEN THEN
10085: close c_dispatch_account;
10086: END IF;
10087: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10088: IF l_debug_on THEN
10089: WSH_DEBUG_SV.logmsg(l_module_name,'Unhandled exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
10090: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
10091: END IF;
10342: || 'INTERFACE_ALL_WRP';
10343: --
10344: l_req_data VARCHAR2(100);
10345: l_num_requests NUMBER;
10346: l_child_req_ids wsh_util_core.id_tab_type;
10347: l_request_id NUMBER;
10348: l_this_request NUMBER;
10349: j NUMBER;
10350: l_dummy BOOLEAN;
10367: e_invalid_number EXCEPTION;
10368:
10369: BEGIN
10370: --
10371: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
10372: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
10373: --
10374: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
10375: --
10368:
10369: BEGIN
10370: --
10371: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
10372: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
10373: --
10374: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
10375: --
10376: IF l_debug_on IS NULL
10531: WHEN OTHERS THEN
10532: l_completion_status := 'ERROR';
10533: l_error_code := SQLCODE;
10534: l_error_text := SQLERRM;
10535: WSH_UTIL_CORE.PrintMsg('Interface_ALL_wrp failed with unexpected error.');
10536: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
10537: l_dummy := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
10538: errbuf := 'Interface trip stop failed with unexpected error';
10539: retcode := '2';
10532: l_completion_status := 'ERROR';
10533: l_error_code := SQLCODE;
10534: l_error_text := SQLERRM;
10535: WSH_UTIL_CORE.PrintMsg('Interface_ALL_wrp failed with unexpected error.');
10536: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
10537: l_dummy := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
10538: errbuf := 'Interface trip stop failed with unexpected error';
10539: retcode := '2';
10540: --
10569: l_num_errors VARCHAR2(3000);
10570: l_ship_beyond_flag VARCHAR2(1);
10571: l_fulfilled_flag VARCHAR2(1);
10572: l_remain_detail_index NUMBER;
10573: l_remain_details_id WSH_UTIL_CORE.Id_Tab_Type ;
10574:
10575: --This cursor should fetch only one records per order line
10576: --Get total_requested_quantity and total_shipped_quantity for the order line
10577: --This cursor type should match with WSH_SHIP_CONFIRM_ACTIONS.oe_interface_rec
10651:
10652: line_rec wsh_ship_confirm_actions.oe_interface_rec;
10653: l_over_reason varchar2(1);
10654: l_delete_detail_id NUMBER;
10655: line_id_tab wsh_util_core.id_tab_type;
10656: i NUMBER :=0;
10657: line_locked EXCEPTION;
10658: PRAGMA EXCEPTION_INIT(line_locked, -54);
10659:
10663: IF l_debug_on IS NULL THEN
10664: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
10665: END IF;
10666:
10667: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
10668:
10669: IF l_debug_on THEN
10670: WSH_DEBUG_SV.push(l_module_name);
10671: WSH_DEBUG_SV.log(l_module_name,'delivery_id ',p_delivery_id);
10703: IF l_debug_on THEN
10704: WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_SHIP_CONFRIM_ACTIONS.Handle_Tolerances ',l_return_status);
10705: END IF;
10706:
10707: WSH_UTIL_CORE.api_post_call( p_return_status => l_return_status,
10708: x_num_warnings =>l_num_warnings,
10709: x_num_errors =>l_num_errors);
10710: --}
10711: END LOOP;
10725: IF l_debug_on THEN
10726: WSH_DEBUG_SV.logmsg(l_module_name, 'After program unit Process_lines_To_OM l_return_status ' || l_return_status );
10727: END IF;
10728:
10729: WSH_UTIL_CORE.api_post_call(p_return_status => l_return_status, x_num_warnings =>l_num_warnings, x_num_errors =>l_num_errors);
10730: --}
10731: ELSE
10732: --{
10733: IF l_debug_on THEN
10735: END IF;
10736: --}
10737: END IF;
10738:
10739: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
10740: x_return_status := l_return_status;
10741: END IF;
10742:
10743: IF l_debug_on THEN
10745: END IF;
10746:
10747: EXCEPTION
10748: WHEN line_locked THEN
10749: wsh_util_core.printMsg('Error: Failure to Accrue the Lock ,Please try after sometime');
10750: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
10751:
10752: IF c_get_oe_interface_line_detail%ISOPEN THEN
10753: close c_get_oe_interface_line_detail;
10746:
10747: EXCEPTION
10748: WHEN line_locked THEN
10749: wsh_util_core.printMsg('Error: Failure to Accrue the Lock ,Please try after sometime');
10750: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
10751:
10752: IF c_get_oe_interface_line_detail%ISOPEN THEN
10753: close c_get_oe_interface_line_detail;
10754: END IF;
10760: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:LINE_LOCKED');
10761: END IF;
10762:
10763: WHEN OTHERS THEN
10764: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10765: wsh_util_core.printMsg('API Process_Delivery_To_OM failed with an unexpected error');
10766: WSH_UTIL_CORE.PrintMsg('The unexpected error is '|| sqlerrm);
10767:
10768: IF c_get_oe_interface_line_detail%ISOPEN THEN
10761: END IF;
10762:
10763: WHEN OTHERS THEN
10764: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10765: wsh_util_core.printMsg('API Process_Delivery_To_OM failed with an unexpected error');
10766: WSH_UTIL_CORE.PrintMsg('The unexpected error is '|| sqlerrm);
10767:
10768: IF c_get_oe_interface_line_detail%ISOPEN THEN
10769: close c_get_oe_interface_line_detail;
10762:
10763: WHEN OTHERS THEN
10764: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
10765: wsh_util_core.printMsg('API Process_Delivery_To_OM failed with an unexpected error');
10766: WSH_UTIL_CORE.PrintMsg('The unexpected error is '|| sqlerrm);
10767:
10768: IF c_get_oe_interface_line_detail%ISOPEN THEN
10769: close c_get_oe_interface_line_detail;
10770: END IF;
10796: -- popualted
10797: -- Based on Bulk and Non bulk varaiables OM API will be called in Bulk and
10798: -- Non Bulk mode.
10799: ----========================================================================
10800: PROCEDURE Process_Lines_To_OM (p_line_id_tab IN wsh_util_core.id_tab_type ,
10801: x_return_status OUT NOCOPY VARCHAR2)IS
10802: l_debug_on BOOLEAN;
10803: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'|| G_PKG_NAME || '.'|| 'Process_lines_To_OM';
10804: l_return_status VARCHAR2(1);
10888: END IF;
10889:
10890: l_bulk_ship_line.line_id.DELETE;
10891: l_non_bulk_ship_line.line_id.DELETE;
10892: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
10893:
10894: --If tolerances are met remaining quantity should have been cancelled till this point
10895:
10896: /*Process Order lines one by one*/
11143: IF l_debug_on THEN
11144: WSH_DEBUG_SV.logmsg(l_module_name, SUBSTR ( X_MSG_DATA , 1 , 255 ) );
11145: END IF;
11146: --
11147: WSH_UTIL_CORE.printmsg('Error msg: '||SUBSTR(x_msg_data,1,2000));
11148: --}
11149: END LOOP;
11150:
11151: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
11147: WSH_UTIL_CORE.printmsg('Error msg: '||SUBSTR(x_msg_data,1,2000));
11148: --}
11149: END LOOP;
11150:
11151: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
11152: --{
11153: IF l_debug_on THEN
11154: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR');
11155: END IF;
11214: IF l_debug_on THEN
11215: WSH_DEBUG_SV.logmsg(l_module_name, SUBSTR ( X_MSG_DATA , 1 , 255 ) );
11216: END IF;
11217: --
11218: WSH_UTIL_CORE.printmsg('Error msg: '||SUBSTR(x_msg_data,1,2000));
11219: END LOOP;
11220:
11221: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
11222: --{
11217: --
11218: WSH_UTIL_CORE.printmsg('Error msg: '||SUBSTR(x_msg_data,1,2000));
11219: END LOOP;
11220:
11221: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
11222: --{
11223: IF l_debug_on THEN
11224: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR');
11225: END IF;
11238: WSH_DEBUG_SV.logmsg(l_module_name,'*******No eligible lines found*********');
11239: END IF;
11240: END IF;
11241:
11242: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
11243: x_return_status := l_return_status;
11244: END IF;
11245:
11246: IF l_debug_on THEN
11248: END IF;
11249:
11250: EXCEPTION
11251: WHEN OTHERS THEN
11252: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
11253: wsh_util_core.printMsg('API Process_lines_To_OM failed with an unexpected error');
11254: WSH_UTIL_CORE.PrintMsg('The unexpected error is '|| sqlerrm);
11255:
11256: IF c_get_line_detail_to_interface%ISOPEN THEN
11249:
11250: EXCEPTION
11251: WHEN OTHERS THEN
11252: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
11253: wsh_util_core.printMsg('API Process_lines_To_OM failed with an unexpected error');
11254: WSH_UTIL_CORE.PrintMsg('The unexpected error is '|| sqlerrm);
11255:
11256: IF c_get_line_detail_to_interface%ISOPEN THEN
11257: close c_get_line_detail_to_interface;
11250: EXCEPTION
11251: WHEN OTHERS THEN
11252: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
11253: wsh_util_core.printMsg('API Process_lines_To_OM failed with an unexpected error');
11254: WSH_UTIL_CORE.PrintMsg('The unexpected error is '|| sqlerrm);
11255:
11256: IF c_get_line_detail_to_interface%ISOPEN THEN
11257: close c_get_line_detail_to_interface;
11258: END IF;