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

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

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

The Cool Fox

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

Comments

Post a Comment

All Categories