[Home] [Help]
9899: -- Cursor to retrieve the trip stop id, carrier id, and expected ship time
9900: -- (without considering dock appointments) for a WDD demand line.
9901: -- The order to look for this information is documented in the Crossdock Pegging TDD.
9902: -- Make use of outer joins in a cascading manner since records might not exist in all
9903: -- of the tables. WSH_DELIVERY_LEGS could have multiple trip stops for the same delivery.
9904: -- For our purposes, we want to pick the first one, hence choose the one with the minimum
9905: -- value for the sequence_number. This is contingent upon a delivery being present for
9906: -- the WDD demand line. For WDD demand lines, reservations will store the mtl sales order
9907: -- header ID, OE order line ID, and WDD delivery detail ID as the source header ID, line ID,
9916: NVL(wts.planned_departure_date,
9917: NVL(wdd.date_scheduled,
9918: NVL(ool.schedule_ship_date, ool.promise_date))) AS expected_ship_date
9919: FROM wsh_delivery_details wdd, oe_order_lines_all ool,
9920: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9921: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9922: wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool, wsh_carriers wc_ool
9923: WHERE wdd.delivery_detail_id = p_source_line_detail_id
9924: AND ool.line_id = p_source_line_id
9928: AND wda.delivery_id = wnd.delivery_id (+)
9929: AND wnd.delivery_id = wdl.delivery_id (+)
9930: AND (wdl.sequence_number IS NULL OR
9931: wdl.sequence_number = (SELECT MIN(sequence_number)
9932: FROM wsh_delivery_legs wdl_first_leg
9933: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
9934: AND wdl.pick_up_stop_id = wts.stop_id (+)
9935: AND wts.trip_id = wt.trip_id (+)
9936: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
9958: wc_poll.carrier_id))) AS carrier_id,
9959: NVL(wts.planned_arrival_date,
9960: NVL(poll.promised_date, poll.need_by_date)) AS expected_receipt_date
9961: FROM po_line_locations_all poll, wsh_delivery_details wdd,
9962: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9963: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9964: wsh_carrier_services wcs_wdd, wsh_carriers wc_poll
9965: WHERE poll.po_header_id = p_source_header_id
9966: AND poll.line_location_id = p_source_line_id
9974: AND wda.delivery_id = wnd.delivery_id (+)
9975: AND wnd.delivery_id = wdl.delivery_id (+)
9976: AND (wdl.sequence_number IS NULL OR
9977: wdl.sequence_number = (SELECT MIN(sequence_number)
9978: FROM wsh_delivery_legs wdl_first_leg
9979: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
9980: AND wdl.drop_off_stop_id = wts.stop_id (+)
9981: AND wts.trip_id = wt.trip_id (+)
9982: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
10007: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
10008: NVL(poll.promised_date, poll.need_by_date))) AS expected_receipt_date
10009: FROM po_line_locations_all poll, wsh_delivery_details wdd,
10010: rcv_shipment_headers rsh, rcv_shipment_lines rsl,
10011: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
10012: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
10013: wsh_carrier_services wcs_wdd, wsh_carriers wc_poll, wsh_carriers wc_rsh
10014: WHERE rsl.po_header_id = p_source_header_id
10015: AND rsl.po_line_location_id = p_source_line_id
10032: AND wda.delivery_id = wnd.delivery_id (+)
10033: AND wnd.delivery_id = wdl.delivery_id (+)
10034: AND (wdl.sequence_number IS NULL OR
10035: wdl.sequence_number = (SELECT MIN(sequence_number)
10036: FROM wsh_delivery_legs wdl_first_leg
10037: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
10038: AND wdl.drop_off_stop_id = wts.stop_id (+)
10039: AND wts.trip_id = wt.trip_id (+)
10040: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
10072: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
10073: prl.need_by_date)) AS expected_receipt_date
10074: FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, rcv_shipment_headers rsh,
10075: oe_order_lines_all ool, wsh_delivery_details wdd,
10076: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
10077: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
10078: wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool,
10079: wsh_carriers wc_ool, wsh_carriers wc_rsh, wsh_carrier_services wcs_prl
10080: WHERE prl.requisition_header_id = p_source_header_id
10096: AND wda.delivery_id = wnd.delivery_id (+)
10097: AND wnd.delivery_id = wdl.delivery_id (+)
10098: AND (wdl.sequence_number IS NULL OR
10099: wdl.sequence_number = (SELECT MIN(sequence_number)
10100: FROM wsh_delivery_legs wdl_first_leg
10101: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
10102: AND wdl.drop_off_stop_id = wts.stop_id (+)
10103: AND wts.trip_id = wt.trip_id (+)
10104: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
10689: MAX(NVL(wts.planned_departure_date,
10690: NVL(wdd.date_scheduled,
10691: NVL(ool.schedule_ship_date, ool.promise_date)))) AS max_expected_ship_date
10692: FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
10693: wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt, oe_order_lines_all ool,
10694: wsh_carrier_services wcs_wnd, wsh_carrier_services wcs_wdd,
10695: wsh_carrier_services wcs_ool, wsh_carriers wc_ool
10696: WHERE wnd.delivery_id = p_delivery_id
10697: AND wnd.shipment_direction = 'O'
10700: AND wdd.source_line_id = ool.line_id (+)
10701: AND wnd.delivery_id = wdl.delivery_id (+)
10702: AND (wdl.sequence_number IS NULL OR
10703: wdl.sequence_number = (SELECT MIN(sequence_number)
10704: FROM wsh_delivery_legs wdl_first_leg
10705: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
10706: AND wdl.pick_up_stop_id = wts.stop_id (+)
10707: AND wts.trip_id = wt.trip_id (+)
10708: AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)