[Home] [Help]
826: --Cursor to get the Trip stop id associated with the PickUp Stop of delivery detail line.
827: CURSOR get_dd_pup_trip_stop(v_org_id IN NUMBER, v_dd_id NUMBER) IS
828: SELECT wts.STOP_ID
829: FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_assignments_v wda,
830: wsh_delivery_legs wdl, wsh_delivery_details wdd
831: WHERE wdd.delivery_detail_id = v_dd_id
832: AND wdd.organization_id = v_org_id
833: AND wda.delivery_detail_id = wdd.delivery_detail_id
834: AND wdl.delivery_id = wda.delivery_id
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
3639: AND WLG.DELIVERY_ID = WND.DELIVERY_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
3661: AND WTS.STOP_ID = v_trip_stop_id
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
3810: wlg.pick_up_stop_id = wst.stop_id AND
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
3832: wlg.pick_up_stop_id = wst.stop_id and
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
3851: wlg2.drop_off_stop_id = wst2.stop_id and
3864:
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