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