[Home] [Help]
811: --Cursor to get the Trip stop id associated with the PickUp Stop of delivery detail line.
812: CURSOR get_dd_pup_trip_stop(v_org_id IN NUMBER, v_dd_id NUMBER) IS
813: SELECT wts.STOP_ID
814: FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_assignments_v wda,
815: wsh_delivery_legs wdl, wsh_delivery_details wdd
816: WHERE wdd.delivery_detail_id = v_dd_id
817: AND wdd.organization_id = v_org_id
818: AND wda.delivery_detail_id = wdd.delivery_detail_id
819: AND wdl.delivery_id = wda.delivery_id
3547: v_ToReqDate IN DATE
3548: ) IS
3549: SELECT DISTINCT WND.ORGANIZATION_ID
3550: FROM WSH_NEW_DELIVERIES WND,
3551: WSH_DELIVERY_LEGS WLG,
3552: WSH_TRIP_STOPS WTS
3553: WHERE WTS.TRIP_ID = v_trip_id
3554: AND WTS.STOP_ID = WLG.PICK_UP_STOP_ID
3555: AND WLG.DELIVERY_ID = WND.DELIVERY_ID
3568: hr.location_id))
3569: UNION ALL
3570: SELECT DISTINCT WND.ORGANIZATION_ID
3571: FROM WSH_NEW_DELIVERIES WND,
3572: WSH_DELIVERY_LEGS WLG,
3573: WSH_TRIP_STOPS WTS
3574: WHERE WTS.STOP_ID = WLG.PICK_UP_STOP_ID
3575: AND WLG.DELIVERY_ID = WND.DELIVERY_ID
3576: AND WTS.STOP_ID = v_trip_stop_id
3714: CURSOR get_pick_up_stops (c_sc_batch_id NUMBER) IS
3715: SELECT DISTINCT wtp.trip_id, wst.stop_sequence_number,
3716: wst.stop_id, wst.stop_location_id
3717: FROM wsh_new_deliveries wnd,
3718: wsh_delivery_legs wlg,
3719: wsh_trip_stops wst,
3720: wsh_trips wtp
3721: WHERE wnd.delivery_id = wlg.delivery_id AND
3722: wlg.pick_up_stop_id = wst.stop_id AND
3736: CURSOR get_all_stops (c_sc_batch_id NUMBER) IS
3737: SELECT wtp.trip_id, wst.stop_sequence_number, wst.stop_id ,
3738: wst.stop_location_id
3739: FROM wsh_new_deliveries wnd,
3740: wsh_delivery_legs wlg,
3741: wsh_trip_stops wst,
3742: wsh_trips wtp
3743: WHERE wnd.delivery_id = wlg.delivery_id and
3744: wlg.pick_up_stop_id = wst.stop_id and
3755: UNION (
3756: SELECT wtp2.trip_id, wst2.stop_sequence_number,
3757: wst2.stop_id, wst2.stop_location_id
3758: FROM wsh_new_deliveries wnd2,
3759: wsh_delivery_legs wlg2,
3760: wsh_trip_stops wst2,
3761: wsh_trips wtp2
3762: WHERE wnd2.delivery_id = wlg2.delivery_id and
3763: wlg2.drop_off_stop_id = wst2.stop_id and
3776:
3777: CURSOR c_batch_stop(l_batch_id NUMBER) IS
3778: SELECT wts.stop_id
3779: FROM wsh_trip_stops wts,
3780: wsh_delivery_legs wdl,
3781: wsh_new_deliveries wnd,
3782: wsh_picking_batches wpb
3783: WHERE p_batch_id IS NOT NULL
3784: AND wnd.batch_id = l_batch_id