Oracle Business Intelligence Explorer Test Q&A

 Hello
If you are trying to give the BIP Explorer test,
Below are the questions asked and answers

Explorer Name: Business Intelligence Explorer

Exam Link:
https://mylearn.oracle.com/learning-path/business-intelligence-explorer/80390

---------------------------------------------------------------------------------------------------------------------------------------

1.Which Oracle Transactional Business Intelligence (OTBI) tools can be used to create OTBI analyses? (Choose two)

ANS:

Report Editor

Report Creator

---------------------------------------------------------------------------------------------------------------------------------------

2.Which is the correct order of steps to create a BI Publisher report using the Guide Me wizard?

ANS:

Select Data > Select Layout > Create Chart > Create Table > Save Report

---------------------------------------------------------------------------------------------------------------------------------------

3.Which statement is true about BI Catalog?

ANS:

It is a repository of OTBI, BI Publisher, and Financial Reporting objects.

---------------------------------------------------------------------------------------------------------------------------------------

4.Which are the components of a BI Publisher report? (Choose three)

ANS:

Layout

Template

Data Model

---------------------------------------------------------------------------------------------------------------------------------------

5.What is bursting?

ANS:

The process of generating multiple documents from a report and delivering them to different destinations

---------------------------------------------------------------------------------------------------------------------------------------

6.You can embed an analysis, webpage, or document in a dashboard.

ANS:

True

---------------------------------------------------------------------------------------------------------------------------------------

7.How are the columns in a subject area's folder or subfolder arranged?

ANS:

Alphabetically

---------------------------------------------------------------------------------------------------------------------------------------

8.Which views can you select to display information in Oracle Transactional Business Intelligence? (Choose three)

ANS:

Graphs

Table

Tile

---------------------------------------------------------------------------------------------------------------------------------------

9.Which process options are available while running a report on the Scheduled Processes page? (Choose three)

ANS:

Language

Currency

Number Format

---------------------------------------------------------------------------------------------------------------------------------------

10.What is a data model?

ANS:

It is a report component that contains SQL queries to retrieve data and other elements like parameters and bursting definitions.

---------------------------------------------------------------------------------------------------------------------------------------

11.Which reporting objects can you create using Oracle Transactional Business Intelligence? (Choose three)

ANS:

Analyses

Data Models

Dashboards

---------------------------------------------------------------------------------------------------------------------------------------

12.What is the function of a translation in a BI Publisher report?

ANS:

Allows you to generate a report’s output in multiple languages at run time

---------------------------------------------------------------------------------------------------------------------------------------

13.The BI Answers tool is only used to create Oracle Transactional Business Intelligence analyses for ERP applications like Expenses.

ANS:

False

---------------------------------------------------------------------------------------------------------------------------------------

14.Multiple versions of a report can be created in a single run by selecting different layouts in the Output option of the Schedule Process page.

ANS:

True

---------------------------------------------------------------------------------------------------------------------------------------

15.Which tabs are available in the BI Answers work area? (Choose four)

ANS:

Results

Prompts

Views

Criteria

---------------------------------------------------------------------------------------------------------------------------------------

16.What is a subject area?

ANS:

It’s a collection of columns available to the report writer for creating reports.

---------------------------------------------------------------------------------------------------------------------------------------

17.Which icon in the BI Catalog toolbar do you use to view the owner or the last modified time of an object?

ANS:

Change-List View Type

---------------------------------------------------------------------------------------------------------------------------------------

18.Which tasks can be performed while editing an Oracle Transactional Business Intelligence analysis? (Choose three)

ANS:

Add or remove columns.

Define prompts.

Configure column properties.

---------------------------------------------------------------------------------------------------------------------------------------

19.Where are predefined reporting objects stored?

ANS:

Shared Folders

---------------------------------------------------------------------------------------------------------------------------------------

20.Which options are available in the Create drop-down list of the Reports and Analytics work area? (Choose two)

ANS:

Report

Analysis

---------------------------------------------------------------------------------------------------------------------------------------

Query to get Assets SLA TO GL Entries in Fusion

 The below query provides you the details about asset journal entires of SLA with GL

SELECT GJH.description                                     DESCRIPTION,
       GJH.name                                            Journal_NAME,
       GJH.je_source,
       GJH.period_name,
       GCC.segment1,
       GCC.segment2,
       GCC.segment3,
       GCC.segment4,
       GCC.segment5,
       GCC.segment6,
       GCC.segment7,
       ( xal.entered_cr )                                  Credit,
       ( xal.entered_dr )                                  Debit,
       Nvl(xal.accounted_dr, 0) - Nvl(xal.accounted_cr, 0) Net,
       To_char(gjl.effective_date, 'mm/dd/yyyy')           GL_DATE,
       Nvl(fa.asset_number, (SELECT FAV.asset_number
                             FROM   fa_additions_vl FAV
                             WHERE  FAV.asset_id = XTE.source_id_int_1
                                    AND ROWNUM = 1))
       INVOICE_RECEIPT_PAYMENT_NUMBER_ref,
       gl.name                                             ledger_name,
       gjh.je_category
FROM   gl_je_headers GJH,
       gl_je_lines GJL,
       gl_code_combinations GCC,
       gl_import_references GIR,
       xla_ae_lines XAL,
       xla_ae_headers XAH,
       xla_events xe,
       xla_transaction_entities xte,
       gl_ledgers gl,
       fa_transaction_headers FTH,
       fa_additions_vl fa
WHERE  1 = 1
       AND GJH.je_header_id = GJL.je_header_id
       AND GJL.code_combination_id = GCC.code_combination_id
       AND GJH.je_header_id = GIR.je_header_id
       AND GJL.je_line_num = GIR.je_line_num
       AND GIR.gl_sl_link_id = XAL.gl_sl_link_id
       AND GIR.gl_sl_link_table = XAL.gl_sl_link_table
       AND XAL.ae_header_id = XAH.ae_header_id
       AND xah.application_id = xe.application_id
       AND xah.application_id = xe.application_id
       AND xah.event_id = xe.event_id
       AND xe.application_id = xte.application_id
       AND xe.entity_id = xte.entity_id
       -- AND xte.entity_code = 'ADJUSTMENTS'
       -- AND xte.source_id_int_1 = aaa.adjustment_id
       AND GJL.status = 'P'
       AND GJH.je_source = 'Assets'
       AND XTE.source_id_int_1 = FTH.transaction_header_id(+)
       AND FTH.asset_id = fa.asset_id(+)
       AND gl.name = Nvl(:p_le, gl.name)
       AND gjh.period_name = Nvl(:P_PERIOD, gjh.period_name) 

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'
  ------------------------------------------------------------------