[Home] [Help]
526: CURSOR c_wdl_details1 IS
527: SELECT pick_up_stop_id,
528: drop_off_stop_id,
529: wdl.delivery_id
530: FROM wsh_delivery_legs wdl
531: WHERE (pick_up_stop_id = p_stop_id
532: OR drop_off_stop_id = p_stop_id);
533:
534: CURSOR c_wdl_details2 IS
534: CURSOR c_wdl_details2 IS
535: SELECT pick_up_stop_id,
536: drop_off_stop_id,
537: delivery_id
538: FROM wsh_delivery_legs
539: WHERE drop_off_stop_id = p_stop_id;
540:
541: l_rec_wdl1 WSH_TRIP_STOPS_VALIDATIONS.dleg_details_tab;
542: --l_rec_wdl2 c_wdl_details%TYPE;
907: NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, --OTM R12, glog proj
908: NVL(wnd.tms_interface_flag,WSH_TRIP_STOPS_PVT.C_TMS_NOT_TO_BE_SENT) tms_interface_flag, -- OTM R12, glog proj
909: wsp.otm_enabled
910: from wsh_new_deliveries wnd,
911: wsh_delivery_legs wdl,
912: wsh_shipping_parameters wsp
913: where wnd.delivery_id = wdl.delivery_id
914: and (wdl.pick_up_stop_id = p_stop_id
915: OR wdl.drop_off_stop_id = p_stop_id )
919: select distinct 'X'
920: from wsh_delivery_details wdd,
921: wsh_new_deliveries wnd,
922: wsh_delivery_assignments_v wda,
923: wsh_delivery_legs wdl
924: where (wdl.pick_up_stop_id = p_stop_id OR wdl.drop_off_stop_id = p_stop_id)
925: and wnd.delivery_id = wdl.delivery_id
926: and wda.delivery_id = wnd.delivery_id
927: and wdd.delivery_detail_id = wda.delivery_detail_id
935: wnd.del_wf_intransit_attr,
936: wnd.del_wf_close_attr,
937: decode(p_stop_id,wdl.drop_off_stop_id,'D',wdl.pick_up_stop_id,'P','X') stop_type
938: from wsh_new_deliveries wnd,
939: wsh_delivery_legs wdl
940: where wnd.delivery_id = wdl.delivery_id
941: and (wdl.pick_up_stop_id = p_stop_id OR
942: wdl.drop_off_stop_id = p_stop_id );
943:
2904: --
2905: CURSOR open_pickup_dlvy_csr (p_stop_id NUMBER) IS
2906: SELECT nvl(shipment_direction,'O') shipment_direction
2907: FROM wsh_new_deliveries wnd,
2908: wsh_delivery_legs wdl
2909: WHERE wdl.pick_up_stop_id = p_stop_id
2910: AND wnd.delivery_id = wdl.delivery_id
2911: AND wnd.status_code IN ('OP', 'PA')
2912: ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 );
2930: (DECODE(wnd.ultimate_dropoff_location_id,p_dummy_location_id,1, 2))
2931: ) last_stop,
2932: wdl.drop_off_stop_id drop_off_stop_id
2933: FROM wsh_new_deliveries wnd,
2934: wsh_delivery_legs wdl
2935: WHERE wdl.drop_off_stop_id IN (p_stop_id, p_dummy_stop_id)
2936: AND wnd.delivery_id = wdl.delivery_id
2937: AND (
2938: (
2977: wt.name prev_leg_trip_name
2978: FROM wsh_trip_stops prev_leg_do_stop,
2979: wsh_trip_stops prev_leg_pu_stop,
2980: wsh_trip_stops curr_leg_pu_stop,
2981: wsh_delivery_legs prev_leg,
2982: wsh_delivery_legs curr_leg,
2983: wsh_new_deliveries wnd,
2984: wsh_trips wt
2985: WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
2978: FROM wsh_trip_stops prev_leg_do_stop,
2979: wsh_trip_stops prev_leg_pu_stop,
2980: wsh_trip_stops curr_leg_pu_stop,
2981: wsh_delivery_legs prev_leg,
2982: wsh_delivery_legs curr_leg,
2983: wsh_new_deliveries wnd,
2984: wsh_trips wt
2985: WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
2986: --AND st1.status_code = 'OP'
3035: wt.name prev_leg_trip_name
3036: FROM wsh_trip_stops prev_leg_do_stop,
3037: wsh_trip_stops prev_leg_pu_stop,
3038: wsh_trip_stops curr_leg_pu_stop,
3039: wsh_delivery_legs prev_leg,
3040: wsh_delivery_legs curr_leg,
3041: wsh_new_deliveries wnd,
3042: wsh_trips wt
3043: WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
3036: FROM wsh_trip_stops prev_leg_do_stop,
3037: wsh_trip_stops prev_leg_pu_stop,
3038: wsh_trip_stops curr_leg_pu_stop,
3039: wsh_delivery_legs prev_leg,
3040: wsh_delivery_legs curr_leg,
3041: wsh_new_deliveries wnd,
3042: wsh_trips wt
3043: WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
3044: --AND st1.status_code = 'OP'
3072: wnd.delivery_id delivery_id,
3073: wnd.status_code delivery_statusCode,
3074: wnd.initial_pickup_location_id dlvy_initialPULocationId
3075: FROM wsh_trip_stops st1,
3076: wsh_delivery_legs dl2,
3077: wsh_new_deliveries wnd
3078: WHERE dl2.pick_up_stop_id = st1.stop_id
3079: AND st1.status_code = 'CL'
3080: AND dl2.drop_off_stop_id IN (p_stop_id, p_linked_stop_id)
3092: DECODE(NVL(dl.shipment_direction,'O'), 'IO', dl.ultimate_dropoff_location_id, NULL) io_location_id,
3093: dl.name,
3094: dl.status_code
3095: FROM wsh_new_deliveries dl,
3096: wsh_delivery_legs dg,
3097: wsh_trip_stops st
3098: WHERE dg.delivery_id = dl.delivery_id
3099: AND st.stop_location_id = dl.initial_pickup_location_id
3100: AND st.stop_id = dg.pick_up_stop_id
3115: dl.name,
3116: dl.status_code,
3117: dg.drop_off_stop_id
3118: FROM wsh_new_deliveries dl,
3119: wsh_delivery_legs dg,
3120: wsh_trip_stops st
3121: WHERE dg.delivery_id = dl.delivery_id
3122: AND st.stop_location_id = dl.ultimate_dropoff_location_id
3123: AND st.stop_id = dg.drop_off_stop_id
5056: IS
5057: SELECT wnd.initial_pickup_date delivery_Date,
5058: DECODE(wts.stop_location_id,wnd.initial_pickup_location_id,1,3) order_seq
5059: FROM wsh_new_deliveries wnd,
5060: wsh_delivery_legs wdl,
5061: wsh_trip_stops wts
5062: WHERE wts.stop_id = p_stop_id
5063: AND wdl.pick_up_stop_id = wts.stop_id
5064: AND wdl.delivery_id = wnd.delivery_id
5065: UNION
5066: SELECT wnd.ultimate_dropoff_date delivery_Date,
5067: DECODE(wts.stop_location_id,wnd.ultimate_dropoff_location_id,2,4) order_seq
5068: FROM wsh_new_deliveries wnd,
5069: wsh_delivery_legs wdl,
5070: wsh_trip_stops wts
5071: WHERE wts.stop_id = p_stop_id
5072: AND wdl.drop_off_stop_id = wts.stop_id
5073: AND wdl.delivery_id = wnd.delivery_id