[Home] [Help]
9704: -- Cursor to retrieve the trip stop id, carrier id, and expected ship time
9705: -- (without considering dock appointments) for a WDD demand line.
9706: -- The order to look for this information is documented in the Crossdock Pegging TDD.
9707: -- Make use of outer joins in a cascading manner since records might not exist in all
9708: -- of the tables. WSH_DELIVERY_LEGS could have multiple trip stops for the same delivery.
9709: -- For our purposes, we want to pick the first one, hence choose the one with the minimum
9710: -- value for the sequence_number. This is contingent upon a delivery being present for
9711: -- the WDD demand line. For WDD demand lines, reservations will store the mtl sales order
9712: -- header ID, OE order line ID, and WDD delivery detail ID as the source header ID, line ID,
9721: NVL(wts.planned_departure_date,
9722: NVL(wdd.date_scheduled,
9723: NVL(ool.schedule_ship_date, ool.promise_date))) AS expected_ship_date
9724: FROM wsh_delivery_details wdd, oe_order_lines_all ool,
9725: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9726: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9727: wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool, wsh_carriers wc_ool
9728: WHERE wdd.delivery_detail_id = p_source_line_detail_id
9729: AND ool.line_id = p_source_line_id
9733: AND wda.delivery_id = wnd.delivery_id (+)
9734: AND wnd.delivery_id = wdl.delivery_id (+)
9735: AND (wdl.sequence_number IS NULL OR
9736: wdl.sequence_number = (SELECT MIN(sequence_number)
9737: FROM wsh_delivery_legs wdl_first_leg
9738: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
9739: AND wdl.pick_up_stop_id = wts.stop_id (+)
9740: AND wts.trip_id = wt.trip_id (+)
9741: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
9763: wc_poll.carrier_id))) AS carrier_id,
9764: NVL(wts.planned_arrival_date,
9765: NVL(poll.promised_date, poll.need_by_date)) AS expected_receipt_date
9766: FROM po_line_locations_all poll, wsh_delivery_details wdd,
9767: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9768: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9769: wsh_carrier_services wcs_wdd, wsh_carriers wc_poll
9770: WHERE poll.po_header_id = p_source_header_id
9771: AND poll.line_location_id = p_source_line_id
9779: AND wda.delivery_id = wnd.delivery_id (+)
9780: AND wnd.delivery_id = wdl.delivery_id (+)
9781: AND (wdl.sequence_number IS NULL OR
9782: wdl.sequence_number = (SELECT MIN(sequence_number)
9783: FROM wsh_delivery_legs wdl_first_leg
9784: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
9785: AND wdl.drop_off_stop_id = wts.stop_id (+)
9786: AND wts.trip_id = wt.trip_id (+)
9787: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
9812: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
9813: NVL(poll.promised_date, poll.need_by_date))) AS expected_receipt_date
9814: FROM po_line_locations_all poll, wsh_delivery_details wdd,
9815: rcv_shipment_headers rsh, rcv_shipment_lines rsl,
9816: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9817: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9818: wsh_carrier_services wcs_wdd, wsh_carriers wc_poll, wsh_carriers wc_rsh
9819: WHERE rsl.po_header_id = p_source_header_id
9820: AND rsl.po_line_location_id = p_source_line_id
9837: AND wda.delivery_id = wnd.delivery_id (+)
9838: AND wnd.delivery_id = wdl.delivery_id (+)
9839: AND (wdl.sequence_number IS NULL OR
9840: wdl.sequence_number = (SELECT MIN(sequence_number)
9841: FROM wsh_delivery_legs wdl_first_leg
9842: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
9843: AND wdl.drop_off_stop_id = wts.stop_id (+)
9844: AND wts.trip_id = wt.trip_id (+)
9845: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
9877: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
9878: prl.need_by_date)) AS expected_receipt_date
9879: FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, rcv_shipment_headers rsh,
9880: oe_order_lines_all ool, wsh_delivery_details wdd,
9881: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9882: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9883: wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool,
9884: wsh_carriers wc_ool, wsh_carriers wc_rsh, wsh_carrier_services wcs_prl
9885: WHERE prl.requisition_header_id = p_source_header_id
9901: AND wda.delivery_id = wnd.delivery_id (+)
9902: AND wnd.delivery_id = wdl.delivery_id (+)
9903: AND (wdl.sequence_number IS NULL OR
9904: wdl.sequence_number = (SELECT MIN(sequence_number)
9905: FROM wsh_delivery_legs wdl_first_leg
9906: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
9907: AND wdl.drop_off_stop_id = wts.stop_id (+)
9908: AND wts.trip_id = wt.trip_id (+)
9909: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
10494: MAX(NVL(wts.planned_departure_date,
10495: NVL(wdd.date_scheduled,
10496: NVL(ool.schedule_ship_date, ool.promise_date)))) AS max_expected_ship_date
10497: FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
10498: wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt, oe_order_lines_all ool,
10499: wsh_carrier_services wcs_wnd, wsh_carrier_services wcs_wdd,
10500: wsh_carrier_services wcs_ool, wsh_carriers wc_ool
10501: WHERE wnd.delivery_id = p_delivery_id
10502: AND wnd.shipment_direction = 'O'
10505: AND wdd.source_line_id = ool.line_id (+)
10506: AND wnd.delivery_id = wdl.delivery_id (+)
10507: AND (wdl.sequence_number IS NULL OR
10508: wdl.sequence_number = (SELECT MIN(sequence_number)
10509: FROM wsh_delivery_legs wdl_first_leg
10510: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
10511: AND wdl.pick_up_stop_id = wts.stop_id (+)
10512: AND wts.trip_id = wt.trip_id (+)
10513: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)