[Home] [Help]
2016: WHERE wnd.organization_id = p_organization_id
2017: AND wnd.delivery_id = p_delivery_id
2018: AND ((p_trip_id IS NULL) OR
2019: (p_trip_id IS NOT NULL AND wnd.delivery_id IN
2020: (select wdl.delivery_id from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
2021: where wdl.pick_up_stop_id = wts.stop_id
2022: and wts.trip_id = p_trip_id)))
2023: AND ((p_delivery_state IS NULL) OR
2024: (p_delivery_state IS NOT NULL AND
2030: nvl(p_delivery_state, wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id))
2031: FROM wsh_new_deliveries_ob_grp_v wnd
2032: WHERE wnd.organization_id = p_organization_id
2033: AND wnd.delivery_id IN
2034: (select wdl.delivery_id from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
2035: where wdl.pick_up_stop_id = wts.stop_id
2036: and wts.trip_id = p_trip_id)
2037: AND ((p_delivery_state IS NULL) OR
2038: (p_delivery_state IS NOT NULL AND
2079: ,wdd1.top_model_line_id
2080: FROM wsh_delivery_details_ob_grp_v wdd1, wsh_delivery_details_ob_grp_v wdd2
2081: ,wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
2082: -- Bug 5121507
2083: , wsh_delivery_legs wdl
2084: , wsh_trip_stops wts
2085: , wsh_trips wt
2086: WHERE wda.delivery_detail_id = wdd1.delivery_detail_id
2087: AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
2424: BEGIN
2425: SELECT t.trip INTO l_material_rec.trip
2426: FROM(
2427: SELECT distinct wt.name trip
2428: FROM wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt
2429: WHERE wdl.delivery_id = l_material_rec.delivery_id
2430: AND wts.stop_id = wdl.pick_up_stop_id
2431: AND wt.trip_id = wts.trip_id) t
2432: WHERE rownum <2;