[Fusion] How to use multi selection LOV in Oracle Fusion BIP Report?

on


  

 In this post we will see how to use multi select LOV in Oracle Fusion BIP Report.

 

  • Login to Fusion BI Catalog of your fusion instance by using the below URL:

https://<server>.fa.ocs.oraclecloud.com/analytics/saw.dll?bieehome

  • Once logged in, create new Data Model by clicking Catalog -> Data Model
 
  • It will present you untitled Data Model. Now, click on New Data Set button and choose SQL Query as type of the Data Set from the list.

  • Enter the name of Data Set like Q1 and choose Data Source as ApplicationDB_FSCM and provide the SQL query
  • We have the below query

SELECT
     aia.invoice_id
    ,aida.invoice_distribution_id
    ,aila.line_number
    ,(SELECT hou.name FROM HR_OPERATING_UNITS hou where hou.organization_id=aia.org_id) org_name
    ,(   SELECT ps.vendor_name FROM poz_suppliers_v ps
         WHERE ps.vendor_id=aia.vendor_id
     ) vendor_name
    ,(   SELECT pss.vendor_site_code FROM poz_supplier_sites_v pss
         WHERE pss.vendor_id=aia.vendor_id AND pss.vendor_site_id=aia.vendor_site_id
     ) vendor_site_code
    ,aia.invoice_num
    ,aia.description    invoice_desc
    ,aia.invoice_type_lookup_code
    ,aia.source
    ,aia.invoice_date
    ,aia.invoice_amount
    ,aia.amount_paid
    ,aia.payment_status_flag
    ,ap_invoices_pkg.Get_approval_status(
        aia.invoice_id, aia.invoice_amount,
        aia.payment_status_flag, aia.invoice_type_lookup_code
    ) approval_status
    ,ap_invoices_pkg.Get_posting_status(aia.invoice_id) accounting_status
    ,aia.gl_date
    ,aia.invoice_currency_code
    ,aia.payment_currency_code
    ,aia.accts_pay_code_combination_id
    ,(  SELECT fnd_flex_ext.get_segs('GL', 'GL#', gcc.chart_of_accounts_id, gcc.code_combination_id)
    FROM gl_code_combinations gcc where gcc.code_combination_id=aia.accts_pay_code_combination_id
     ) code_combination
    ,aila.amount
FROM    
     ap_invoices_all                aia
    ,ap_invoice_lines_all           aila
    ,ap_invoice_distributions_all   aida
WHERE 1=1
AND aia.invoice_id      = aila.invoice_id
AND aila.invoice_id     = aida.invoice_id
AND aila.line_number    = aida.invoice_line_number
AND aia.org_id      = :pOrgId
  •  Now click OK button
  •  G_1 group will be added, click OK button again
  • This will add the parameter 
  • Now, go to List of Values section and add new LOV like below. Here you need to enter the Name of LOV and select Type as SQL Query and select Data Source as ApplicationDB_FSCM
SELECT hou.name, hou.organization_id FROM HR_OPERATING_UNITS hou order by 1
 


In LOV there are two things:
  • Key Value - It can be like ID field
  • Key Label - It can be like description
So like in this LOV we will display Org Name in LOV, but when we select any Org from LOV, it will pass Organization Id to the parameter. 
 
In this one we have Org_Name as Key Label and Organization_Id as Key value. 

First column of LOV query will be Key Label and second will be Key Value always, like:

SELECT key_label , key_value FROM table
  • So, now use this LOV in parameter like below:
 
  •  Parameter type should be Menu, and then select OrgLOV in List of Values field
  •  Also, select Multi Selection option and select Can select all & NULL Value Passed
  •  Now, click on View Data button
  • Select any Org Name from the list
  • Once you select any single Org Name from list you will be able to get the data. But in case you select multiple Org Names, you will not be able to get the data
  • To fix this problem we have to change our query like below:
SELECT
     aia.invoice_id
    ,aida.invoice_distribution_id
    ,aila.line_number
    ,(SELECT hou.name FROM HR_OPERATING_UNITS hou where hou.organization_id=aia.org_id) org_name
    ,(   SELECT ps.vendor_name FROM poz_suppliers_v ps
         WHERE ps.vendor_id=aia.vendor_id
     ) vendor_name
    ,(   SELECT pss.vendor_site_code FROM poz_supplier_sites_v pss
         WHERE pss.vendor_id=aia.vendor_id AND pss.vendor_site_id=aia.vendor_site_id
     ) vendor_site_code
    ,aia.invoice_num
    ,aia.description    invoice_desc
    ,aia.invoice_type_lookup_code
    ,aia.source
    ,aia.invoice_date
    ,aia.invoice_amount
    ,aia.amount_paid
    ,aia.payment_status_flag
    ,ap_invoices_pkg.Get_approval_status(
        aia.invoice_id, aia.invoice_amount,
        aia.payment_status_flag, aia.invoice_type_lookup_code
    ) approval_status
    ,ap_invoices_pkg.Get_posting_status(aia.invoice_id) accounting_status
    ,aia.gl_date
    ,aia.invoice_currency_code
    ,aia.payment_currency_code
    ,aia.accts_pay_code_combination_id
    ,(  SELECT fnd_flex_ext.get_segs('GL', 'GL#', gcc.chart_of_accounts_id, gcc.code_combination_id)
    FROM gl_code_combinations gcc where gcc.code_combination_id=aia.accts_pay_code_combination_id
     ) code_combination
    ,aila.amount
FROM    
     ap_invoices_all                aia
    ,ap_invoice_lines_all           aila
    ,ap_invoice_distributions_all   aida
WHERE 1=1
AND aia.invoice_id      = aila.invoice_id
AND aila.invoice_id     = aida.invoice_id
AND aila.line_number    = aida.invoice_line_number
AND (aia.org_id IN (:pOrgId) OR 'All' IN (:pOrgId ||'All'))
  • So, by using the above condition in WHERE clause you will be able to accept multiple selections for the parameter



Comments

Post a Comment