[Home] [Help]
47: CURSOR deliveries_exist IS
48: SELECT dl.delivery_id
49: FROM wsh_trips t,
50: wsh_trip_stops st,
51: wsh_delivery_legs dg,
52: wsh_new_deliveries dl
53: WHERE t.trip_id = p_trip_id AND
54: st.trip_id = t.trip_id AND
55: dg.pick_up_stop_id = st.stop_id AND
382: CURSOR prev_stop_info IS
383: SELECT 1 from dual
384: WHERE exists ( select 1
385: FROM wsh_trip_stops ds,
386: wsh_delivery_legs dg,
387: wsh_new_deliveries dl
388: WHERE ds.trip_id = p_trip_id
389: AND dg.drop_off_stop_id = p_stop_id
390: AND dg.delivery_id = dl.delivery_id
967:
968: CURSOR group_by_carrier_set IS
969: SELECT count(*)
970: FROM wsh_trip_stops t,
971: wsh_delivery_legs dg,
972: wsh_new_deliveries dl,
973: wsh_shipping_parameters wsp
974: WHERE t.stop_id = p_trip_id AND
975: dg.pick_up_stop_id = t.stop_id AND
1228: -- bug 1550824: find delivery not confirmed, in transit or closed.
1229: CURSOR deliveries_still_open(p_stop_id NUMBER) IS
1230: SELECT wnd.delivery_id
1231: FROM wsh_new_deliveries wnd,
1232: wsh_delivery_legs wdl
1233: WHERE wdl.pick_up_stop_id = p_stop_id
1234: AND wnd.delivery_id = wdl.delivery_id
1235: AND wnd.status_code IN ('OP', 'PA')
1236: AND rownum = 1;
1423:
1424: CURSOR pickup_deliveries IS
1425: SELECT dg.delivery_id
1426: FROM wsh_trip_stops t,
1427: wsh_delivery_legs dg
1428: WHERE t.stop_id = p_stop_id AND
1429: dg.pick_up_stop_id = t.stop_id
1430: FOR UPDATE NOWAIT;
1431:
1431:
1432: CURSOR dropoff_deliveries IS
1433: SELECT dg.delivery_id
1434: FROM wsh_trip_stops t,
1435: wsh_delivery_legs dg
1436: WHERE t.stop_id = p_stop_id AND
1437: dg.drop_off_stop_id = t.stop_id
1438: FOR UPDATE NOWAIT;
1439:
2134: NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, -- OTM R12, glog proj
2135: NVL(wnd.tms_interface_flag,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) tms_interface_flag, -- OTM R12, glog proj
2136: wsp.otm_enabled -- OTM R12, glog proj
2137: FROM wsh_new_deliveries wnd,
2138: wsh_delivery_legs wdl,
2139: wsh_trip_stops wts1,
2140: wsh_trip_stops wts2,
2141: wsh_trips wt,
2142: wsh_shipping_parameters wsp -- OTM R12, glog proj
2597: )
2598: )
2599: ) THEN
2600: l_entity_ids(1):=p_trip_info.trip_id;
2601: WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
2602: p_entity_type => 'TRIP',
2603: p_entity_ids => l_entity_ids,
2604: p_consolidation_change => p_trip_info.consolidation_allowed,
2605: x_return_status => x_return_status);
4721: --
4722: CURSOR trip_csr (p_trip_id NUMBER, p_stop_id NUMBER)
4723: IS
4724: SELECT 1
4725: FROM wsh_delivery_legs wdl,
4726: wsh_new_deliveries wnd,
4727: wsh_trip_stops wts
4728: WHERE wts.trip_id = p_trip_id
4729: AND wdl.delivery_id = wnd.delivery_id
4831: --
4832: CURSOR trip_csr (p_trip_id NUMBER, p_stop_id NUMBER)
4833: IS
4834: SELECT 1
4835: FROM wsh_delivery_legs wdl,
4836: wsh_new_deliveries wnd,
4837: wsh_trip_stops wts
4838: WHERE wts.trip_id = p_trip_id
4839: AND wdl.delivery_id = wnd.delivery_id
5911: -- End of Bug 3782135
5912:
5913: CURSOR get_deliveries_to_unassign IS
5914: SELECT leg.delivery_id
5915: FROM wsh_delivery_legs leg,
5916: wsh_trip_stops pickup,
5917: wsh_trip_stops dropoff
5918: WHERE pickup.trip_id = p_trip_id
5919: AND pickup.trip_id = dropoff.trip_id