Fusion : BIP Report With Multi Selection & Parameter Printing Logic
=========================================================
Tracking
=========================================================
Select *
from hr_all_organization_units
where ORGANIZATION_ID=300000001829266
Select *
from ap_invoices_all
where 1=1
and ORG_ID=300000001829266
and invoice_id=1094
Select *
from poz_suppliers_v
where vendor_id=300000004110228
Select *
from poz_supplier_sites_v
where vendor_id=300000004110228
=========================================================
MAIN Query
=========================================================
Select
hou.NAME Organization_NAME,
aia.INVOICE_NUM,
aia.invoice_amount,
(Select segment1
from poz_suppliers_v
where vendor_id=aia.vendor_id) Supplier_Number,
(Select VENDOR_NAME
from poz_suppliers_v
where vendor_id=aia.vendor_id) Supplier_Name,
(Select VENDOR_SITE_CODE
from poz_supplier_sites_v
where vendor_id=aia.vendor_id
and vendor_site_id=aia.vendor_site_id) Supplier_Site_Name
from
hr_all_organization_units hou,
ap_invoices_all aia
Where
1=1
and hou.ORGANIZATION_ID=aia.ORG_ID
--and hou.ORGANIZATION_ID = NVL(:P_ORG_NAME,hou.ORGANIZATION_ID)
and (hou.ORGANIZATION_ID IN(:P_ORG_NAME) OR 'ALL' IN(:P_ORG_NAME||'ALL'))
--and aia.invoice_id= NVL(:P_INVOICE_ID,aia.invoice_id)
and (aia.invoice_id IN(:P_INVOICE_NUM) OR 'ALL' IN(:P_INVOICE_NUM||'ALL'))
=========================================================
Parameter LOV Query
=========================================================
Select name,ORGANIZATION_ID
from hr_all_organization_units
Select invoice_num,invoice_id
from ap_invoices_all
where org_id in(:P_ORG_NAME)
--(hou.ORGANIZATION_ID In(:P_ORG_NAME) Or 'All' In(:P_ORG_NAME||'All'))
(aia.invoice_id IN(:P_INVOICE_NUM) OR 'ALL' IN(:P_INVOICE_NUM||'ALL')) ---Logic used in main Query for multiselection of paramters values
===========Parameters Printing Logic ================================
------------------------------------------------------
Parameter Priniting When parameters are multiselected
LISTAGG() is used to print the multiselected paramter
values ',' comma seperated.
------------------------------------------------------
select
nvl(
(Select listagg(name,',')within group(order by name)
from hr_all_organization_units
where ORGANIZATION_ID in(:P_ORG_NAME)),'All'
)v_bu,
nvl(
(Select listagg(invoice_num,',')within group(order by invoice_num)
from ap_invoices_all
where invoice_id in(:P_INVOICE_NUM)),'All'
)v_in
from dual
---------------------------------------------------------------------
Parameter Priniting when parameter are not multiselected
---------------------------------------------------------------------
nvl2(:P_INVOICE_ID,
(Select invoice_num
from ap_invoices_all
where invoice_id =:P_INVOICE_NUM
),'All' )v_in
=========================================================
No comments:
Post a Comment