[Home] [Help]
21: --
22: -- R12 Routing Guide (Start)
23: --
24:
25: TYPE wsh_cs_delivery_info_rec IS RECORD( l_delivery_id wsh_new_deliveries.delivery_id%TYPE,
26: l_dlvy_name wsh_new_deliveries.name%TYPE,
27: l_organization_id wsh_new_deliveries.organization_id%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
22: -- R12 Routing Guide (Start)
23: --
24:
25: TYPE wsh_cs_delivery_info_rec IS RECORD( l_delivery_id wsh_new_deliveries.delivery_id%TYPE,
26: l_dlvy_name wsh_new_deliveries.name%TYPE,
27: l_organization_id wsh_new_deliveries.organization_id%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
23: --
24:
25: TYPE wsh_cs_delivery_info_rec IS RECORD( l_delivery_id wsh_new_deliveries.delivery_id%TYPE,
26: l_dlvy_name wsh_new_deliveries.name%TYPE,
27: l_organization_id wsh_new_deliveries.organization_id%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
24:
25: TYPE wsh_cs_delivery_info_rec IS RECORD( l_delivery_id wsh_new_deliveries.delivery_id%TYPE,
26: l_dlvy_name wsh_new_deliveries.name%TYPE,
27: l_organization_id wsh_new_deliveries.organization_id%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
25: TYPE wsh_cs_delivery_info_rec IS RECORD( l_delivery_id wsh_new_deliveries.delivery_id%TYPE,
26: l_dlvy_name wsh_new_deliveries.name%TYPE,
27: l_organization_id wsh_new_deliveries.organization_id%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
26: l_dlvy_name wsh_new_deliveries.name%TYPE,
27: l_organization_id wsh_new_deliveries.organization_id%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
27: l_organization_id wsh_new_deliveries.organization_id%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
28: l_gross_weight wsh_new_deliveries.gross_weight%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
29: l_weight_uom_code wsh_new_deliveries.weight_uom_code%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
30: l_volume wsh_new_deliveries.volume%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
31: l_volume_uom_code wsh_new_deliveries.volume_uom_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
39: l_fob_code wsh_new_deliveries.fob_code%TYPE,
32: l_initial_pickup_location_id wsh_new_deliveries.initial_pickup_location_id%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
39: l_fob_code wsh_new_deliveries.fob_code%TYPE,
40: l_shipment_direction wsh_new_deliveries.shipment_direction%TYPE,
33: l_ultimate_dropoff_location_id wsh_new_deliveries.ultimate_dropoff_location_id%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
39: l_fob_code wsh_new_deliveries.fob_code%TYPE,
40: l_shipment_direction wsh_new_deliveries.shipment_direction%TYPE,
41: l_delivery_type wsh_new_deliveries.delivery_type%TYPE,
34: l_customer_id wsh_new_deliveries.customer_id%TYPE,
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
39: l_fob_code wsh_new_deliveries.fob_code%TYPE,
40: l_shipment_direction wsh_new_deliveries.shipment_direction%TYPE,
41: l_delivery_type wsh_new_deliveries.delivery_type%TYPE,
42: l_trip_id NUMBER);
35: l_freight_terms_code wsh_new_deliveries.freight_terms_code%TYPE,
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
39: l_fob_code wsh_new_deliveries.fob_code%TYPE,
40: l_shipment_direction wsh_new_deliveries.shipment_direction%TYPE,
41: l_delivery_type wsh_new_deliveries.delivery_type%TYPE,
42: l_trip_id NUMBER);
43:
36: l_planned_flag wsh_new_deliveries.planned_flag%TYPE,
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
39: l_fob_code wsh_new_deliveries.fob_code%TYPE,
40: l_shipment_direction wsh_new_deliveries.shipment_direction%TYPE,
41: l_delivery_type wsh_new_deliveries.delivery_type%TYPE,
42: l_trip_id NUMBER);
43:
44: TYPE wsh_cs_delivery_info_tab IS TABLE OF wsh_cs_delivery_info_rec INDEX BY BINARY_INTEGER;
37: l_initial_pickup_date wsh_new_deliveries.initial_pickup_date%TYPE,
38: l_ultimate_dropoff_date wsh_new_deliveries.ultimate_dropoff_date%TYPE,
39: l_fob_code wsh_new_deliveries.fob_code%TYPE,
40: l_shipment_direction wsh_new_deliveries.shipment_direction%TYPE,
41: l_delivery_type wsh_new_deliveries.delivery_type%TYPE,
42: l_trip_id NUMBER);
43:
44: TYPE wsh_cs_delivery_info_tab IS TABLE OF wsh_cs_delivery_info_rec INDEX BY BINARY_INTEGER;
45: --
445: IF (nvl(l_del_valid_flag,'Y') = 'N') THEN
446: --
447: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_SC_CNT_ERROR');
448: IF l_debug_on THEN
449: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
450: END IF;
451:
452: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
453: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
448: IF l_debug_on THEN
449: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
450: END IF;
451:
452: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
453: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
454: --
455: END IF;
456: --
529: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
530: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_FIRM_ERROR');
531:
532: IF l_debug_on THEN
533: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
534: END IF;
535:
536: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
537: wsh_util_core.add_message(x_return_status);
532: IF l_debug_on THEN
533: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
534: END IF;
535:
536: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
537: wsh_util_core.add_message(x_return_status);
538: l_num_error := l_num_error + 1;
539: goto loop_end;
540: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
641: p_called_for_sc IN BOOLEAN default false) IS
642:
643: CURSOR c_isdelfirm(p_delid IN NUMBER) IS
644: select 'Y'
645: from wsh_new_deliveries
646: where delivery_id=p_delid AND
647: planned_flag='F';
648:
649: Cursor c_get_unplanned_delivery(p_delivery_id NUMBER) IS
647: planned_flag='F';
648:
649: Cursor c_get_unplanned_delivery(p_delivery_id NUMBER) IS
650: SELECT wnd.organization_id, wnd.planned_flag , wnd.initial_pickup_location_id
651: FROM wsh_new_deliveries wnd
652: WHERE wnd.delivery_id = p_delivery_id AND
653: wnd.status_code = 'OP' AND
654: wnd.planned_flag = 'N' AND
655: NVL(wnd.shipment_direction,'O') in ('O','IO');
655: NVL(wnd.shipment_direction,'O') in ('O','IO');
656:
657: Cursor c_get_delivery(p_delivery_id NUMBER) IS
658: SELECT wnd.organization_id, wnd.planned_flag
659: FROM wsh_new_deliveries wnd
660: WHERE wnd.delivery_id = p_delivery_id AND
661: wnd.status_code = 'OP' AND
662: NVL(wnd.shipment_direction,'O') in ('O','IO');
663:
687: l_num_error BINARY_INTEGER := 0;
688: l_num_warn BINARY_INTEGER := 0;
689: l_delfirm VARCHAR2(1);
690: l_organization_id WSH_SHIPPING_PARAMETERS.ORGANIZATION_ID%TYPE;
691: l_planned_flag WSH_NEW_DELIVERIES.PLANNED_FLAG%TYPE;
692: l_initial_pickup_location_id WSH_NEW_DELIVERIES.INITIAL_PICKUP_LOCATION_ID%TYPE;
693: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
694: l_num_of_staged_lines NUMBER := 0;
695: l_ignore_count NUMBER := 0;
688: l_num_warn BINARY_INTEGER := 0;
689: l_delfirm VARCHAR2(1);
690: l_organization_id WSH_SHIPPING_PARAMETERS.ORGANIZATION_ID%TYPE;
691: l_planned_flag WSH_NEW_DELIVERIES.PLANNED_FLAG%TYPE;
692: l_initial_pickup_location_id WSH_NEW_DELIVERIES.INITIAL_PICKUP_LOCATION_ID%TYPE;
693: l_released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE;
694: l_num_of_staged_lines NUMBER := 0;
695: l_ignore_count NUMBER := 0;
696: l_delivery_detail_count NUMBER := 0;
694: l_num_of_staged_lines NUMBER := 0;
695: l_ignore_count NUMBER := 0;
696: l_delivery_detail_count NUMBER := 0;
697: l_delivery_id NUMBER := 0;
698: l_auto_rate_tbl WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
699: l_select_carrier_tbl WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
700: l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
701: l_return_status VARCHAR2(1);
702: l_status_code VARCHAR2(30) := 'OP';
695: l_ignore_count NUMBER := 0;
696: l_delivery_detail_count NUMBER := 0;
697: l_delivery_id NUMBER := 0;
698: l_auto_rate_tbl WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
699: l_select_carrier_tbl WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
700: l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
701: l_return_status VARCHAR2(1);
702: l_status_code VARCHAR2(30) := 'OP';
703: l_dels_to_update WSH_UTIL_CORE.Id_Tab_Type;
705: l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
706: l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
707: l_msg_count NUMBER;
708: l_msg_data VARCHAR2(2000);
709: l_planned_flag_true WSH_NEW_DELIVERIES.PLANNED_FLAG%TYPE; --bugfix 3778944
710:
711: l_debug_on BOOLEAN;
712: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'Adjust_Planned_Flag';
713: l_exception_message VARCHAR2(2000);
788: --
789: -- Debug Statements
790: --
791: IF l_debug_on THEN
792: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
793: END IF;
794: --
795: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
796: wsh_util_core.add_message(x_return_status);
791: IF l_debug_on THEN
792: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
793: END IF;
794: --
795: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
796: wsh_util_core.add_message(x_return_status);
797: l_num_error := l_num_error + 1;
798: goto loop_end;
799: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
820: -- lock the delivery befor update
821: l_delivery_id := p_delivery_ids(i);
822:
823: SELECT organization_id, planned_flag INTO l_organization_id, l_planned_flag
824: FROM wsh_new_deliveries
825: WHERE delivery_id = p_delivery_ids(i)
826: FOR UPDATE NOWAIT;
827:
828: IF (SQL%NOTFOUND) THEN
985: record_locked_test exception;
986: PRAGMA EXCEPTION_INIT(record_locked_test, -54);
987: BEGIN
988: SELECT planned_flag INTO l_planned_flag
989: FROM wsh_new_deliveries
990: WHERE delivery_id = p_delivery_ids(i) AND status_code = 'OP'
991: AND planned_flag = 'N'
992: FOR UPDATE NOWAIT;
993:
1040: --
1041: -- Debug Statements
1042: --
1043: IF l_debug_on THEN
1044: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1045: END IF;
1046: --
1047: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
1048: wsh_util_core.add_message(x_return_status);
1043: IF l_debug_on THEN
1044: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1045: END IF;
1046: --
1047: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
1048: wsh_util_core.add_message(x_return_status);
1049: l_num_error := l_num_error + 1;
1050: goto end_of_loop;
1051: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1108: record_locked_test exception;
1109: PRAGMA EXCEPTION_INIT(record_locked_test, -54);
1110: BEGIN
1111: SELECT planned_flag INTO l_planned_flag
1112: FROM wsh_new_deliveries
1113: WHERE delivery_id = p_delivery_ids(i) AND status_code = 'OP'
1114: AND planned_flag = 'N'
1115: FOR UPDATE NOWAIT;
1116:
1165: --
1166: -- Debug Statements
1167: --
1168: IF l_debug_on THEN
1169: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1170: END IF;
1171: --
1172: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
1173: wsh_util_core.add_message(x_return_status);
1168: IF l_debug_on THEN
1169: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1170: END IF;
1171: --
1172: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
1173: wsh_util_core.add_message(x_return_status);
1174: l_num_error := l_num_error + 1;
1175: goto end_of_loop;
1176: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1223: l_delivery_id := p_delivery_ids(i);
1224: l_planned_flag := NULL;
1225:
1226: SELECT planned_flag INTO l_planned_flag
1227: FROM wsh_new_deliveries
1228: WHERE delivery_id = p_delivery_ids(i) AND status_code = 'OP'
1229: FOR UPDATE NOWAIT;
1230:
1231: IF l_planned_flag is not NULL THEN
1237: --
1238: -- Debug Statements
1239: --
1240: IF l_debug_on THEN
1241: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1242: END IF;
1243: --
1244: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
1245: wsh_util_core.add_message(x_return_status);
1240: IF l_debug_on THEN
1241: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1242: END IF;
1243: --
1244: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_ids(i)));
1245: wsh_util_core.add_message(x_return_status);
1246: l_num_error := l_num_error + 1;
1247: goto end_of_loop;
1248: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1356: -- plan the deliveries
1357: l_planned_flag_true := 'Y'; --Bugfix 3778944
1358: IF l_dels_to_update.count > 0 THEN
1359: FORALL i in l_dels_to_update.FIRST .. l_dels_to_update.LAST
1360: update wsh_new_deliveries
1361: set planned_flag = l_planned_flag_true --Bugfix 3778944
1362: where delivery_id = l_dels_to_update(i);
1363: END IF;
1364:
1578: l_num_error NUMBER := 0;
1579: l_num_warn NUMBER := 0;
1580: others EXCEPTION;
1581: l_return_status VARCHAR2(1);
1582: l_planned_flag_false WSH_NEW_DELIVERIES.PLANNED_FLAG%TYPE; --bugfix 3778944
1583:
1584: CURSOR c_isdelfirm(p_delid IN NUMBER) IS
1585: select 'Y'
1586: from wsh_new_deliveries
1582: l_planned_flag_false WSH_NEW_DELIVERIES.PLANNED_FLAG%TYPE; --bugfix 3778944
1583:
1584: CURSOR c_isdelfirm(p_delid IN NUMBER) IS
1585: select 'Y'
1586: from wsh_new_deliveries
1587: where delivery_id=p_delid AND
1588: planned_flag='F';
1589:
1590: l_delfirm VARCHAR2(1);
1633: --
1634: -- Debug Statements
1635: --
1636: IF l_debug_on THEN
1637: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1638: END IF;
1639: --
1640: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
1641: wsh_util_core.add_message(x_return_status);
1636: IF l_debug_on THEN
1637: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1638: END IF;
1639: --
1640: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
1641: wsh_util_core.add_message(x_return_status);
1642: l_num_error := l_num_error + 1;
1643: ELSE
1644:
1665: IF l_delfirm='N' THEN
1666: -- Set planned flag to 'N'
1667: l_planned_flag_false := 'N'; --bugfix 3778944
1668:
1669: UPDATE wsh_new_deliveries
1670: SET planned_flag = l_planned_flag_false --bugfix 3778944
1671: WHERE delivery_id = p_del_rows(i);
1672:
1673: IF (SQL%NOTFOUND) THEN
1741: dl.initial_pickup_location_id,
1742: t.ship_method_code,
1743: dl.organization_id,
1744: t.name
1745: FROM wsh_new_deliveries dl,
1746: wsh_delivery_legs dg,
1747: wsh_trip_stops st,
1748: wsh_trips t
1749: WHERE dl.delivery_id = p_delivery_id AND
1960:
1961: CURSOR get_delivery_info IS
1962: SELECT dl.initial_pickup_location_id,
1963: dl.organization_id
1964: FROM wsh_new_deliveries dl
1965: WHERE dl.delivery_id = p_delivery_id;
1966:
1967: CURSOR pack_slip_required (l_org_id NUMBER) IS
1968: SELECT pack_slip_required_flag
2084: p_validation_level => NULL,
2085: x_return_status => x_return_status,
2086: x_msg_count => x_msg_count,
2087: x_msg_data => x_msg_data,
2088: p_entity_name => 'WSH_NEW_DELIVERIES',
2089: p_entity_id => p_delivery_id,
2090: p_application_id => 665,
2091: p_location_id => l_pickup_location_id,
2092: p_document_type => 'PACK_TYPE',
2241:
2242: -- frontport bug 4310141 / Bug 4103142 - to get first trip for delivery
2243: CURSOR c_first_ship_method (p_delivery_id IN number)IS
2244: SELECT wt.ship_method_code
2245: FROM wsh_new_deliveries del,
2246: wsh_delivery_legs dlg,
2247: wsh_trip_stops st,
2248: wsh_trips wt
2249: WHERE del.delivery_id = dlg.delivery_id
2644: --OTM R12
2645: ignore_for_planning,
2646: tms_interface_flag
2647: --
2648: FROM wsh_new_deliveries
2649: WHERE delivery_id = v_delivery_id;
2650:
2651: -- J: W/V Changes
2652: CURSOR get_delivery_wv(v_delivery_id NUMBER) IS
2651: -- J: W/V Changes
2652: CURSOR get_delivery_wv(v_delivery_id NUMBER) IS
2653: SELECT gross_weight,
2654: volume
2655: FROM wsh_new_deliveries
2656: WHERE delivery_id = v_delivery_id;
2657:
2658:
2659: CURSOR Get_Del_First_Trip(v_del_id NUMBER) IS
2657:
2658:
2659: CURSOR Get_Del_First_Trip(v_del_id NUMBER) IS
2660: SELECT wts.trip_id
2661: FROM wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_new_deliveries wnd
2662: WHERE wdl.pick_up_stop_id=wts.stop_id
2663: AND wnd.initial_pickup_location_id = wts.stop_location_id
2664: AND wnd.delivery_id = wdl.delivery_id
2665: AND wnd.delivery_id=v_del_id;
2703: from wsh_delivery_legs
2704: where delivery_id = t_delivery_id );
2705:
2706:
2707: l_batch_id WSH_NEW_DELIVERIES.batch_id%TYPE;
2708:
2709: l_source_code VARCHAR2(30); /*Bug 2096052 */
2710: l_source_code_flag VARCHAR2(1) := 'N'; /*Bug 2096052 */
2711: l_released_status VARCHAR2(1);
2945: b_tripalreadychecked VARCHAR2(1);
2946: l_count_hashtrip NUMBER;
2947:
2948: --dummy tables for calling validate_constraint_wrapper
2949: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2950: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
2951: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2952: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2953: l_cc_in_ids wsh_util_core.id_tab_type;
3082: l_consol_del_doc_set wsh_util_core.id_tab_type;
3083: l_content_del_flag BOOLEAN := TRUE;
3084:
3085: --OTM R12
3086: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3087: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3088: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3089: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3090: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3083: l_content_del_flag BOOLEAN := TRUE;
3084:
3085: --OTM R12
3086: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3087: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3088: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3089: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3090: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3091: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3085: --OTM R12
3086: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3087: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3088: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3089: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3090: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3091: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3092: l_otm_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
3093: l_gc3_is_installed VARCHAR2(1);
3086: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3087: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3088: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3089: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3090: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3091: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3092: l_otm_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
3093: l_gc3_is_installed VARCHAR2(1);
3094: --
3087: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3088: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3089: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3090: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3091: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3092: l_otm_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
3093: l_gc3_is_installed VARCHAR2(1);
3094: --
3095:
3492: l_ship_to_location ,
3493: l_gross_weight ,
3494: l_net_weight ,
3495: l_volume
3496: FROM wsh_new_deliveries
3497: WHERE delivery_id = p_del_rows(i)
3498: AND nvl(shipment_direction,'O') IN ('O','IO') -- J-IB-NPARIKH
3499: FOR UPDATE NOWAIT;
3500:
3507: --
3508: -- Debug Statements
3509: --
3510: IF l_debug_on THEN
3511: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3512: END IF;
3513: --
3514: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3515: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS);
3510: IF l_debug_on THEN
3511: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3512: END IF;
3513: --
3514: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3515: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS);
3516: goto loop_end ;
3517: END IF;
3518:
3541: --
3542: -- Debug Statements
3543: --
3544: IF l_debug_on THEN
3545: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3546: END IF;
3547: --
3548: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3549: FND_MESSAGE.SET_TOKEN('SHIP_METHOD',l_ship_method_code);
3544: IF l_debug_on THEN
3545: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3546: END IF;
3547: --
3548: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3549: FND_MESSAGE.SET_TOKEN('SHIP_METHOD',l_ship_method_code);
3550: FND_MESSAGE.SET_TOKEN('ORG_ID',l_organization_id);
3551: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
3552: x_return_Status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
3567: --
3568: -- Debug Statements
3569: --
3570: IF l_debug_on THEN
3571: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3572: END IF;
3573: --
3574: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3575: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3570: IF l_debug_on THEN
3571: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3572: END IF;
3573: --
3574: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3575: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3576: wsh_util_core.add_message(x_return_status);
3577:
3578: l_num_warn := l_num_warn + 1;
3596: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.VALIDATE_DFF',WSH_DEBUG_SV.C_PROC_LEVEL);
3597: END IF;
3598: --
3599: wsh_flexfield_utils.Validate_DFF
3600: (p_table_name => 'WSH_NEW_DELIVERIES',
3601: p_primary_id => p_del_rows(i),
3602: x_return_status => x_return_status);
3603:
3604: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3606: --
3607: -- Debug Statements
3608: --
3609: IF l_debug_on THEN
3610: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3611: END IF;
3612: --
3613: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3614: wsh_util_core.add_message(x_return_status);
3609: IF l_debug_on THEN
3610: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3611: END IF;
3612: --
3613: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3614: wsh_util_core.add_message(x_return_status);
3615: if ( x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR ) then
3616: l_num_error := l_num_error + 1 ;
3617: goto loop_end ;
3636: --
3637: -- Debug Statements
3638: --
3639: IF l_debug_on THEN
3640: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3641: END IF;
3642: --
3643: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3644: wsh_util_core.add_message(x_return_status);
3639: IF l_debug_on THEN
3640: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3641: END IF;
3642: --
3643: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3644: wsh_util_core.add_message(x_return_status);
3645: END IF;
3646: --
3647: IF (nvl(l_del_valid_flag,'Y') = 'N') THEN
3748: ELSE
3749: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
3750: END IF;
3751: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_entity_name);
3752: FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3753: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR',l_exc_beh_error);
3754: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3755: wsh_util_core.add_message(x_return_status);
3756: l_num_error := l_num_error + 1 ;
3758: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
3759: IF l_exceptions_tab(exp_cnt).entity_name = 'DELIVERY' THEN
3760: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
3761: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_entity_name);
3762: FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3763: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR',l_exc_beh_warn);
3764: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3765: wsh_util_core.add_message(x_return_status);
3766: l_num_warn := l_num_warn + 1 ;
3766: l_num_warn := l_num_warn + 1 ;
3767: ELSIF NOT (l_exp_logged) THEN
3768: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
3769: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_entity_name);
3770: FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3771: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR',l_exc_beh_warn);
3772: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3773: l_exp_logged := TRUE;
3774: wsh_util_core.add_message(x_return_status);
4055: --
4056: -- Debug Statements
4057: --
4058: IF l_debug_on THEN
4059: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4060: END IF;
4061: --
4062: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4063: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4058: IF l_debug_on THEN
4059: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4060: END IF;
4061: --
4062: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4063: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4064: wsh_util_core.add_message(x_return_status);
4065: l_num_warn := l_num_warn + 1;
4066:
4676: --
4677: -- Debug Statements
4678: --
4679: IF l_debug_on THEN
4680: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4681: END IF;
4682: --
4683: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
4684: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
4679: IF l_debug_on THEN
4680: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4681: END IF;
4682: --
4683: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
4684: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
4685: l_num_error := l_num_error + 1;
4686: goto confirm_error_no_msg;
4687: ELSE
5147: --
5148: -- Debug Statements
5149: --
5150: IF l_debug_on THEN
5151: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5152: END IF;
5153: --
5154: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5155: x_return_status := l_return_status;
5150: IF l_debug_on THEN
5151: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5152: END IF;
5153: --
5154: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5155: x_return_status := l_return_status;
5156: wsh_util_core.add_message(x_return_status);
5157: l_num_error := l_num_error + 1 ;
5158: goto loop_end;
5391:
5392: IF (l_param_info.percent_fill_basis_flag = 'W' and l_gross_weight is NULL) THEN
5393: FND_MESSAGE.SET_NAME('WSH','WSH_NULL_WV');
5394: IF l_debug_on THEN
5395: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5396: END IF;
5397: --
5398: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5399: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5395: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5396: END IF;
5397: --
5398: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5399: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5400: FND_MESSAGE.SET_TOKEN('WV','Weight');
5401: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5402: wsh_util_core.add_message(x_return_status);
5403: l_num_warn := l_num_warn + 1;
5405:
5406: IF (l_param_info.percent_fill_basis_flag = 'V' and l_volume is NULL) THEN
5407: FND_MESSAGE.SET_NAME('WSH','WSH_NULL_WV');
5408: IF l_debug_on THEN
5409: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5410: END IF;
5411: --
5412: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5413: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5409: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5410: END IF;
5411: --
5412: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5413: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5414: FND_MESSAGE.SET_TOKEN('WV','Volume');
5415: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5416: wsh_util_core.add_message(x_return_status);
5417: l_num_warn := l_num_warn + 1;
5539: --
5540: -- Debug Statements
5541: --
5542: IF l_debug_on THEN
5543: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5544: END IF;
5545: --
5546: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5547: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5542: IF l_debug_on THEN
5543: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5544: END IF;
5545: --
5546: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5547: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5548: wsh_util_core.add_message(x_return_status);
5549: goto confirm_error_no_msg;
5550:
5625: --
5626: -- Debug Statements
5627: --
5628: IF l_debug_on THEN
5629: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5630: END IF;
5631: --
5632: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5633: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5628: IF l_debug_on THEN
5629: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5630: END IF;
5631: --
5632: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5633: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5634: wsh_util_core.add_message(x_return_status);
5635: l_num_warn := l_num_warn + 1;
5636: END IF;
5728: IF l_cc_failed_records.COUNT>0 THEN
5729:
5730: IF l_debug_on THEN
5731: wsh_debug_sv.logmsg(l_module_name,'all lines errored in compatibility check');
5732: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5733: END IF;
5734:
5735: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
5736: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5732: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5733: END IF;
5734:
5735: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
5736: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5737: x_return_status := l_return_status;
5738: wsh_util_core.add_message(x_return_status);
5739: goto confirm_error;
5740: ELSE
5776: x_return_status := l_return_status;
5777: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_AUTOCREATE_TRIP_ERROR');
5778:
5779: IF l_debug_on THEN
5780: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5781: END IF;
5782: --
5783: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5784: wsh_util_core.add_message(x_return_status);
5779: IF l_debug_on THEN
5780: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5781: END IF;
5782: --
5783: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5784: wsh_util_core.add_message(x_return_status);
5785: goto confirm_error;
5786: END IF;
5787: END IF;
5945:
5946: WHEN app_exception.application_exception OR app_exception.record_lock_exception THEN
5947: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5948: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_STOP_TRIP_LOCK');
5949: FND_MESSAGE.SET_TOKEN('DLVY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5950: wsh_util_core.add_message(x_return_status);
5951: goto confirm_error;
5952:
5953: END;
6076: IF l_cc_fail_ids.COUNT>0 THEN
6077:
6078: IF l_debug_on THEN
6079: wsh_debug_sv.logmsg(l_module_name,'all lines errored in compatibility check');
6080: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6081: END IF;
6082:
6083: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
6084: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6080: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6081: END IF;
6082:
6083: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
6084: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6085: x_return_status := l_return_status;
6086: wsh_util_core.add_message(x_return_status);
6087: goto confirm_error;
6088: ELSE
6286: --
6287: -- Debug Statements
6288: --
6289: IF l_debug_on THEN
6290: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6291: END IF;
6292: --
6293: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6294: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6289: IF l_debug_on THEN
6290: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6291: END IF;
6292: --
6293: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6294: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6295: wsh_util_core.add_message(x_return_status);
6296: WSH_NEW_DELIVERY_ACTIONS.g_error_level := 'E';
6297: l_num_warn := l_num_warn + 1;
6368: --
6369: -- Debug Statements
6370: --
6371: IF l_debug_on THEN
6372: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6373: END IF;
6374: --
6375: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6376: wsh_util_core.add_message(x_return_status);
6371: IF l_debug_on THEN
6372: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6373: END IF;
6374: --
6375: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6376: wsh_util_core.add_message(x_return_status);
6377:
6378: /* H integration: 940/945 wrudge */
6379: <
6612: --
6613: -- Debug Statements
6614: --
6615: IF l_debug_on THEN
6616: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6617: END IF;
6618: --
6619:
6620: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6616: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6617: END IF;
6618: --
6619:
6620: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6621: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6622: wsh_util_core.add_message(x_return_status);
6623: l_num_warn := l_num_warn + 1;
6624: END IF;
6650: -- Debug Statements
6651: --
6652:
6653: IF l_debug_on THEN
6654: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6655: END IF;
6656: --
6657: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6658: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6653: IF l_debug_on THEN
6654: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6655: END IF;
6656: --
6657: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6658: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6659: wsh_util_core.add_message(x_return_status);
6660: l_num_warn := l_num_warn + 1;
6661: END IF;
6722: --
6723: -- Debug Statements
6724: --
6725: IF l_debug_on THEN
6726: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6727: END IF;
6728: --
6729: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6730: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6725: IF l_debug_on THEN
6726: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6727: END IF;
6728: --
6729: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6730: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6731: wsh_util_core.add_message(x_return_status);
6732: l_num_warn := l_num_warn + 1;
6733: END IF;
6760: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
6761: l_num_error := l_num_error + 1;
6762: ELSE
6763: IF l_debug_on THEN
6764: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6765: END IF;
6766: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6767: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6768: wsh_util_core.add_message(x_return_status);
6762: ELSE
6763: IF l_debug_on THEN
6764: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6765: END IF;
6766: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6767: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6768: wsh_util_core.add_message(x_return_status);
6769: l_num_warn := l_num_warn + 1;
6770: END IF;
6847: -- Update the delivery record with status code and confirm date
6848: -- 2052673 - modified the way in which the number_of_lpn is being updated.
6849: -- 2335270 included last_updated_by and last_update_login
6850: FORALL i IN 1..u_del_rows.count
6851: UPDATE wsh_new_deliveries
6852: SET status_code = u_status_code(i),
6853: confirm_date = sysdate,
6854: confirmed_by = nvl(confirmed_by, l_user_name),
6855: -- number_of_lpn = decode(u_number_of_lpn(i),0,null,u_number_of_lpn(i)) , --Bug 1729723
6859: --OTM R12, when setting delivery to closed, set the tms flag
6860: TMS_INTERFACE_FLAG = DECODE(l_gc3_is_installed,
6861: 'Y', DECODE(NVL(u_status_code(i), 'XXXXX'),
6862: 'CL', DECODE(NVL(tms_interface_flag,
6863: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
6864: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6865: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6866: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6867: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6860: TMS_INTERFACE_FLAG = DECODE(l_gc3_is_installed,
6861: 'Y', DECODE(NVL(u_status_code(i), 'XXXXX'),
6862: 'CL', DECODE(NVL(tms_interface_flag,
6863: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
6864: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6865: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6866: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6867: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6868: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
6861: 'Y', DECODE(NVL(u_status_code(i), 'XXXXX'),
6862: 'CL', DECODE(NVL(tms_interface_flag,
6863: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
6864: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6865: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6866: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6867: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6868: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
6869: NVL(tms_interface_flag,
6862: 'CL', DECODE(NVL(tms_interface_flag,
6863: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
6864: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6865: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6866: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6867: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6868: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
6869: NVL(tms_interface_flag,
6870: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6863: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
6864: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6865: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6866: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6867: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6868: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
6869: NVL(tms_interface_flag,
6870: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6871: NVL(tms_interface_flag,
6864: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6865: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
6866: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6867: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6868: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
6869: NVL(tms_interface_flag,
6870: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6871: NVL(tms_interface_flag,
6872: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6866: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6867: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
6868: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
6869: NVL(tms_interface_flag,
6870: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6871: NVL(tms_interface_flag,
6872: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6873: --END OTM R12
6874: last_update_date = SYSDATE ,
6868: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
6869: NVL(tms_interface_flag,
6870: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6871: NVL(tms_interface_flag,
6872: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
6873: --END OTM R12
6874: last_update_date = SYSDATE ,
6875: last_updated_by = l_user_id,
6876: last_update_login = l_login_id,
6958: aname => 'ACTUAL_DATE',
6959: avalue => P_ACTUAL_DEP_DATE);
6960:
6961: FND_MESSAGE.SET_NAME('WSH','WSH_WF_SCPOD_LAUNCHED');
6962: FND_MESSAGE.Set_Token('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(l_ctr)));
6963: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS,l_module_name);
6964:
6965: END IF;
6966: END LOOP;
7012: --
7013: -- Debug Statements
7014: --
7015: IF l_debug_on THEN
7016: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7017: END IF;
7018: --
7019: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
7020: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7015: IF l_debug_on THEN
7016: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7017: END IF;
7018: --
7019: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
7020: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7021: wsh_util_core.add_message(x_return_status);
7022: l_num_warn := l_num_warn + 1;
7023: END IF;
7201: --
7202: -- Debug Statements
7203: --
7204: IF l_debug_on THEN
7205: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7206: END IF;
7207: --
7208: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
7209: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7204: IF l_debug_on THEN
7205: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7206: END IF;
7207: --
7208: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
7209: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7210: wsh_util_core.add_message(x_return_status);
7211: WSH_UTIL_CORE.Store_Msg_In_Table (p_store_flag => FALSE,
7212: x_msg_rec_count => l_msg_rec_count,
7539: END IF;
7540: -- End of Code for DBI Project
7541: --
7542: --
7543: UPDATE wsh_new_deliveries
7544: SET initial_pickup_date = p_in_rec.actual_date,
7545: ultimate_dropoff_date = GREATEST(
7546: NVL(ultimate_dropoff_date,p_in_rec.actual_date),
7547: p_in_rec.actual_date
7809: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED; -- this is actual function
7810: END IF;
7811: --
7812: --
7813: UPDATE wsh_new_deliveries
7814: SET ultimate_dropoff_date = GREATEST(
7815: nvl(ultimate_dropoff_date,p_in_rec.actual_date),
7816: p_in_rec.actual_date
7817: ),
7818: status_code = 'CL',
7819: --OTM R12, when setting delivery to closed, set tms flag
7820: tms_interface_flag = DECODE(l_gc3_is_installed,
7821: 'Y', DECODE(NVL(tms_interface_flag,
7822: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7823: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7824: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7825: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7826: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7819: --OTM R12, when setting delivery to closed, set tms flag
7820: tms_interface_flag = DECODE(l_gc3_is_installed,
7821: 'Y', DECODE(NVL(tms_interface_flag,
7822: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7823: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7824: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7825: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7826: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7827: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7820: tms_interface_flag = DECODE(l_gc3_is_installed,
7821: 'Y', DECODE(NVL(tms_interface_flag,
7822: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7823: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7824: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7825: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7826: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7827: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7828: NVL(tms_interface_flag,
7821: 'Y', DECODE(NVL(tms_interface_flag,
7822: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7823: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7824: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7825: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7826: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7827: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7828: NVL(tms_interface_flag,
7829: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7822: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7823: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7824: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7825: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7826: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7827: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7828: NVL(tms_interface_flag,
7829: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7830: --
7823: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7824: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7825: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7826: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7827: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7828: NVL(tms_interface_flag,
7829: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7830: --
7831: last_update_date = SYSDATE ,
7825: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7826: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7827: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7828: NVL(tms_interface_flag,
7829: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7830: --
7831: last_update_date = SYSDATE ,
7832: last_updated_by = l_user_id,
7833: last_update_login = l_login_id
8032: p_caller IN VARCHAR2) IS
8033:
8034: CURSOR del_status (l_delivery_id NUMBER) IS
8035: SELECT status_code, name,organization_id
8036: FROM wsh_new_deliveries
8037: WHERE delivery_id = l_delivery_id;
8038:
8039: CURSOR delivery_leg_stops(l_delivery_id NUMBER) IS
8040: SELECT dg.pick_up_stop_id,
8398: wsh_debug_sv.log (l_module_name, 'l_status_code',l_status_code);
8399: END IF;
8400:
8401:
8402: update wsh_new_deliveries
8403: set status_code = l_status_code,
8404: confirm_date = null,--6453740
8405: confirmed_by = null,--6453740
8406: last_update_date = SYSDATE ,
8419: -- Reopen the consol delivery as well if it exists and is not open
8420: IF l_status_code = 'OP' THEN
8421:
8422: -- bug 4891897, sql 15037864
8423: -- removed wsh_new_deliveries d2
8424:
8425: update wsh_new_deliveries d1
8426: set d1.status_code = l_status_code,
8427: d1.last_update_date = SYSDATE,
8421:
8422: -- bug 4891897, sql 15037864
8423: -- removed wsh_new_deliveries d2
8424:
8425: update wsh_new_deliveries d1
8426: set d1.status_code = l_status_code,
8427: d1.last_update_date = SYSDATE,
8428: d1.last_updated_by = l_user_id,
8429: d1.last_update_login = l_login_id
8566: -- Following is old procedure as of I, which is replaced by proc. above in J
8567: -- remove after ut
8568:
8569: /* bug 2335270 Included last_update_date,last_updated_by and last_update_login
8570: in wsh_new_deliveries and wsh_delivery_details */
8571:
8572: -- J-IB-HEALI---{
8573: PROCEDURE Complete_Leg_Sequence
8574: ( p_delivery_id IN NUMBER,
8580:
8581: CURSOR delivery_info IS
8582: SELECT initial_pickup_location_id,
8583: ultimate_dropoff_location_id
8584: FROM wsh_new_deliveries
8585: WHERE delivery_id = p_delivery_id;
8586:
8587: CURSOR count_legs IS
8588: SELECT count(*)
8725: CLOSE pickup_delivery_legs;
8726:
8727: IF (p_insert_msg) THEN
8728: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTIPLE_LEGS');
8729: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8730: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8731: END IF;
8732:
8733: IF l_debug_on THEN
8744:
8745: IF (l_count > 1) THEN
8746: IF (p_insert_msg) THEN
8747: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTIPLE_LEGS');
8748: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8749: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8750: END IF;
8751:
8752: IF l_debug_on THEN
8764: IF (l_seq_num <> l_leg_count) THEN
8765:
8766: IF (p_insert_msg) THEN
8767: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_INVALID_FLOW');
8768: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8769: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8770: END IF;
8771: --
8772: IF l_debug_on THEN
8781: x_leg_complete := 'NW';
8782:
8783: IF (p_insert_msg) THEN
8784: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_NO_ULTIMATE_DROPOFF');
8785: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8786: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8787: END IF;
8788: RETURN;
8789: END IF;
8939: END IF;
8940:
8941:
8942: IF (p_update_del_flag='Y') THEN
8943: UPDATE wsh_new_deliveries
8944: SET ITINERARY_COMPLETE = l_update_flag,
8945: last_update_date = sysdate,
8946: last_updated_by = FND_GLOBAL.USER_ID
8947: WHERE delivery_id= p_delivery_id;
8985: x_return_status OUT NOCOPY VARCHAR2) IS
8986:
8987: CURSOR loading_order (l_delivery_id NUMBER) IS
8988: SELECT loading_order_flag
8989: FROM wsh_new_deliveries
8990: WHERE delivery_id = l_delivery_id;
8991:
8992: -- Bug 1421549: Changed the logic for load sequencing
8993: cont_str VARCHAR2(1000) :=
9079: --
9080: -- Debug Statements
9081: --
9082: IF l_debug_on THEN
9083: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9084: END IF;
9085: --
9086: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9087: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9082: IF l_debug_on THEN
9083: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9084: END IF;
9085: --
9086: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9087: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9088: wsh_util_core.add_message(x_return_status);
9089: l_num_error := l_num_error + 1;
9090: goto loop_end;
9152: --
9153: -- Debug Statements
9154: --
9155: IF l_debug_on THEN
9156: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9157: END IF;
9158: --
9159: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9160: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9155: IF l_debug_on THEN
9156: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9157: END IF;
9158: --
9159: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9160: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9161: wsh_util_core.add_message(x_return_status);
9162: l_num_error := l_num_error + 1;
9163: EXIT;
9174: --
9175: -- Debug Statements
9176: --
9177: IF l_debug_on THEN
9178: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9179: END IF;
9180: --
9181: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9182: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9177: IF l_debug_on THEN
9178: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9179: END IF;
9180: --
9181: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9182: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9183: wsh_util_core.add_message(x_return_status);
9184: l_num_error := l_num_error + 1;
9185: END IF;
9252: (p_delivery_id IN NUMBER,
9253: p_del_params IN wsh_delivery_autocreate.grp_attr_rec_type,
9254: x_return_status OUT NOCOPY VARCHAR2) IS
9255:
9256: l_del_info wsh_new_deliveries_pvt.delivery_rec_type;
9257:
9258: --
9259: l_debug_on BOOLEAN;
9260: --
9282: --
9283: -- Debug Statements
9284: --
9285: IF l_debug_on THEN
9286: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
9287: END IF;
9288: --
9289: wsh_new_deliveries_pvt.populate_record( p_delivery_id, l_del_info, x_return_status);
9290:
9285: IF l_debug_on THEN
9286: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
9287: END IF;
9288: --
9289: wsh_new_deliveries_pvt.populate_record( p_delivery_id, l_del_info, x_return_status);
9290:
9291: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
9292: --
9293: -- Debug Statements
9324: --
9325: -- Debug Statements
9326: --
9327: IF l_debug_on THEN
9328: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
9329: END IF;
9330: --
9331: wsh_new_deliveries_pvt.update_delivery( null, l_del_info, x_return_status);
9332:
9327: IF l_debug_on THEN
9328: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
9329: END IF;
9330: --
9331: wsh_new_deliveries_pvt.update_delivery( null, l_del_info, x_return_status);
9332:
9333: --}
9334: END IF;
9335:
9391: CURSOR dlvy_csr (p_delivery_id IN NUMBER)
9392: IS
9393: SELECT freight_terms_code, name,
9394: nvl(shipment_direction,'O') shipment_direction
9395: FROM wsh_new_deliveries
9396: WHERE delivery_id = p_delivery_id;
9397: --
9398: --
9399: -- Lock delivery
9400: --
9401: CURSOR lock_dlvy_csr (p_delivery_id IN NUMBER)
9402: IS
9403: SELECT 1
9404: FROM wsh_new_deliveries
9405: WHERE delivery_id = p_delivery_id
9406: FOR UPDATE OF freight_terms_code NOWAIT;
9407: --
9408: --
9664: --tms_interface_flag and tms_version_number should already be
9665: --reflecting the change in delivery,
9666: --no need to set them again.
9667:
9668: UPDATE wsh_new_deliveries
9669: SET freight_terms_code = l_dlvy_freight_terms_code,
9670: last_update_date = SYSDATE,
9671: last_updated_by = FND_GLOBAL.USER_ID,
9672: last_update_login = FND_GLOBAL.LOGIN_ID
9703: IF l_debug_on THEN
9704: wsh_debug_sv.LOGMSG(l_module_name, 'Updating delivery WHO columns ' );
9705: END IF;
9706: --
9707: UPDATE wsh_new_deliveries
9708: SET last_update_date = SYSDATE,
9709: last_updated_by = FND_GLOBAL.USER_ID,
9710: last_update_login = FND_GLOBAL.LOGIN_ID
9711: WHERE delivery_id = p_delivery_id;
9790: CURSOR dlvy_csr (p_delivery_id IN NUMBER)
9791: IS
9792: SELECT wnd.name,
9793: initial_pickup_location_id
9794: FROM wsh_new_deliveries wnd
9795: WHERE delivery_id = p_delivery_id;
9796: --
9797: l_dlvy_rec dlvy_csr%ROWTYPE;
9798: --
9817: CURSOR pickup_dlvy_csr (p_delivery_id IN NUMBER,p_stop_id IN NUMBER)
9818: IS
9819: SELECT wnd.delivery_id, wnd.name
9820: FROM wsh_delivery_legs wdl,
9821: wsh_new_deliveries wnd
9822: WHERE pick_up_stop_id = p_stop_id
9823: AND wdl.delivery_id <> p_delivery_id
9824: AND wdl.delivery_id = wnd.delivery_id;
9825: --
9906: FETCH stop_csr INTO stop_rec;
9907: CLOSE stop_csr;
9908: --
9909: /*
9910: UPDATE WSH_NEW_DELIVERIES
9911: SET INITIAL_PICKUP_LOCATION_ID = p_location_id,
9912: last_update_date = SYSDATE,
9913: last_updated_by = FND_GLOBAL.USER_ID,
9914: last_update_login = FND_GLOBAL.LOGIN_ID
10046: LOOP
10047: --{
10048: --OTM R12, this update is called by inbound so we
10049: --will not check tms flag here
10050: UPDATE WSH_NEW_DELIVERIES
10051: SET INITIAL_PICKUP_LOCATION_ID = p_location_id,
10052: last_update_date = SYSDATE,
10053: last_updated_by = FND_GLOBAL.USER_ID,
10054: last_update_login = FND_GLOBAL.LOGIN_ID
10177: CURSOR Check_Last_Trip (l_delivery_id NUMBER) IS
10178: SELECT s1.trip_id
10179: FROM wsh_trip_stops s1,
10180: wsh_delivery_legs dl1,
10181: wsh_new_deliveries d1,
10182: wsh_trip_stops s2,
10183: wsh_delivery_legs dl2
10184: WHERE d1.delivery_id <> l_delivery_id
10185: AND s1.stop_id = dl1.pick_up_stop_id
10293: -- count('CONSOLIDATION') should be 1
10294:
10295: CURSOR check_shared_trip_consol(x_delivery_leg_id NUMBER) IS
10296: SELECT count(*), min(s.trip_id)
10297: FROM wsh_new_deliveries n, wsh_delivery_legs d, wsh_trip_stops s
10298: WHERE n.delivery_id = d.delivery_id
10299: AND d.pick_up_stop_id = s.stop_id
10300: AND s.trip_id = (SELECT s1.trip_id
10301: FROM wsh_trip_stops s1, wsh_delivery_legs d1
11056: -- p_smc_tab IN WSH_NEW_DELIVERY_ACTIONS.TableVarchar30 --
11057: -- p_org_del_id_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers --
11058: -- p_trip_id_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers --
11059: -- --
11060: -- PARAMETERS (OUT): x_rec_attr_tab OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type --
11061: -- x_trip_info_tab OUT NOCOPY WSH_TRIPS_PVT.Trip_Attr_Tbl_Type
11062: --
11063: --
11064: -- RETURN: none --
11087:
11088: PROCEDURE FORMAT_DEL_UPDATE_TABLE(--p_cs_result_tab IN FTE_ACS_PKG.FTE_CS_RESULT_TAB_TYPE,
11089: p_cs_result_tab IN WSH_FTE_INTEGRATION.WSH_CS_RESULT_TAB_TYPE,
11090: p_caller IN VARCHAR2 DEFAULT NULL,
11091: x_rec_attr_tab OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
11092: x_trip_info_tab OUT NOCOPY WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
11093: x_return_message OUT NOCOPY VARCHAR2,
11094: x_return_status OUT NOCOPY VARCHAR2) IS
11095:
11548: fob_code,
11549: shipment_direction,
11550: delivery_type,
11551: NULL -- l_trip_id
11552: from wsh_new_deliveries
11553: where delivery_id = x_delivery_id
11554: and ship_method_code is null
11555: AND initial_pickup_location_id <> WSH_UTIL_CORE.C_NULL_SF_LOCN_ID -- J-IB-NPARIKH
11556: and planned_flag NOT IN ('Y','F');
11579: fob_code,
11580: shipment_direction,
11581: delivery_type,
11582: NULL -- l_trip_id
11583: from wsh_new_deliveries
11584: where batch_id = x_batch_id
11585: and organization_id = x_organization_id
11586: and ship_method_code is null
11587: and planned_flag NOT IN ('Y','F');
11608: fob_code,
11609: shipment_direction,
11610: delivery_type,
11611: NULL -- l_trip_id
11612: from wsh_new_deliveries
11613: where delivery_id = f_delivery_id;
11614:
11615: CURSOR check_del_assigned(p_del_id IN NUMBER) IS
11616: select wts.trip_id,
11625: -- AG not required
11626: /*
11627: CURSOR get_org_for_delivery(p_del_id IN NUMBER) IS
11628: select wnd.organization_id
11629: from wsh_new_deliveries wnd
11630: where wnd.delivery_id = p_del_id;
11631: */
11632:
11633:
11655: --
11656:
11657: l_in_rec WSH_DELIVERIES_GRP.Del_In_Rec_Type;
11658: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
11659: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
11660: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
11661: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
11662: l_del_out_rec_tab WSH_DELIVERIES_GRP.Del_Out_Tbl_Type;
11663: l_msg_count NUMBER;
12769: -- x_return_status: status
12770: **/
12771:
12772: Procedure Assign_Del_to_Consol_Del(
12773: p_del_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
12774: p_parent_del_id IN NUMBER,
12775: p_caller IN VARCHAR2,
12776: x_return_status OUT NOCOPY VARCHAR2) IS
12777:
12787: s1.planned_arrival_date pu_ar_date,
12788: s1.planned_departure_date pu_dep_date,
12789: s2.planned_arrival_date do_ar_date,
12790: s2.planned_departure_date do_dep_date
12791: from wsh_trip_stops s1, wsh_trip_stops s2, wsh_delivery_legs l, wsh_new_deliveries d, wsh_trips t
12792: where s1.stop_id = l.pick_up_stop_id
12793: and s2.stop_id = l.drop_off_stop_id
12794: and l.delivery_id = d.delivery_id
12795: and d.delivery_id = p_parent_del_id
12801: -- make sure that the delivery is open, STANDARD and
12802: -- is not assigned to a parent delivery.
12803: cursor c_check_valid_child(p_delivery_id in number) is
12804: select d.delivery_id
12805: from wsh_new_deliveries d
12806: where d.delivery_id = p_delivery_id
12807: and d.status_code = 'OP'
12808: and d.delivery_type = 'STANDARD'
12809: and not exists (select 1 from wsh_delivery_legs
12818: */
12819: -- check if the trip the delivery is assigned to is valid
12820: cursor c_check_trips(p_delivery_id in number) is
12821: select l.delivery_leg_id, s1.trip_id
12822: from wsh_delivery_legs l, wsh_trip_stops s1, wsh_new_deliveries d
12823: where l.delivery_id = p_delivery_id
12824: and d.delivery_id = l.delivery_id
12825: and d.initial_pickup_location_id = s1.stop_location_id
12826: and s1.stop_id = l.pick_up_stop_id;
13076: EXCEPTION
13077: WHEN OTHERS THEN
13078: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13079: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
13080: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_valid_children_tab(i)));
13081: WSH_UTIL_CORE.Add_Message(x_return_status);
13082: --
13083: -- Debug Statements
13084: --
13298: EXCEPTION
13299: WHEN OTHERS THEN
13300: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13301: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
13302: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_valid_children_tab(i)));
13303: WSH_UTIL_CORE.Add_Message(x_return_status);
13304: --
13305: -- Debug Statements
13306: --
13604: x_return_status OUT NOCOPY VARCHAR2) IS
13605:
13606: CURSOR c_check_parent_exists(p_child_delivery_id in number, p_parent_delivery_id in number) IS
13607: select l1.delivery_id, l2.delivery_id, l1.delivery_leg_id, s.trip_id
13608: from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d, wsh_trip_stops s
13609: where l1.parent_delivery_leg_id = l2.delivery_leg_id
13610: and l1.delivery_id = p_child_delivery_id
13611: and l2.delivery_id = NVL(p_parent_delivery_id, l2.delivery_id)
13612: and d.delivery_id = l1.delivery_id
13622: and rownum = 1;
13623:
13624: CURSOR c_get_children(p_parent_delivery_id IN NUMBER) is
13625: select l1.delivery_id, l1.delivery_leg_id, s.trip_id
13626: from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d, wsh_trip_stops s
13627: where l1.parent_delivery_leg_id = l2.delivery_leg_id
13628: and l2.delivery_id = p_parent_delivery_id
13629: and d.delivery_id = l1.delivery_id
13630: and d.status_code = 'OP'
13754: FETCH check_consol_lpns INTO l_consol_lpn_dummy;
13755: IF check_consol_lpns%FOUND THEN
13756: CLOSE check_consol_lpns;
13757: FND_MESSAGE.SET_NAME('WSH','WSH_FAIL_UNASSIGN_DEL_LPN');
13758: FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_assigned_dels_tab(i)));
13759: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13760: wsh_util_core.add_message(l_return_status, l_module_name);
13761: wsh_util_core.api_post_call
13762: (
13791: EXCEPTION
13792: WHEN OTHERS THEN
13793: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13794: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
13795: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_assigned_dels_tab(i)));
13796: WSH_UTIL_CORE.Add_Message(x_return_status);
13797: --
13798: -- Debug Statements
13799: --
13861: EXCEPTION
13862: WHEN OTHERS THEN
13863: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13864: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_DEL_LEG_LOCK');
13865: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_assigned_dels_tab(i)));
13866: WSH_UTIL_CORE.Add_Message(x_return_status);
13867: --
13868: -- Debug Statements
13869: --
14071: x_return_status OUT NOCOPY VARCHAR2) IS
14072:
14073: cursor c_check_close_consol(p_del_id IN NUMBER) IS
14074: select d.delivery_id
14075: from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d
14076: where l1.parent_delivery_leg_id = l2.delivery_leg_id
14077: and l2.delivery_id = p_del_id
14078: and l1.delivery_id = d.delivery_id
14079: and d.status_code = 'OP'
14109:
14110: -- bug 4505105
14111: cursor l_get_consol_del_org_csr (p_del_id IN NUMBER) is
14112: select organization_id
14113: from wsh_new_deliveries
14114: where delivery_id = p_del_id;
14115:
14116: l_return_status VARCHAR2(1);
14117: l_del_valid_flag VARCHAR2(1);
14264: IF l_debug_on THEN
14265: WSH_DEBUG_SV.log(l_module_name,'l_co_consol_del_tab(l)', l_co_consol_del_tab(l));
14266: END IF;
14267: l_lock_index := l;
14268: WSH_NEW_DELIVERIES_PVT.Lock_Dlvy_No_Compare(l_co_consol_del_tab(l));
14269:
14270: l_lpn_number := 0;
14271: OPEN c_get_child_lpn_number(l_co_consol_del_tab(l));
14272: FETCH c_get_child_lpn_number
14287: EXCEPTION
14288: WHEN OTHERS THEN
14289: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
14290: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
14291: FND_MESSAGE.Set_Token('ENTITY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_co_consol_del_tab(l_lock_index)));
14292: WSH_UTIL_CORE.Add_Message(x_return_status);
14293: --
14294: -- Debug Statements
14295: --
14304: WSH_DEBUG_SV.logmsg(l_module_name,'after lock');
14305: END IF;
14306: IF l_co_consol_del_tab.count > 0 THEN
14307: FORALL m in 1..l_co_consol_del_tab.count
14308: UPDATE wsh_new_deliveries
14309: SET status_code = 'CO',
14310: number_of_lpn = decode(number_of_lpn, NULL, l_number_of_lpn_tab(m), number_of_lpn)
14311: WHERE delivery_id = l_co_consol_del_tab(m);
14312: END IF;
14352: child.carrier_id,
14353: child.service_level,
14354: child.mode_of_transport,
14355: child.ignore_for_planning
14356: FROM wsh_new_deliveries child,
14357: wsh_new_deliveries parent,
14358: wsh_delivery_legs child_leg,
14359: wsh_delivery_legs parent_leg,
14360: wsh_trip_stops stop
14353: child.service_level,
14354: child.mode_of_transport,
14355: child.ignore_for_planning
14356: FROM wsh_new_deliveries child,
14357: wsh_new_deliveries parent,
14358: wsh_delivery_legs child_leg,
14359: wsh_delivery_legs parent_leg,
14360: wsh_trip_stops stop
14361: WHERE child_leg.delivery_id = p_delivery_id