[Home] [Help]
1820: IS
1821:
1822: CURSOR del_cur(c_dlvy_id NUMBER) IS
1823: SELECT ship_method_code, intmed_ship_to_location_id
1824: FROM wsh_new_deliveries
1825: WHERE delivery_id = c_dlvy_id;
1826:
1827: l_del_details_tbl WSH_UTIL_CORE.Id_Tab_Type;
1828: l_grouping_tbl WSH_UTIL_CORE.Id_Tab_Type;
1857: l_cc_return_status VARCHAR2(1);
1858: l_cc_count_del_rows NUMBER;
1859: l_cc_count_grouping_rows NUMBER;
1860: --dummy tables for calling validate_constraint_main
1861: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1862: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
1863: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
1864: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
1865: l_cc_in_ids WSH_UTIL_CORE.Id_Tab_Type;
2081: FOR delcurtemp in del_cur(l_delivery_ids_tbl(i)) LOOP
2082: l_cc_dlvy_intmed_ship_to := delcurtemp.INTMED_SHIP_TO_LOCATION_ID;
2083: l_cc_dlvy_ship_method := delcurtemp.SHIP_METHOD_CODE;
2084: IF l_cc_upd_dlvy_intmed_ship_to = 'N' and l_cc_dlvy_intmed_ship_to IS NOT NULL THEN
2085: UPDATE wsh_new_deliveries
2086: SET intmed_ship_to_location_id = NULL
2087: WHERE delivery_id = l_delivery_ids_tbl(i);
2088: END IF;
2089: --IF l_cc_upd_dlvy_ship_method = 'N' and l_cc_dlvy_ship_method IS NOT NULL THEN
2090: IF l_cc_upd_dlvy_ship_method = 'N' THEN
2091:
2092: --OTM R12, this update is FTE part of code, OTM flow will not go through here
2093:
2094: UPDATE wsh_new_deliveries
2095: SET ship_method_code = NULL,
2096: carrier_id = NULL,
2097: mode_of_transport = NULL,
2098: service_level = NULL
2232: FOR delcurtemp in del_cur(l_delivery_ids_tbl(i)) LOOP
2233: l_cc_dlvy_intmed_ship_to := delcurtemp.INTMED_SHIP_TO_LOCATION_ID;
2234: l_cc_dlvy_ship_method := delcurtemp.SHIP_METHOD_CODE;
2235: IF l_cc_upd_dlvy_intmed_ship_to = 'N' and l_cc_dlvy_intmed_ship_to IS NOT NULL THEN
2236: UPDATE wsh_new_deliveries
2237: SET intmed_ship_to_location_id = NULL
2238: WHERE delivery_id = l_delivery_ids_tbl(i);
2239: END IF;
2240: --IF l_cc_upd_dlvy_ship_method = 'N' and l_cc_dlvy_ship_method IS NOT NULL THEN
2241: IF l_cc_upd_dlvy_ship_method = 'N' THEN
2242:
2243: --OTM R12, this update is FTE part of code, OTM flow will not go through here
2244:
2245: UPDATE wsh_new_deliveries
2246: SET ship_method_code = NULL,
2247: carrier_id = NULL,
2248: mode_of_transport = NULL,
2249: service_level = NULL
3630: v_FromReqDate IN DATE,
3631: v_ToReqDate IN DATE
3632: ) IS
3633: SELECT DISTINCT WND.ORGANIZATION_ID
3634: FROM WSH_NEW_DELIVERIES WND,
3635: WSH_DELIVERY_LEGS WLG,
3636: WSH_TRIP_STOPS WTS
3637: WHERE WTS.TRIP_ID = v_trip_id
3638: AND WTS.STOP_ID = WLG.PICK_UP_STOP_ID
3652: AND wl.source_location_id =
3653: hr.location_id))
3654: UNION ALL
3655: SELECT DISTINCT WND.ORGANIZATION_ID
3656: FROM WSH_NEW_DELIVERIES WND,
3657: WSH_DELIVERY_LEGS WLG,
3658: WSH_TRIP_STOPS WTS
3659: WHERE WTS.STOP_ID = WLG.PICK_UP_STOP_ID
3660: AND WLG.DELIVERY_ID = WND.DELIVERY_ID
3665: -- Bug 11669468: Included -2 in IN clause
3666: AND v_del_detail_id in (0,-1,-2)
3667: UNION ALL
3668: SELECT WND.ORGANIZATION_ID
3669: FROM WSH_NEW_DELIVERIES WND
3670: WHERE WND.DELIVERY_ID = v_delivery_id
3671: AND nvl(WND.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
3672: UNION ALL
3673: SELECT DISTINCT ORGANIZATION_ID
3737: wdd.line_direction, wdd.gross_weight, wdd.net_weight, wdd.volume,
3738: wnd.planned_flag, wnd.batch_id
3739: FROM wsh_delivery_details wdd,
3740: wsh_delivery_assignments_v wda,
3741: wsh_new_deliveries wnd
3742: WHERE wdd.batch_id = l_batch_id
3743: AND wdd.delivery_detail_id = wda.delivery_detail_id
3744: AND wdd.released_status = 'B'
3745: AND wdd.replenishment_status IS NULL -- replenishment2, select only back order delivery lines.
3801:
3802: CURSOR get_pick_up_stops (c_sc_batch_id NUMBER) IS
3803: SELECT DISTINCT wtp.trip_id, wst.stop_sequence_number,
3804: wst.stop_id, wst.stop_location_id
3805: FROM wsh_new_deliveries wnd,
3806: wsh_delivery_legs wlg,
3807: wsh_trip_stops wst,
3808: wsh_trips wtp
3809: WHERE wnd.delivery_id = wlg.delivery_id AND
3823:
3824: CURSOR get_all_stops (c_sc_batch_id NUMBER) IS
3825: SELECT wtp.trip_id, wst.stop_sequence_number, wst.stop_id ,
3826: wst.stop_location_id
3827: FROM wsh_new_deliveries wnd,
3828: wsh_delivery_legs wlg,
3829: wsh_trip_stops wst,
3830: wsh_trips wtp
3831: WHERE wnd.delivery_id = wlg.delivery_id and
3842: we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL')
3843: UNION (
3844: SELECT wtp2.trip_id, wst2.stop_sequence_number,
3845: wst2.stop_id, wst2.stop_location_id
3846: FROM wsh_new_deliveries wnd2,
3847: wsh_delivery_legs wlg2,
3848: wsh_trip_stops wst2,
3849: wsh_trips wtp2
3850: WHERE wnd2.delivery_id = wlg2.delivery_id and
3865: CURSOR c_batch_stop(l_batch_id NUMBER) IS
3866: SELECT wts.stop_id
3867: FROM wsh_trip_stops wts,
3868: wsh_delivery_legs wdl,
3869: wsh_new_deliveries wnd,
3870: wsh_picking_batches wpb
3871: WHERE p_batch_id IS NOT NULL
3872: AND wnd.batch_id = l_batch_id
3873: AND wdl.delivery_id = wnd.delivery_id
3880: CURSOR c_batch_unplanned_del(l_batch_id NUMBER) IS
3881: SELECT DISTINCT wda.delivery_id
3882: FROM wsh_delivery_details wdd,
3883: wsh_delivery_assignments_v wda,
3884: wsh_new_deliveries wnd,
3885: wsh_shipping_parameters wsp
3886: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
3887: AND wdd.batch_id = l_batch_id
3888: AND wdd.released_status = 'Y'
3924:
3925:
3926: CURSOR c_dels_in_batch(l_batch_id NUMBER, p_organization_id NUMBER) IS
3927: SELECT delivery_id
3928: FROM wsh_new_deliveries
3929: WHERE batch_id = l_batch_id
3930: AND organization_id = p_organization_id;
3931:
3932: CURSOR c_get_location(p_delivery_id NUMBER) IS
3930: AND organization_id = p_organization_id;
3931:
3932: CURSOR c_get_location(p_delivery_id NUMBER) IS
3933: SELECT initial_pickup_location_id
3934: FROM wsh_new_deliveries
3935: WHERE delivery_id = p_delivery_id;
3936:
3937: CURSOR c_requests (p_parent_request_id NUMBER) IS
3938: SELECT request_id
5547: l_completion_status := 'WARNING';
5548: END IF;
5549:
5550: -- Reset delivery batch id.
5551: UPDATE wsh_new_deliveries
5552: SET batch_id = NULL
5553: WHERE batch_id = g_batch_id;
5554:
5555: --Bug 3433645 :Automated Shipping Failing for Order with Huge Delivery Lines
7127: where stop_id = c_stop_id;
7128:
7129: --Cursor to get delivery attributes.
7130: CURSOR check_delivery(c_delivery_id IN NUMBER) IS
7131: select status_code, organization_id from wsh_new_deliveries
7132: where delivery_id = c_delivery_id;
7133:
7134: CURSOR check_detail (c_delivery_detail_id IN NUMBER) IS
7135: select released_status from wsh_delivery_details
7397: ELSIF (p_delivery_ids.COUNT > 0) THEN
7398: l_index := p_delivery_ids.FIRST;
7399: l_delivery_id := p_delivery_ids(l_index);
7400: --
7401: l_name := WSH_NEW_DELIVERIES_PVT.Get_Name(l_delivery_id);
7402: l_type := 'Delivery';
7403: ELSIF (p_detail_ids.COUNT > 0) THEN
7404: l_index := p_detail_ids.FIRST;
7405: l_detail_id := p_detail_ids(l_index);
7889: l_index := p_delivery_ids.NEXT(l_index);
7890: l_delivery_id := p_delivery_ids(l_index);
7891: --
7892: IF l_debug_on THEN
7893: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7894: END IF;
7895: --
7896: l_name := WSH_NEW_DELIVERIES_PVT.Get_Name(l_delivery_id);
7897: ELSE
7892: IF l_debug_on THEN
7893: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
7894: END IF;
7895: --
7896: l_name := WSH_NEW_DELIVERIES_PVT.Get_Name(l_delivery_id);
7897: ELSE
7898: EXIT; -- Only one loop when launching pick release for delivery details.
7899: END IF;
7900: