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 

Query to get Customer Contact Information in Oracle Fusion

Below is the SQL used to get Customer Contact Information

SELECT party_id,
       person_last_name,
       person_first_name,
       party_number,
       party_name,
       status,
       email_address
FROM   fusion.hz_parties
WHERE  Upper (party_name)LIKE Upper('%&PARTY_NAME%')
       AND party_type = 'PERSON'

SELECT hzporg.party_number,
       hzporg.party_name,
       hzporg.party_type,
       hZp.person_last_name,
       hZp.person_first_name,
       hZp.party_number,
       hZp.party_name,
       hZp.status,
       hZp.email_address
FROM   fusion.hz_relationships HZR,
       fusion.hz_parties hzp,
       fusion.hz_parties hzpORG
WHERE  HZR.object_id = hzp.party_id
       AND Upper (hzp.party_name) LIKE Upper('%&PARTY_NAME%')
       AND hzp.party_type = 'PERSON'
       AND hzr.relationship_code = 'CONTACT'
       AND hzr.subject_id = hzporg.party_id
ORDER  BY hzporg.party_name,
          hZp.person_last_name,
          hZp.person_first_name

SELECT hzo.contact_number,
       hzporg.party_number,
       hzporg.party_name,
       hzporg.party_type,
       hzr.subject_id,
       hzo.org_contact_id,
       hZp.person_last_name,
       hZp.person_first_name,
       hZp.party_number,
       hZp.party_name,
       hZp.status,
       hZp.email_address,
       hzp.party_id
FROM   fusion.hz_relationships HZR,
       fusion.hz_parties hzp,
       fusion.hz_org_contacts hzo,
       fusion.hz_parties hzpORG
WHERE  HZR.object_id = hzp.party_id
       AND Upper (hzp.party_name)LIKE Upper('%&PARTY_NAME%')
       AND hzp.party_type = 'PERSON'
       AND hzr.relationship_code = 'CONTACT'
       AND hzr.subject_id = hzporg.party_id
       AND hzo.party_relationship_id = HZR.relationship_id
ORDER  BY hzporg.party_name,
          hZp.person_last_name,

          hZp.person_first_name