The below query is used to get the order and line-level eff information
SELECT dha.order_number,
dla.line_number,
(SELECT DFLE.attribute_char1
FROM doo_fulfill_lines_eff_b DFLE,
doo_fulfill_lines_all DFLA
WHERE 1 = 1
AND DFLE.context_code = 'Customer Reference Number'
AND DFLA.fulfill_line_id = DFLE.fulfill_line_id
AND DFLA.header_id = dha.header_id
AND DFLA.line_id = dla.line_id
AND rownum = 1) customer_reference_number
FROM doo_headers_all_v dha,
doo_lines_all dla,
doo_fulfill_lines_all dfl
WHERE 1 = 1
AND dha.header_id = dla.header_id
AND dha.header_id = 100000253187322
AND dha.change_version_number = (SELECT Max(dha1.change_version_number)
FROM doo_headers_all_v dha1
WHERE
dha1.order_number = dha.order_number
AND dha1.status_code IN
( 'OPEN', 'DOO_DRAFT' )) ----this condition has been used to get latest revision
AND dfl.header_id = dha.header_id
AND dfl.line_id = dla.line_id
ORDER BY dha.order_number,
dla.line_number
