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 

AR Customer invoice and ship to & Bill to details

Below is the sample query to retrieve the customer invoice and ship to  customer 

 SELECT rct.trx_number,

hp.party_name ship_to_customer,

site.party_site_number

FROM RA_CUSTOMER_TRX_ALL rct,

HZ_PARTY_SITE_USES hps,

hz_party_sites site,

hz_parties hp

WHERE RCT.TRX_NUMBER = '1017986'

AND hps.PARTY_SITE_USE_ID = rct.SHIP_TO_PARTY_SITE_USE_ID

AND hps.SITE_USE_TYPE = 'SHIP_TO'

AND hps.PARTY_SITE_ID = site.PARTY_SITE_ID

AND site.party_id = hp.party_id

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

Query to get the bill to details for a customer

SELECT hl.ADDRESS1 || ',' || hl.CITY || ',' || hl.STATE || ',' || hl.POSTAL_CODE || ',' || COUNTRY remit_address FROM ra_customer_trx_all rct, hz_cust_accounts hca, hz_cust_acct_sites_all hcas, HZ_CUST_SITE_USES_ALL hcsu, HZ_PARTY_SITES RAAD_BILL_PS, hz_locations hl WHERE 1 = 1 AND rct.bill_to_customer_id = hca.cust_account_id AND hcas.cust_account_id = hca.cust_account_id AND hcsu.site_use_id = rct.BILL_TO_SITE_USE_ID AND hcas.PARTY_SITE_ID = RAAD_BILL_PS.PARTY_SITE_ID AND SITE_USE_CODE = 'BILL_TO' AND hcas.CUST_ACCT_SITE_ID(+) = hcsu.CUST_ACCT_SITE_ID AND RAAD_BILL_PS.location_id = hl.location_id AND trx_number = '1014102'

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

To get Extensible FlexFlields (EFF) information in Oracle fusion

 We can get the EFF information by using the below SQL

EFF- The Values returned in:

fdcb.CONTEXT_IDENTIFIER,

fdsb.SEGMENT_IDENTIFIER

are used when working with extensions and service mappings in pricing.

SELECT   fdcb.context_code ,

           fdcb.context_identifier ,
           fdcb.enabled_flag ,
           fdsb.segment_code ,
           fdsb.segment_identifier ,
           fdsb.column_name
  FROM     fusion.fnd_df_contexts_tl fdct,
           fusion.fnd_df_contexts_b fdcb ,
           fusion.fnd_df_segments_tl fdst,
           fusion.fnd_df_segments_b fdsb
  WHERE    fdct.context_code = fdcb.context_code
  AND      fdcb.context_code = fdsb.context_code
  AND      fdst.context_code = fdsb.context_code
  AND      fdst.segment_code = fdsb.segment_code
  AND      fdct.application_id = fdcb.application_id
  AND      fdct.language = Userenv('Lang')
  AND      fdst.language = Userenv('Lang')
  AND      fdct.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  ORDER BY fdcb.context_identifier,
           fdsb.segment_identifier
  ---------------------------------------------------------------
  /*Select ALL Segments (Fields defined in a Contexts)*/SELECT   fdst.descriptive_flexfield_code ,
           fdst.context_code ,
           fdst.segment_code ,
           fdst.NAME ,
           fdsb.application_id ,
           fdsb.descriptive_flexfield_code ,
           fdsb.context_code ,
           fdsb.segment_code ,
           fdsb.segment_identifier ,
           fdsb.column_name ,
           fdsb.sequence_number ,
           fdsb.enabled_flag ,
           fdsb.required_flag ,
           fdsb.value_set_id ,
           fdsb.default_type ,
           fdsb.default_value ,
           fdsb.derivation_value ,
           fdsb.range_type ,
           fdsb.read_only_flag ,
           fdsb.display_type ,
           fdsb.display_width ,
           fdsb.display_height ,
           fdsb.checkbox_checked_value ,
           fdsb.checkbox_unchecked_value
  FROM     fusion.fnd_df_segments_tl fdst,
           fusion.fnd_df_segments_b fdsb
  WHERE    fdst.application_id = fdsb.application_id
  AND      fdst.enterprise_id = fdsb.enterprise_id
  AND      fdst.descriptive_flexfield_code = fdsb.descriptive_flexfield_code
  AND      fdst.context_code = fdsb.context_code
  AND      fdst.segment_code = fdsb.segment_code
  AND      fdst.language = 'US'
  AND      fdst.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  ORDER BY fdst.context_code,
           fdsb.sequence_number
  --------------------------------------------------------------------------------
  /*Select All Categories*/SELECT application_id ,
         descriptive_flexfield_code ,
         context_code ,
         category_code
  FROM   fusion.fnd_ef_category_contexts
  WHERE  descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  --------------------------------------------------------------------------------
  /*EFF Miscellaneous - 1*/SELECT   feupb.application_id ,
           feupb.descriptive_flexfield_code ,
           feupb.flexfield_usage_code ,
           feupb.category_code ,
           feupb.page_code ,
           feupt.NAME ,
           feupb.sequence_number ,
           feupb.mds_document_name
  FROM     fusion.fnd_ef_ui_pages_b feupb,
           fusion.fnd_ef_ui_pages_tl feupt
  WHERE    feupt.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  AND      feupt.application_id = feupb.application_id
  AND      feupt.descriptive_flexfield_code = feupb.descriptive_flexfield_code
  AND      feupt.flexfield_usage_code = feupb.flexfield_usage_code
  AND      feupt.category_code = feupb.category_code
  AND      feupt.page_code = feupb.page_code
  AND      feupt.language = 'US'
  ORDER BY feupb.descriptive_flexfield_code ,
           feupb.flexfield_usage_code ,
           feupb.category_code ,
           feupb.page_code ,
           feupb.sequence_number
  ----------------------------------------------------------------
  /*EFF Miscellaneous – 2*/SELECT application_id ,
         descriptive_flexfield_code ,
         flexfield_usage_code ,
         category_code ,
         page_code ,
         context_code ,
         context_category_code ,
         sequence_number ,
         mds_document_name
  FROM   fusion.fnd_ef_ui_page_task_flows
  WHERE  descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
  ------------------------------------------------------------------

Query to get ESS Job Status

 When we submit an ESS Job requested we can check the status from the backend,

Below is the SQL to get ESS Status 

SELECT state,
       requestid,
       NAME,
       executable_status,
       error_warning_message,
       error_warning_detail,
       cmdline,
       workdirectoryroot,
       logworkdirectory,
       inputworkdirectory,
       outputworkdirectory,
       redirectedoutputfile
FROM   fusion_ora_ess.request_history
WHERE  requestid = 20780---Please pass requst id

ORDER  BY requestid DESC