Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Leigh Holling
    Drill to Fusion from a BI Publisher Report
    Topic posted October 2, 2019 by Leigh HollingRed Ribbon: 250+ Points, last edited October 2, 2019, tagged BI Publisher, Public Sector 
    121 Views, 9 Comments
    Title:
    Drill to Fusion from a BI Publisher Report
    Content:

    Hi,

    I have created a very useful data model that will allow our users to search for payments, what I would like to do is link the invoice numbers to oracle fusion financials to allow the users to quickly access the the invoice attachments and further invoice details.

     

    In an ideal world this would be a simple click of the invoice number and then they will be taken to the invoice.

     

    Regards,

    Version:
    19d

    Comment

     

    • Santosh Kumar Bhairi

      Hello Leigh,

      You may want to check this post -> https://cloudcustomerconnect.oracle.com/posts/664ae1b87c for guidelines.

      thanks,

      Santosh

    • Leigh Holling

      Hello,

       

      Thank you for this i have tried this one, but when I download the data model attached to looked it i cannot import (unarchive) it. i get a compression error.

       

      If anyone else could import it and send me the code as a .txt file that would be easier.


      Regards,

    • Manish Pesswani

      Hi Leigh,

      This can be done in BIP reports via DEEP LINK functionality.

      Just go through the deeplink configuration for OTBI reports & configure your url in BIP similary.

      I have done this in past & it works.

    • Leigh Holling

      Hi,

       

      do you have a deeplink guide for BIP I know it can be done but I am looking for the guidance or method to help me do this, i know i need to create parameters to pass invoice ID into which will then from part of the URL to drill.

    • Leigh Holling

      Hi All,

      I have managed to get the above process working, if anyone else is struggling let me know. Happy to provide clarity.

       

      Regards,

      • Santosh Kumar Bhairi

        great new Leigh. Would you mind share the steps/process, if possible?

        • Leigh Holling

          Hi Sure,

          I have only done this for Invoice Number, will update if and when I do it for any other transaction such as PO number, Supplier etc, but the guidance can be easily adapted to do these yourself.

          First step create a common lookup to find your instance Go to Setup and Maintanence>Manage Common Lookups

          Then create a new lookup that looks like the attached image

          Then in the Data model you want to drill from first create a list of values with the below in the SQL query section name it L_AP_LINK

                          select replace(MEANING, '[DUMMY]', '')  ||

          'payables/faces/FndOverview?fnd=%252FWEB-INF%252Foracle%252Fapps%252Ffinancials%252Fpayables%252Finvoices%252Ftransactions%252Fui%252Fflow%252FInvoiceHomeMainFlow.xml%2523InvoiceHomeMainFlow%3BInvoiceId%253D[P_INVOICE_ID]%253BCntxtTask%253DinvoiceDetails%253BPoMatchedFlagParam%253Dnull%253BNewViewToken%253D%252FInvoiceDetailsFlow%252FInvoiceDetailsPF%253BOldViewToken%253Dnull%3B%3B%3Bfalse%3B256%3B%3B%3B&fndGlobalItemNodeId=itemNode_payables_payables_invoices'

           

          from FUSION.FND_LOOKUP_VALUES_TL

          where

          lookup_type = 'XX_INSTANCE_PARAMETERS'

          and LOOKUP_CODE = 'FIN_URL'

          AND LANGUAGE = 'US'

          This will create a direct URL to the transaction by using the Lookup you created and the Invoice_ID in place of the [P_INVOICE_ID].

          Next create a string and menu parameter called P_AP_LINK that uses the List of values you just created, give it a display name of AP Link and untick the Can select all and Multiple selection options. 

          In your data set add a column Replace(:P_AP_LINK, '[P_INVOICE_ID]',  api.invoice_id)  AS "AP_INVOICE_LINK this will populate the invoice ID into the [P_INVOICE_ID] of the URL. Ensure that this api.invoice_id references the AP_INVOICES_ALL table and the INVOICE.ID column.

          Next go to your report or create one click and in the properties section of the Invoice Number column go to the URL section and put in {/DATA_DS/G_1/AP_INVOICE_LINK}, Note the G_1 should refer to the name of your data set change if necessary.

          Now its a good idea to apply some format  to the invoice number to make it look like it can be clicked on to your users, I turned it blue and underlined. 

          I also chose to hide the P_AP_LINK link parameter from the report by using the parameters section of the edit report and un-checking the show box.

          Hope this helps :)