Order Fulfillment Lines Which are - Awaiting Shipping - the Shipment Has Been Confirmed and Interfaced.sql

SELECT DISTINCT
	doh.order_number
  , wnd.DELIVERY_id
  , wnd.DELIVERY_NAME
  , dfla.fulfill_line_id
  , dfla.status_code
FROM
	fusion.doo_headers_all          doh
  , fusion.doo_fulfill_lines_all    dfla
  , fusion.wsh_delivery_details     wdd
  , fusion.WSH_DELIVERY_ASSIGNMENTS WDA
  , fusion.WSH_NEW_DELIVERIES       WND
WHERE
	wdd.delivery_Detail_id                                      =WDA.delivery_Detail_id
	AND WDA.DELIVERY_ID                                         = WND.DELIVERY_ID
	AND doh.header_id                                           =dfla.header_id
	AND NVL(dfla.fulfillment_split_ref_id,dfla.FULFILL_line_ID) = wdd.source_shipment_id
	AND SHIPMENT_ADVICE_STATUS                            IS NULL
	AND wdd.WMS_INTERFACED_FLAG                                 ='CONFIRMED'
	AND wdd.released_status                                     ='C'
	AND dfla.status_code IN ('BACKORDERED','AWAIT_SHIP')
	AND doh.submitted_flag ='Y'
	AND NOT EXISTS
	(
		SELECT  *
		FROM
			fusion.doo_fulfill_line_DETAILS FLD
		WHERE
			FLD.DELIVERY_NAME =WND.DELIVERY_NAME
	)
;