Shipment Number And Fulfill Line Number For Split Sales Orders.sql

SELECT
	f.source_order_number
  , h.header_id
  , f.line_id
  , f.fulfill_line_id
  , f.fulfill_line_number
  , l.display_line_number
  , (l.display_line_number || '-' || f.fulfill_line_number)    exact_line_number
  , fd.delivery_name                                        AS shipment_number
FROM
	doo_fulfill_line_details fd
  , doo_fulfill_lines_all    f
  , doo_lines_all            l
  , doo_headers_all          h
WHERE
	fd.fulfill_line_id        =f.fulfill_line_id
	AND f.line_id             =l.line_id
	AND fd.task_type          ='Shipment'
	AND h.header_id           =f.header_id
	AND h.header_id           =l.header_id
	AND h.submitted_flag      ='Y'
	AND f.source_order_number = ''
ORDER BY
	l.line_id
;