[Home] [Help]
46:
47:
48: cursor get_pickup_del is
49: select dg.delivery_id, st.stop_sequence_number,st.trip_id
50: from wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
51: where st.stop_id = p_stop_id
52: and dg.delivery_id = dl.delivery_id
53: and st.stop_location_id = dl.initial_pickup_location_id
54: and st.stop_id = dg.pick_up_stop_id
208: FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
209: IF l_exceptions_tab(exp_cnt).exception_behavior in ('ERROR', 'WARNING') THEN
210:
211: l_exc_exist := 'Y';
212: l_del_name := WSH_NEW_DELIVERIES_PVT.Get_Name(del_ids(i));
213: EXIT;
214:
215: END IF;
216: END LOOP;
303:
304: CURSOR stop_dropoffs (l_stop_id NUMBER) IS
305: SELECT dl.delivery_id
306: FROM wsh_delivery_legs dg,
307: wsh_new_deliveries dl
308: WHERE dg.delivery_id = dl.delivery_id AND
309: dg.drop_off_stop_id = l_stop_id AND
310: dl.status_code IN ('OP','PA', 'SA') -- sperera 940/945
311: AND nvl(dl.shipment_direction,'O') IN ('O','IO');
313: CURSOR stop_pickups (l_stop_id NUMBER) IS
314: SELECT 1 from dual
315: WHERE exists ( select 1
316: FROM wsh_delivery_legs dg,
317: wsh_new_deliveries dl
318: WHERE dg.delivery_id = dl.delivery_id
319: AND dg.pick_up_stop_id = l_stop_id
320: AND dl.status_code in ('OP','PA', 'SA') -- sperera 940/945
321: AND nvl(dl.shipment_direction,'O') IN ('O','IO'));
1571: l_wf_rs VARCHAR2(1); -- Workflow Project
1572:
1573: --OTM R12, glog proj
1574: l_last_pickup_stop_closed VARCHAR2(1);
1575: l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
1576: l_gc3_is_installed VARCHAR2(1);
1577: l_loop_counter NUMBER;
1578: --
1579:
1814: -- Bug 1783103 and 2699764 :Ship Method and carrier at Ship Confirm is not reflected at Delivery
1815: -- To update deliveries with the ship method and carrier entered for the trips
1816: --
1817: FORALL i IN l_dlvy_id_tbl.FIRST..l_dlvy_id_tbl.LAST
1818: UPDATE wsh_new_deliveries
1819: SET ship_method_code = p_in_rec1.ship_method_code,
1820: carrier_id = p_in_rec1.carrier_id,
1821: service_level = p_in_rec1.service_level,
1822: mode_of_transport = p_in_rec1.mode_of_transport,
2367: CURSOR ib_dropoff_stop_csr(p_pu_stop_id IN NUMBER, p_do_stop_id IN NUMBER)
2368: IS
2369: SELECT 1
2370: FROM wsh_delivery_legs wdl,
2371: wsh_new_deliveries wnd
2372: WHERE wdl.pick_up_stop_id = p_pu_stop_id
2373: AND wdl.drop_off_stop_id = p_do_stop_id
2374: AND wdl.delivery_id = wnd.delivery_id
2375: AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
2380: CURSOR ib_dlvy_csr(p_stop_id IN NUMBER)
2381: IS
2382: SELECT wnd.delivery_id, wts.stop_location_id, wnd.ultimate_dropoff_location_id
2383: FROM wsh_delivery_legs wdl,
2384: wsh_new_deliveries wnd,
2385: wsh_trip_stops wts
2386: WHERE wdl.drop_off_stop_id = p_stop_id
2387: AND wts.stop_id = p_stop_id
2388: AND wdl.delivery_id = wnd.delivery_id
2407: wsh_trip_stops next_leg_pu_stop,
2408: wsh_trip_stops curr_leg_do_stop,
2409: wsh_delivery_legs next_leg,
2410: wsh_delivery_legs curr_leg,
2411: wsh_new_deliveries wnd
2412: WHERE next_leg.drop_off_stop_id = next_leg_do_stop.stop_id
2413: --AND st1.status_code = 'OP'
2414: AND next_leg.pick_up_stop_id = next_leg_pu_stop.stop_id
2415: AND next_leg_pu_stop.stop_location_id = curr_leg_do_stop.stop_location_id
3204: CURSOR check_eligible_for_asr_csr IS
3205: SELECT 1
3206: FROM wsh_trip_stops wts
3207: ,wsh_delivery_legs wdl
3208: ,wsh_new_deliveries wnd
3209: WHERE wts.trip_id = p_trip_id
3210: AND wts.stop_id = wdl.pick_up_stop_id
3211: AND wdl.delivery_id = wnd.delivery_id
3212: AND EXISTS ( SELECT 1
3216: )
3217: AND NOT EXISTS ( SELECT 1
3218: FROM wsh_trip_stops wts
3219: ,wsh_delivery_legs wdl
3220: ,wsh_new_deliveries wnd
3221: WHERE wts.trip_id = p_trip_id
3222: AND wts.stop_id = wdl.pick_up_stop_id
3223: AND wdl.delivery_id = wnd.delivery_id
3224: AND ( EXISTS ( SELECT 1