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 

Query to get Order Bill to & Ship to Address for a customer

 Below is the reference query for the Bill to & Ship to customer and address details 

**********************************************************************************

SELECT hl.ADDRESS1,

hl.ADDRESS2,

hl.CITY,

hl.POSTAL_CODE,

(

SELECT territory_short_name

FROM fnd_territories_tl

WHERE territory_code = hl.country

AND LANGUAGE = 'US'

) country,

hzp.party_id,

hzp.party_name,

hzp.party_number

FROM 

doo_order_addresses doa,

hz_party_sites hps,

doo_headers_all dha,

hz_locations hl,

hz_parties hzp

WHERE 

doa.HEADER_ID = 300000010340672   -------Please pass header of an Order

AND doa.ADDRESS_USE_TYPE = 'SHIP_TO' ------this is for ship to, if you need bill to please replace SHIP_TO With BILL_TO

AND doa.party_site_id = hps.party_site_id

AND hps.location_id = hl.location_id

AND hzp.party_id = dha.sold_to_party_id

AND dha.header_id = doa.header_id

**********************************************************************************

SCM: OM: SQL Query Obtain All FOB Information - FOB is not shown on Order Entry UI

 FOB information is stored on DOO_HEADERS_ALL in the attribute - FOB_POINT_CODE.

Any Changes to FOB information must be collected, this is described in the following notes:

The collection process is described in: FA: SCM: GOP: Collecting Fusion Reference Data (Doc ID 2102248.1)
The following note describes how to view collected data: FA: SCM: GOP: How To Review Collected Order Reference Data (Doc ID 1329868.1)

NOTE: FOB information will only be shown on the Order Entry UI where:

1. The FOB is active.
2. A start date has been provided
3. The current date is between Start and End Date.

The following SQL can be used to identify the FOB information that has been created.

SELECT  mslt.lookup_code      ,
mslt.meaning          ,
mslt.description      ,
mslb.start_date_active,
mslb.end_date_Active  ,
mslb.enabled_flag
FROM    fusion.MSC_SR_LOOKUP_VALUES_B mslb,
fusion.MSC_SR_LOOKUP_VALUES_tl mslt
WHERE   mslb.lookup_code     = mslt.lookup_code
AND mslb.lookup_type = 'FOB'
AND mslt.language    = userenv('LANG')

Cross reference data can be reviewed by running the following SQL:

SELECT  mai.instance_code  ,

mxm.ENTITY_NAME    ,

mxm.ATTRIBUTE_NAME ,

mxm.SOURCE_VALUE   ,

mxm.TARGET_VALUe

FROM    fusion.MSC_XREF_MAPPING MXM,

Fusion.MSC_APPs_INSTANCEs Mai

WHERE   mxm.SR_INSTANCE_ID = MAI.instance_id

AND entity_name LIKE 'FOB'

ORDER BY mai.instance_code ,

mxm.ATTRIBUTE_NAME ,

mxm.SOURCE_VALUE   ,

mxm.TARGET_VALUe