[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',
2250:
2251: -- frontport bug 4310141 / Bug 4103142 - to get first trip for delivery
2252: CURSOR c_first_ship_method (p_delivery_id IN number)IS
2253: SELECT wt.ship_method_code
2254: FROM wsh_new_deliveries del,
2255: wsh_delivery_legs dlg,
2256: wsh_trip_stops st,
2257: wsh_trips wt
2258: WHERE del.delivery_id = dlg.delivery_id
2677: --OTM R12
2678: ignore_for_planning,
2679: tms_interface_flag
2680: --
2681: FROM wsh_new_deliveries
2682: WHERE delivery_id = v_delivery_id;
2683:
2684: -- J: W/V Changes
2685: CURSOR get_delivery_wv(v_delivery_id NUMBER) IS
2684: -- J: W/V Changes
2685: CURSOR get_delivery_wv(v_delivery_id NUMBER) IS
2686: SELECT gross_weight,
2687: volume
2688: FROM wsh_new_deliveries
2689: WHERE delivery_id = v_delivery_id;
2690:
2691:
2692: CURSOR Get_Del_First_Trip(v_del_id NUMBER) IS
2690:
2691:
2692: CURSOR Get_Del_First_Trip(v_del_id NUMBER) IS
2693: SELECT wts.trip_id
2694: FROM wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_new_deliveries wnd
2695: WHERE wdl.pick_up_stop_id=wts.stop_id
2696: AND wnd.initial_pickup_location_id = wts.stop_location_id
2697: AND wnd.delivery_id = wdl.delivery_id
2698: AND wnd.delivery_id=v_del_id;
2736: from wsh_delivery_legs
2737: where delivery_id = t_delivery_id );
2738:
2739:
2740: l_batch_id WSH_NEW_DELIVERIES.batch_id%TYPE;
2741:
2742: l_source_code VARCHAR2(30); /*Bug 2096052 */
2743: l_source_code_flag VARCHAR2(1) := 'N'; /*Bug 2096052 */
2744: l_released_status VARCHAR2(1);
2983: b_tripalreadychecked VARCHAR2(1);
2984: l_count_hashtrip NUMBER;
2985:
2986: --dummy tables for calling validate_constraint_wrapper
2987: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2988: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
2989: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2990: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2991: l_cc_in_ids wsh_util_core.id_tab_type;
3122: l_consol_del_doc_set wsh_util_core.id_tab_type;
3123: l_content_del_flag BOOLEAN := TRUE;
3124:
3125: --OTM R12
3126: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3127: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3128: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3129: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3130: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3123: l_content_del_flag BOOLEAN := TRUE;
3124:
3125: --OTM R12
3126: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3127: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3128: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3129: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3130: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3131: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3125: --OTM R12
3126: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3127: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3128: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3129: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3130: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3131: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3132: l_otm_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
3133: l_gc3_is_installed VARCHAR2(1);
3126: l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
3127: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3128: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3129: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3130: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3131: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3132: l_otm_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
3133: l_gc3_is_installed VARCHAR2(1);
3134: --
3127: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
3128: l_otm_trip_id WSH_TRIPS.TRIP_ID%TYPE;
3129: l_otm_carrier_id WSH_NEW_DELIVERIES.CARRIER_ID%TYPE;
3130: l_otm_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
3131: l_otm_mode WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE;
3132: l_otm_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
3133: l_gc3_is_installed VARCHAR2(1);
3134: --
3135: -- Bug 8555654 : begin
3545: l_ship_to_location ,
3546: l_gross_weight ,
3547: l_net_weight ,
3548: l_volume
3549: FROM wsh_new_deliveries
3550: WHERE delivery_id = p_del_rows(i)
3551: AND nvl(shipment_direction,'O') IN ('O','IO') -- J-IB-NPARIKH
3552: FOR UPDATE NOWAIT;
3553:
3560: --
3561: -- Debug Statements
3562: --
3563: IF l_debug_on THEN
3564: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3565: END IF;
3566: --
3567: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3568: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS);
3563: IF l_debug_on THEN
3564: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3565: END IF;
3566: --
3567: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3568: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS);
3569: goto loop_end ;
3570: END IF;
3571:
3594: --
3595: -- Debug Statements
3596: --
3597: IF l_debug_on THEN
3598: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3599: END IF;
3600: --
3601: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3602: FND_MESSAGE.SET_TOKEN('SHIP_METHOD',l_ship_method_code);
3597: IF l_debug_on THEN
3598: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3599: END IF;
3600: --
3601: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3602: FND_MESSAGE.SET_TOKEN('SHIP_METHOD',l_ship_method_code);
3603: FND_MESSAGE.SET_TOKEN('ORG_ID',l_organization_id);
3604: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
3605: x_return_Status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
3620: --
3621: -- Debug Statements
3622: --
3623: IF l_debug_on THEN
3624: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3625: END IF;
3626: --
3627: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3628: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3623: IF l_debug_on THEN
3624: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3625: END IF;
3626: --
3627: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3628: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3629: wsh_util_core.add_message(x_return_status);
3630:
3631: l_num_warn := l_num_warn + 1;
3649: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.VALIDATE_DFF',WSH_DEBUG_SV.C_PROC_LEVEL);
3650: END IF;
3651: --
3652: wsh_flexfield_utils.Validate_DFF
3653: (p_table_name => 'WSH_NEW_DELIVERIES',
3654: p_primary_id => p_del_rows(i),
3655: x_return_status => x_return_status);
3656:
3657: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3659: --
3660: -- Debug Statements
3661: --
3662: IF l_debug_on THEN
3663: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3664: END IF;
3665: --
3666: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3667: wsh_util_core.add_message(x_return_status);
3662: IF l_debug_on THEN
3663: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3664: END IF;
3665: --
3666: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3667: wsh_util_core.add_message(x_return_status);
3668: if ( x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR ) then
3669: l_num_error := l_num_error + 1 ;
3670: goto loop_end ;
3689: --
3690: -- Debug Statements
3691: --
3692: IF l_debug_on THEN
3693: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3694: END IF;
3695: --
3696: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3697: wsh_util_core.add_message(x_return_status);
3692: IF l_debug_on THEN
3693: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3694: END IF;
3695: --
3696: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3697: wsh_util_core.add_message(x_return_status);
3698: END IF;
3699: --
3700: IF (nvl(l_del_valid_flag,'Y') = 'N') THEN
3801: ELSE
3802: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
3803: END IF;
3804: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_entity_name);
3805: FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3806: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR',l_exc_beh_error);
3807: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3808: wsh_util_core.add_message(x_return_status);
3809: l_num_error := l_num_error + 1 ;
3811: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
3812: IF l_exceptions_tab(exp_cnt).entity_name = 'DELIVERY' THEN
3813: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
3814: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_entity_name);
3815: FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3816: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR',l_exc_beh_warn);
3817: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3818: wsh_util_core.add_message(x_return_status);
3819: l_num_warn := l_num_warn + 1 ;
3819: l_num_warn := l_num_warn + 1 ;
3820: ELSIF NOT (l_exp_logged) THEN
3821: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
3822: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_entity_name);
3823: FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
3824: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR',l_exc_beh_warn);
3825: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3826: l_exp_logged := TRUE;
3827: wsh_util_core.add_message(x_return_status);
4108: --
4109: -- Debug Statements
4110: --
4111: IF l_debug_on THEN
4112: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4113: END IF;
4114: --
4115: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4116: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4111: IF l_debug_on THEN
4112: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4113: END IF;
4114: --
4115: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4116: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4117: wsh_util_core.add_message(x_return_status);
4118: l_num_warn := l_num_warn + 1;
4119:
4760: --
4761: -- Debug Statements
4762: --
4763: IF l_debug_on THEN
4764: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4765: END IF;
4766: --
4767: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
4768: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
4763: IF l_debug_on THEN
4764: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4765: END IF;
4766: --
4767: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
4768: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
4769: l_num_error := l_num_error + 1;
4770: goto confirm_error_no_msg;
4771: ELSE
5231: --
5232: -- Debug Statements
5233: --
5234: IF l_debug_on THEN
5235: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5236: END IF;
5237: --
5238: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5239: x_return_status := l_return_status;
5234: IF l_debug_on THEN
5235: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5236: END IF;
5237: --
5238: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5239: x_return_status := l_return_status;
5240: wsh_util_core.add_message(x_return_status);
5241: l_num_error := l_num_error + 1 ;
5242: goto loop_end;
5492:
5493: IF (l_param_info.percent_fill_basis_flag = 'W' and l_gross_weight is NULL) THEN
5494: FND_MESSAGE.SET_NAME('WSH','WSH_NULL_WV');
5495: IF l_debug_on THEN
5496: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5497: END IF;
5498: --
5499: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5500: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5496: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5497: END IF;
5498: --
5499: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5500: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5501: FND_MESSAGE.SET_TOKEN('WV','Weight');
5502: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5503: wsh_util_core.add_message(x_return_status);
5504: l_num_warn := l_num_warn + 1;
5506:
5507: IF (l_param_info.percent_fill_basis_flag = 'V' and l_volume is NULL) THEN
5508: FND_MESSAGE.SET_NAME('WSH','WSH_NULL_WV');
5509: IF l_debug_on THEN
5510: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5511: END IF;
5512: --
5513: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5514: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5510: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5511: END IF;
5512: --
5513: FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Delivery');
5514: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5515: FND_MESSAGE.SET_TOKEN('WV','Volume');
5516: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5517: wsh_util_core.add_message(x_return_status);
5518: l_num_warn := l_num_warn + 1;
5640: --
5641: -- Debug Statements
5642: --
5643: IF l_debug_on THEN
5644: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5645: END IF;
5646: --
5647: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5648: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5643: IF l_debug_on THEN
5644: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5645: END IF;
5646: --
5647: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5648: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5649: wsh_util_core.add_message(x_return_status);
5650: goto confirm_error_no_msg;
5651:
5726: --
5727: -- Debug Statements
5728: --
5729: IF l_debug_on THEN
5730: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5731: END IF;
5732: --
5733: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5734: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5729: IF l_debug_on THEN
5730: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5731: END IF;
5732: --
5733: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5734: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5735: wsh_util_core.add_message(x_return_status);
5736: l_num_warn := l_num_warn + 1;
5737: END IF;
5829: IF l_cc_failed_records.COUNT>0 THEN
5830:
5831: IF l_debug_on THEN
5832: wsh_debug_sv.logmsg(l_module_name,'all lines errored in compatibility check');
5833: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5834: END IF;
5835:
5836: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
5837: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5833: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5834: END IF;
5835:
5836: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
5837: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5838: x_return_status := l_return_status;
5839: wsh_util_core.add_message(x_return_status);
5840: goto confirm_error;
5841: ELSE
5877: x_return_status := l_return_status;
5878: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_AUTOCREATE_TRIP_ERROR');
5879:
5880: IF l_debug_on THEN
5881: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5882: END IF;
5883: --
5884: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5885: wsh_util_core.add_message(x_return_status);
5880: IF l_debug_on THEN
5881: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5882: END IF;
5883: --
5884: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
5885: wsh_util_core.add_message(x_return_status);
5886: goto confirm_error;
5887: END IF;
5888: END IF;
6046:
6047: WHEN app_exception.application_exception OR app_exception.record_lock_exception THEN
6048: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6049: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_STOP_TRIP_LOCK');
6050: FND_MESSAGE.SET_TOKEN('DLVY_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6051: wsh_util_core.add_message(x_return_status);
6052: goto confirm_error;
6053:
6054: END;
6177: IF l_cc_fail_ids.COUNT>0 THEN
6178:
6179: IF l_debug_on THEN
6180: wsh_debug_sv.logmsg(l_module_name,'all lines errored in compatibility check');
6181: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6182: END IF;
6183:
6184: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
6185: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6181: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6182: END IF;
6183:
6184: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_COMP_FAILED');
6185: FND_MESSAGE.SET_TOKEN('DEL_ID',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6186: x_return_status := l_return_status;
6187: wsh_util_core.add_message(x_return_status);
6188: goto confirm_error;
6189: ELSE
6426: --
6427: -- Debug Statements
6428: --
6429: IF l_debug_on THEN
6430: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6431: END IF;
6432: --
6433: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6434: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6429: IF l_debug_on THEN
6430: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6431: END IF;
6432: --
6433: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6434: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6435: wsh_util_core.add_message(x_return_status);
6436: WSH_NEW_DELIVERY_ACTIONS.g_error_level := 'E';
6437: l_num_warn := l_num_warn + 1;
6508: --
6509: -- Debug Statements
6510: --
6511: IF l_debug_on THEN
6512: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6513: END IF;
6514: --
6515: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6516: wsh_util_core.add_message(x_return_status);
6511: IF l_debug_on THEN
6512: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6513: END IF;
6514: --
6515: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
6516: wsh_util_core.add_message(x_return_status);
6517:
6518: /* H integration: 940/945 wrudge */
6519: <
6752: --
6753: -- Debug Statements
6754: --
6755: IF l_debug_on THEN
6756: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6757: END IF;
6758: --
6759:
6760: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6756: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6757: END IF;
6758: --
6759:
6760: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6761: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6762: wsh_util_core.add_message(x_return_status);
6763: l_num_warn := l_num_warn + 1;
6764: END IF;
6790: -- Debug Statements
6791: --
6792:
6793: IF l_debug_on THEN
6794: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6795: END IF;
6796: --
6797: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6798: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6793: IF l_debug_on THEN
6794: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6795: END IF;
6796: --
6797: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6798: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6799: wsh_util_core.add_message(x_return_status);
6800: l_num_warn := l_num_warn + 1;
6801: END IF;
6862: --
6863: -- Debug Statements
6864: --
6865: IF l_debug_on THEN
6866: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6867: END IF;
6868: --
6869: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6870: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6865: IF l_debug_on THEN
6866: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6867: END IF;
6868: --
6869: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6870: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6871: wsh_util_core.add_message(x_return_status);
6872: l_num_warn := l_num_warn + 1;
6873: END IF;
6900: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
6901: l_num_error := l_num_error + 1;
6902: ELSE
6903: IF l_debug_on THEN
6904: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6905: END IF;
6906: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6907: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6908: wsh_util_core.add_message(x_return_status);
6902: ELSE
6903: IF l_debug_on THEN
6904: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6905: END IF;
6906: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(i)));
6907: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6908: wsh_util_core.add_message(x_return_status);
6909: l_num_warn := l_num_warn + 1;
6910: END IF;
6987: -- Update the delivery record with status code and confirm date
6988: -- 2052673 - modified the way in which the number_of_lpn is being updated.
6989: -- 2335270 included last_updated_by and last_update_login
6990: FORALL i IN 1..u_del_rows.count
6991: UPDATE wsh_new_deliveries
6992: SET status_code = u_status_code(i),
6993: confirm_date = sysdate,
6994: confirmed_by = nvl(confirmed_by, l_user_name),
6995: -- number_of_lpn = decode(u_number_of_lpn(i),0,null,u_number_of_lpn(i)) , --Bug 1729723
6999: --OTM R12, when setting delivery to closed, set the tms flag
7000: TMS_INTERFACE_FLAG = DECODE(l_gc3_is_installed,
7001: 'Y', DECODE(NVL(u_status_code(i), 'XXXXX'),
7002: 'CL', DECODE(NVL(tms_interface_flag,
7003: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7004: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7005: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7006: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7007: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7000: TMS_INTERFACE_FLAG = DECODE(l_gc3_is_installed,
7001: 'Y', DECODE(NVL(u_status_code(i), 'XXXXX'),
7002: 'CL', DECODE(NVL(tms_interface_flag,
7003: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7004: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7005: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7006: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7007: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7008: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7001: 'Y', DECODE(NVL(u_status_code(i), 'XXXXX'),
7002: 'CL', DECODE(NVL(tms_interface_flag,
7003: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7004: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7005: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7006: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7007: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7008: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7009: NVL(tms_interface_flag,
7002: 'CL', DECODE(NVL(tms_interface_flag,
7003: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7004: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7005: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7006: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7007: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7008: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7009: NVL(tms_interface_flag,
7010: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7003: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
7004: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7005: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7006: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7007: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7008: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7009: NVL(tms_interface_flag,
7010: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7011: NVL(tms_interface_flag,
7004: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7005: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
7006: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7007: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7008: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7009: NVL(tms_interface_flag,
7010: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7011: NVL(tms_interface_flag,
7012: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7006: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7007: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
7008: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7009: NVL(tms_interface_flag,
7010: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7011: NVL(tms_interface_flag,
7012: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7013: --END OTM R12
7014: last_update_date = SYSDATE ,
7008: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
7009: NVL(tms_interface_flag,
7010: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7011: NVL(tms_interface_flag,
7012: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
7013: --END OTM R12
7014: last_update_date = SYSDATE ,
7015: last_updated_by = l_user_id,
7016: last_update_login = l_login_id,
7098: aname => 'ACTUAL_DATE',
7099: avalue => P_ACTUAL_DEP_DATE);
7100:
7101: FND_MESSAGE.SET_NAME('WSH','WSH_WF_SCPOD_LAUNCHED');
7102: FND_MESSAGE.Set_Token('DEL_NAME',wsh_new_deliveries_pvt.get_name(u_del_rows(l_ctr)));
7103: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS,l_module_name);
7104:
7105: END IF;
7106: END LOOP;
7152: --
7153: -- Debug Statements
7154: --
7155: IF l_debug_on THEN
7156: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7157: END IF;
7158: --
7159: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
7160: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7155: IF l_debug_on THEN
7156: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7157: END IF;
7158: --
7159: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
7160: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7161: wsh_util_core.add_message(x_return_status);
7162: l_num_warn := l_num_warn + 1;
7163: END IF;
7341: --
7342: -- Debug Statements
7343: --
7344: IF l_debug_on THEN
7345: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7346: END IF;
7347: --
7348: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
7349: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7344: IF l_debug_on THEN
7345: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7346: END IF;
7347: --
7348: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
7349: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7350: wsh_util_core.add_message(x_return_status);
7351: WSH_UTIL_CORE.Store_Msg_In_Table (p_store_flag => FALSE,
7352: x_msg_rec_count => l_msg_rec_count,
7706: BEGIN
7707:
7708: SELECT organization_id
7709: INTO l_organization_id
7710: FROM wsh_new_deliveries
7711: WHERE delivery_id = p_in_rec.delivery_id;
7712:
7713: EXCEPTION
7714: WHEN OTHERS THEN
7733: END IF;
7734:
7735: END IF; --}
7736:
7737: UPDATE wsh_new_deliveries
7738: SET initial_pickup_date = p_in_rec.actual_date,
7739: ultimate_dropoff_date = GREATEST(
7740: NVL(ultimate_dropoff_date,p_in_rec.actual_date),
7741: p_in_rec.actual_date
8005: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED; -- this is actual function
8006: END IF;
8007: --
8008: --
8009: UPDATE wsh_new_deliveries
8010: SET ultimate_dropoff_date = GREATEST(
8011: nvl(ultimate_dropoff_date,p_in_rec.actual_date),
8012: p_in_rec.actual_date
8013: ),
8014: status_code = 'CL',
8015: --OTM R12, when setting delivery to closed, set tms flag
8016: tms_interface_flag = DECODE(l_gc3_is_installed,
8017: 'Y', DECODE(NVL(tms_interface_flag,
8018: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
8019: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8020: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8021: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8022: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8015: --OTM R12, when setting delivery to closed, set tms flag
8016: tms_interface_flag = DECODE(l_gc3_is_installed,
8017: 'Y', DECODE(NVL(tms_interface_flag,
8018: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
8019: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8020: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8021: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8022: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8023: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
8016: tms_interface_flag = DECODE(l_gc3_is_installed,
8017: 'Y', DECODE(NVL(tms_interface_flag,
8018: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
8019: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8020: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8021: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8022: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8023: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
8024: NVL(tms_interface_flag,
8017: 'Y', DECODE(NVL(tms_interface_flag,
8018: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
8019: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8020: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8021: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8022: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8023: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
8024: NVL(tms_interface_flag,
8025: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
8018: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
8019: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8020: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8021: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8022: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8023: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
8024: NVL(tms_interface_flag,
8025: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
8026: --
8019: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8020: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
8021: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8022: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8023: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
8024: NVL(tms_interface_flag,
8025: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
8026: --
8027: last_update_date = SYSDATE ,
8021: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8022: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
8023: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
8024: NVL(tms_interface_flag,
8025: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
8026: --
8027: last_update_date = SYSDATE ,
8028: last_updated_by = l_user_id,
8029: last_update_login = l_login_id
8228: p_caller IN VARCHAR2) IS
8229:
8230: CURSOR del_status (l_delivery_id NUMBER) IS
8231: SELECT status_code, name,organization_id
8232: FROM wsh_new_deliveries
8233: WHERE delivery_id = l_delivery_id;
8234:
8235: CURSOR delivery_leg_stops(l_delivery_id NUMBER) IS
8236: SELECT dg.pick_up_stop_id,
8594: wsh_debug_sv.log (l_module_name, 'l_status_code',l_status_code);
8595: END IF;
8596:
8597:
8598: update wsh_new_deliveries
8599: set status_code = l_status_code,
8600: confirm_date = null,--6453740
8601: confirmed_by = null,--6453740
8602: last_update_date = SYSDATE ,
8615: -- Reopen the consol delivery as well if it exists and is not open
8616: IF l_status_code = 'OP' THEN
8617:
8618: -- bug 4891897, sql 15037864
8619: -- removed wsh_new_deliveries d2
8620:
8621: update wsh_new_deliveries d1
8622: set d1.status_code = l_status_code,
8623: d1.last_update_date = SYSDATE,
8617:
8618: -- bug 4891897, sql 15037864
8619: -- removed wsh_new_deliveries d2
8620:
8621: update wsh_new_deliveries d1
8622: set d1.status_code = l_status_code,
8623: d1.last_update_date = SYSDATE,
8624: d1.last_updated_by = l_user_id,
8625: d1.last_update_login = l_login_id
8762: -- Following is old procedure as of I, which is replaced by proc. above in J
8763: -- remove after ut
8764:
8765: /* bug 2335270 Included last_update_date,last_updated_by and last_update_login
8766: in wsh_new_deliveries and wsh_delivery_details */
8767:
8768: -- J-IB-HEALI---{
8769: PROCEDURE Complete_Leg_Sequence
8770: ( p_delivery_id IN NUMBER,
8776:
8777: CURSOR delivery_info IS
8778: SELECT initial_pickup_location_id,
8779: ultimate_dropoff_location_id
8780: FROM wsh_new_deliveries
8781: WHERE delivery_id = p_delivery_id;
8782:
8783: CURSOR count_legs IS
8784: SELECT count(*)
8921: CLOSE pickup_delivery_legs;
8922:
8923: IF (p_insert_msg) THEN
8924: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTIPLE_LEGS');
8925: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8926: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8927: END IF;
8928:
8929: IF l_debug_on THEN
8940:
8941: IF (l_count > 1) THEN
8942: IF (p_insert_msg) THEN
8943: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTIPLE_LEGS');
8944: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8945: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8946: END IF;
8947:
8948: IF l_debug_on THEN
8960: IF (l_seq_num <> l_leg_count) THEN
8961:
8962: IF (p_insert_msg) THEN
8963: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_INVALID_FLOW');
8964: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8965: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8966: END IF;
8967: --
8968: IF l_debug_on THEN
8977: x_leg_complete := 'NW';
8978:
8979: IF (p_insert_msg) THEN
8980: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_NO_ULTIMATE_DROPOFF');
8981: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
8982: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8983: END IF;
8984: RETURN;
8985: END IF;
9135: END IF;
9136:
9137:
9138: IF (p_update_del_flag='Y') THEN
9139: UPDATE wsh_new_deliveries
9140: SET ITINERARY_COMPLETE = l_update_flag,
9141: last_update_date = sysdate,
9142: last_updated_by = FND_GLOBAL.USER_ID
9143: WHERE delivery_id= p_delivery_id;
9181: x_return_status OUT NOCOPY VARCHAR2) IS
9182:
9183: CURSOR loading_order (l_delivery_id NUMBER) IS
9184: SELECT loading_order_flag
9185: FROM wsh_new_deliveries
9186: WHERE delivery_id = l_delivery_id;
9187:
9188: -- Bug 1421549: Changed the logic for load sequencing
9189: cont_str VARCHAR2(1000) :=
9275: --
9276: -- Debug Statements
9277: --
9278: IF l_debug_on THEN
9279: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9280: END IF;
9281: --
9282: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9283: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9278: IF l_debug_on THEN
9279: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9280: END IF;
9281: --
9282: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9283: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9284: wsh_util_core.add_message(x_return_status);
9285: l_num_error := l_num_error + 1;
9286: goto loop_end;
9348: --
9349: -- Debug Statements
9350: --
9351: IF l_debug_on THEN
9352: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9353: END IF;
9354: --
9355: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9356: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9351: IF l_debug_on THEN
9352: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9353: END IF;
9354: --
9355: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9356: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9357: wsh_util_core.add_message(x_return_status);
9358: l_num_error := l_num_error + 1;
9359: EXIT;
9370: --
9371: -- Debug Statements
9372: --
9373: IF l_debug_on THEN
9374: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9375: END IF;
9376: --
9377: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9378: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9373: IF l_debug_on THEN
9374: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
9375: END IF;
9376: --
9377: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
9378: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9379: wsh_util_core.add_message(x_return_status);
9380: l_num_error := l_num_error + 1;
9381: END IF;
9448: (p_delivery_id IN NUMBER,
9449: p_del_params IN wsh_delivery_autocreate.grp_attr_rec_type,
9450: x_return_status OUT NOCOPY VARCHAR2) IS
9451:
9452: l_del_info wsh_new_deliveries_pvt.delivery_rec_type;
9453:
9454: --
9455: l_debug_on BOOLEAN;
9456: --
9478: --
9479: -- Debug Statements
9480: --
9481: IF l_debug_on THEN
9482: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
9483: END IF;
9484: --
9485: wsh_new_deliveries_pvt.populate_record( p_delivery_id, l_del_info, x_return_status);
9486:
9481: IF l_debug_on THEN
9482: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
9483: END IF;
9484: --
9485: wsh_new_deliveries_pvt.populate_record( p_delivery_id, l_del_info, x_return_status);
9486:
9487: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
9488: --
9489: -- Debug Statements
9520: --
9521: -- Debug Statements
9522: --
9523: IF l_debug_on THEN
9524: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
9525: END IF;
9526: --
9527: wsh_new_deliveries_pvt.update_delivery( null, l_del_info, x_return_status);
9528:
9523: IF l_debug_on THEN
9524: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
9525: END IF;
9526: --
9527: wsh_new_deliveries_pvt.update_delivery( null, l_del_info, x_return_status);
9528:
9529: --}
9530: END IF;
9531:
9587: CURSOR dlvy_csr (p_delivery_id IN NUMBER)
9588: IS
9589: SELECT freight_terms_code, name,
9590: nvl(shipment_direction,'O') shipment_direction
9591: FROM wsh_new_deliveries
9592: WHERE delivery_id = p_delivery_id;
9593: --
9594: --
9595: -- Lock delivery
9596: --
9597: CURSOR lock_dlvy_csr (p_delivery_id IN NUMBER)
9598: IS
9599: SELECT 1
9600: FROM wsh_new_deliveries
9601: WHERE delivery_id = p_delivery_id
9602: FOR UPDATE OF freight_terms_code NOWAIT;
9603: --
9604: --
9860: --tms_interface_flag and tms_version_number should already be
9861: --reflecting the change in delivery,
9862: --no need to set them again.
9863:
9864: UPDATE wsh_new_deliveries
9865: SET freight_terms_code = l_dlvy_freight_terms_code,
9866: last_update_date = SYSDATE,
9867: last_updated_by = FND_GLOBAL.USER_ID,
9868: last_update_login = FND_GLOBAL.LOGIN_ID
9899: IF l_debug_on THEN
9900: wsh_debug_sv.LOGMSG(l_module_name, 'Updating delivery WHO columns ' );
9901: END IF;
9902: --
9903: UPDATE wsh_new_deliveries
9904: SET last_update_date = SYSDATE,
9905: last_updated_by = FND_GLOBAL.USER_ID,
9906: last_update_login = FND_GLOBAL.LOGIN_ID
9907: WHERE delivery_id = p_delivery_id;
9986: CURSOR dlvy_csr (p_delivery_id IN NUMBER)
9987: IS
9988: SELECT wnd.name,
9989: initial_pickup_location_id
9990: FROM wsh_new_deliveries wnd
9991: WHERE delivery_id = p_delivery_id;
9992: --
9993: l_dlvy_rec dlvy_csr%ROWTYPE;
9994: --
10013: CURSOR pickup_dlvy_csr (p_delivery_id IN NUMBER,p_stop_id IN NUMBER)
10014: IS
10015: SELECT wnd.delivery_id, wnd.name
10016: FROM wsh_delivery_legs wdl,
10017: wsh_new_deliveries wnd
10018: WHERE pick_up_stop_id = p_stop_id
10019: AND wdl.delivery_id <> p_delivery_id
10020: AND wdl.delivery_id = wnd.delivery_id;
10021: --
10102: FETCH stop_csr INTO stop_rec;
10103: CLOSE stop_csr;
10104: --
10105: /*
10106: UPDATE WSH_NEW_DELIVERIES
10107: SET INITIAL_PICKUP_LOCATION_ID = p_location_id,
10108: last_update_date = SYSDATE,
10109: last_updated_by = FND_GLOBAL.USER_ID,
10110: last_update_login = FND_GLOBAL.LOGIN_ID
10242: LOOP
10243: --{
10244: --OTM R12, this update is called by inbound so we
10245: --will not check tms flag here
10246: UPDATE WSH_NEW_DELIVERIES
10247: SET INITIAL_PICKUP_LOCATION_ID = p_location_id,
10248: last_update_date = SYSDATE,
10249: last_updated_by = FND_GLOBAL.USER_ID,
10250: last_update_login = FND_GLOBAL.LOGIN_ID
10373: CURSOR Check_Last_Trip (l_delivery_id NUMBER) IS
10374: SELECT s1.trip_id
10375: FROM wsh_trip_stops s1,
10376: wsh_delivery_legs dl1,
10377: wsh_new_deliveries d1,
10378: wsh_trip_stops s2,
10379: wsh_delivery_legs dl2
10380: WHERE d1.delivery_id <> l_delivery_id
10381: AND s1.stop_id = dl1.pick_up_stop_id
10489: -- count('CONSOLIDATION') should be 1
10490:
10491: CURSOR check_shared_trip_consol(x_delivery_leg_id NUMBER) IS
10492: SELECT count(*), min(s.trip_id)
10493: FROM wsh_new_deliveries n, wsh_delivery_legs d, wsh_trip_stops s
10494: WHERE n.delivery_id = d.delivery_id
10495: AND d.pick_up_stop_id = s.stop_id
10496: AND s.trip_id = (SELECT s1.trip_id
10497: FROM wsh_trip_stops s1, wsh_delivery_legs d1
11252: -- p_smc_tab IN WSH_NEW_DELIVERY_ACTIONS.TableVarchar30 --
11253: -- p_org_del_id_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers --
11254: -- p_trip_id_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers --
11255: -- --
11256: -- PARAMETERS (OUT): x_rec_attr_tab OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type --
11257: -- x_trip_info_tab OUT NOCOPY WSH_TRIPS_PVT.Trip_Attr_Tbl_Type
11258: --
11259: --
11260: -- RETURN: none --
11283:
11284: PROCEDURE FORMAT_DEL_UPDATE_TABLE(--p_cs_result_tab IN FTE_ACS_PKG.FTE_CS_RESULT_TAB_TYPE,
11285: p_cs_result_tab IN WSH_FTE_INTEGRATION.WSH_CS_RESULT_TAB_TYPE,
11286: p_caller IN VARCHAR2 DEFAULT NULL,
11287: x_rec_attr_tab OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
11288: x_trip_info_tab OUT NOCOPY WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
11289: x_return_message OUT NOCOPY VARCHAR2,
11290: x_return_status OUT NOCOPY VARCHAR2) IS
11291:
11744: fob_code,
11745: shipment_direction,
11746: delivery_type,
11747: NULL -- l_trip_id
11748: from wsh_new_deliveries
11749: where delivery_id = x_delivery_id
11750: and ship_method_code is null
11751: AND initial_pickup_location_id <> WSH_UTIL_CORE.C_NULL_SF_LOCN_ID -- J-IB-NPARIKH
11752: and planned_flag NOT IN ('Y','F');
11775: fob_code,
11776: shipment_direction,
11777: delivery_type,
11778: NULL -- l_trip_id
11779: from wsh_new_deliveries
11780: where batch_id = x_batch_id
11781: and organization_id = x_organization_id
11782: and ship_method_code is null
11783: and planned_flag NOT IN ('Y','F');
11804: fob_code,
11805: shipment_direction,
11806: delivery_type,
11807: NULL -- l_trip_id
11808: from wsh_new_deliveries
11809: where delivery_id = f_delivery_id;
11810:
11811: CURSOR check_del_assigned(p_del_id IN NUMBER) IS
11812: select wts.trip_id,
11821: -- AG not required
11822: /*
11823: CURSOR get_org_for_delivery(p_del_id IN NUMBER) IS
11824: select wnd.organization_id
11825: from wsh_new_deliveries wnd
11826: where wnd.delivery_id = p_del_id;
11827: */
11828:
11829:
11851: --
11852:
11853: l_in_rec WSH_DELIVERIES_GRP.Del_In_Rec_Type;
11854: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
11855: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
11856: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
11857: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
11858: l_del_out_rec_tab WSH_DELIVERIES_GRP.Del_Out_Tbl_Type;
11859: l_msg_count NUMBER;
12966: -- x_return_status: status
12967: **/
12968:
12969: Procedure Assign_Del_to_Consol_Del(
12970: p_del_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
12971: p_parent_del_id IN NUMBER,
12972: p_caller IN VARCHAR2,
12973: x_return_status OUT NOCOPY VARCHAR2) IS
12974:
12984: s1.planned_arrival_date pu_ar_date,
12985: s1.planned_departure_date pu_dep_date,
12986: s2.planned_arrival_date do_ar_date,
12987: s2.planned_departure_date do_dep_date
12988: from wsh_trip_stops s1, wsh_trip_stops s2, wsh_delivery_legs l, wsh_new_deliveries d, wsh_trips t
12989: where s1.stop_id = l.pick_up_stop_id
12990: and s2.stop_id = l.drop_off_stop_id
12991: and l.delivery_id = d.delivery_id
12992: and d.delivery_id = p_parent_del_id
12998: -- make sure that the delivery is open, STANDARD and
12999: -- is not assigned to a parent delivery.
13000: cursor c_check_valid_child(p_delivery_id in number) is
13001: select d.delivery_id
13002: from wsh_new_deliveries d
13003: where d.delivery_id = p_delivery_id
13004: and d.status_code = 'OP'
13005: and d.delivery_type = 'STANDARD'
13006: and not exists (select 1 from wsh_delivery_legs
13015: */
13016: -- check if the trip the delivery is assigned to is valid
13017: cursor c_check_trips(p_delivery_id in number) is
13018: select l.delivery_leg_id, s1.trip_id
13019: from wsh_delivery_legs l, wsh_trip_stops s1, wsh_new_deliveries d
13020: where l.delivery_id = p_delivery_id
13021: and d.delivery_id = l.delivery_id
13022: and d.initial_pickup_location_id = s1.stop_location_id
13023: and s1.stop_id = l.pick_up_stop_id;
13273: EXCEPTION
13274: WHEN OTHERS THEN
13275: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13276: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
13277: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_valid_children_tab(i)));
13278: WSH_UTIL_CORE.Add_Message(x_return_status);
13279: --
13280: -- Debug Statements
13281: --
13495: EXCEPTION
13496: WHEN OTHERS THEN
13497: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13498: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
13499: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_valid_children_tab(i)));
13500: WSH_UTIL_CORE.Add_Message(x_return_status);
13501: --
13502: -- Debug Statements
13503: --
13801: x_return_status OUT NOCOPY VARCHAR2) IS
13802:
13803: CURSOR c_check_parent_exists(p_child_delivery_id in number, p_parent_delivery_id in number) IS
13804: select l1.delivery_id, l2.delivery_id, l1.delivery_leg_id, s.trip_id
13805: from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d, wsh_trip_stops s
13806: where l1.parent_delivery_leg_id = l2.delivery_leg_id
13807: and l1.delivery_id = p_child_delivery_id
13808: and l2.delivery_id = NVL(p_parent_delivery_id, l2.delivery_id)
13809: and d.delivery_id = l1.delivery_id
13819: and rownum = 1;
13820:
13821: CURSOR c_get_children(p_parent_delivery_id IN NUMBER) is
13822: select l1.delivery_id, l1.delivery_leg_id, s.trip_id
13823: from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d, wsh_trip_stops s
13824: where l1.parent_delivery_leg_id = l2.delivery_leg_id
13825: and l2.delivery_id = p_parent_delivery_id
13826: and d.delivery_id = l1.delivery_id
13827: and d.status_code = 'OP'
13951: FETCH check_consol_lpns INTO l_consol_lpn_dummy;
13952: IF check_consol_lpns%FOUND THEN
13953: CLOSE check_consol_lpns;
13954: FND_MESSAGE.SET_NAME('WSH','WSH_FAIL_UNASSIGN_DEL_LPN');
13955: FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_assigned_dels_tab(i)));
13956: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13957: wsh_util_core.add_message(l_return_status, l_module_name);
13958: wsh_util_core.api_post_call
13959: (
13988: EXCEPTION
13989: WHEN OTHERS THEN
13990: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13991: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
13992: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_assigned_dels_tab(i)));
13993: WSH_UTIL_CORE.Add_Message(x_return_status);
13994: --
13995: -- Debug Statements
13996: --
14058: EXCEPTION
14059: WHEN OTHERS THEN
14060: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
14061: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_DEL_LEG_LOCK');
14062: FND_MESSAGE.Set_Token('DEL_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_assigned_dels_tab(i)));
14063: WSH_UTIL_CORE.Add_Message(x_return_status);
14064: --
14065: -- Debug Statements
14066: --
14268: x_return_status OUT NOCOPY VARCHAR2) IS
14269:
14270: cursor c_check_close_consol(p_del_id IN NUMBER) IS
14271: select d.delivery_id
14272: from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d
14273: where l1.parent_delivery_leg_id = l2.delivery_leg_id
14274: and l2.delivery_id = p_del_id
14275: and l1.delivery_id = d.delivery_id
14276: and d.status_code = 'OP'
14306:
14307: -- bug 4505105
14308: cursor l_get_consol_del_org_csr (p_del_id IN NUMBER) is
14309: select organization_id
14310: from wsh_new_deliveries
14311: where delivery_id = p_del_id;
14312:
14313: l_return_status VARCHAR2(1);
14314: l_del_valid_flag VARCHAR2(1);
14461: IF l_debug_on THEN
14462: WSH_DEBUG_SV.log(l_module_name,'l_co_consol_del_tab(l)', l_co_consol_del_tab(l));
14463: END IF;
14464: l_lock_index := l;
14465: WSH_NEW_DELIVERIES_PVT.Lock_Dlvy_No_Compare(l_co_consol_del_tab(l));
14466:
14467: l_lpn_number := 0;
14468: OPEN c_get_child_lpn_number(l_co_consol_del_tab(l));
14469: FETCH c_get_child_lpn_number
14484: EXCEPTION
14485: WHEN OTHERS THEN
14486: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
14487: FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_LOCK_FAILED');
14488: FND_MESSAGE.Set_Token('ENTITY_NAME', WSH_NEW_DELIVERIES_PVT.Get_Name(l_co_consol_del_tab(l_lock_index)));
14489: WSH_UTIL_CORE.Add_Message(x_return_status);
14490: --
14491: -- Debug Statements
14492: --
14501: WSH_DEBUG_SV.logmsg(l_module_name,'after lock');
14502: END IF;
14503: IF l_co_consol_del_tab.count > 0 THEN
14504: FORALL m in 1..l_co_consol_del_tab.count
14505: UPDATE wsh_new_deliveries
14506: SET status_code = 'CO',
14507: number_of_lpn = decode(number_of_lpn, NULL, l_number_of_lpn_tab(m), number_of_lpn)
14508: WHERE delivery_id = l_co_consol_del_tab(m);
14509: END IF;
14549: child.carrier_id,
14550: child.service_level,
14551: child.mode_of_transport,
14552: child.ignore_for_planning
14553: FROM wsh_new_deliveries child,
14554: wsh_new_deliveries parent,
14555: wsh_delivery_legs child_leg,
14556: wsh_delivery_legs parent_leg,
14557: wsh_trip_stops stop
14550: child.service_level,
14551: child.mode_of_transport,
14552: child.ignore_for_planning
14553: FROM wsh_new_deliveries child,
14554: wsh_new_deliveries parent,
14555: wsh_delivery_legs child_leg,
14556: wsh_delivery_legs parent_leg,
14557: wsh_trip_stops stop
14558: WHERE child_leg.delivery_id = p_delivery_id