Query to get Order details along with File level EFF information in Fusion

 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