Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Sachin Mistry
    Hyperlinking PO Number, AP Invoice Number on BI Publisher...Answered
    Topic posted November 13, 2017 by Sachin MistryGold Medal: 3,500+ Points, tagged BI Publisher, Fusion Financial reporting, Fusion Procurement reporting, Fusion Project reporting, Fusion Supply Chain Management reporting, Reports, Sample Reports 
    866 Views, 17 Comments
    Title:
    Hyperlinking PO Number, AP Invoice Number on BI Publisher reports
    Summary:
    Hyperlinking PO Number, AP Invoice Number on BI Publisher reports
    Content:

    We have a BI Publisher report where we are showing Project profitability and showing all transactions on the project - such as all PO lines, all AP lines, all lines imported into projects that are non PO or AP related such as time and expenses.

    End users will like to see hyperlink to PO numbers where they can open the PO, see attachments and all change history.

    How can we accomplish this?

    Best Comment

    Alexey Shtrakhov

    Ok, here it is :)
    Attached the document. Including word document and 2 BIP reports with their datamodels.

    BIP Invoice Drilldown.docx --> document explaining the solution
    AP Invoice Listing with Drilldown --> this is the main report where the drill down is implemented.
    APInvoiceDetails --> this is the sub report

    It probably looking a little bit more complicated in written form, but in fact the idea is quite straightforward.

    I tried to provide 2 examples of dynamic linking.

    1) Link to AP form : here we generate link fully in SQL
    2) Link to a sub report (BIP REPORT) : here we generate partially in Datamodel and then add dynamicaly invoice id value on the template itself.

    Both of these solutions are needed anyway, as in some cases there are some restrictions on how Report Template URL property is behaving.... So you can have here 2 different ways.. But in someway it is also good to see 2 slightly different ways to do it.

    Also provided my solution of the common problem we noticed related to linked report migration from TEST to PROD and same issue related to instance cloning... This mean that if you hardcode instance name in your links, then above mentioned operations will not work properly and you would have manually to update those links in the report definition... Not sure if somebody has better solution for this, but at least this is working.

    Anyway enjoy this material and if any questions please ask :)

    With respect
    Alexey

    Comment

     

    • Alexey Shtrakhov

      You could do this by creating dynamic URL on your main report to open the detail report for the particular transaction e.g. PO.
      Can provide you some example how I did it with AP Invoice.

      Not sure if you can drill down directly to PO but to AP invoice we were not able to drill down. There exist ER to implement DEEP LINKS for Financial Applications. Support for drilldowns/Deeplink from OTBI to objects in Fusion Applications (Doc ID 1612091.1)

       

      Alexey

    • Sachin Mistry

      Alexey,

      Appreciate your response. Example of opening another detail report from a particular transaction will be helpful too.

      Best,
      Sachin

    • Alexey Shtrakhov

      Hi Guys,

      Will provide you a document with a way to do it for AP invoice today.
      I got actually it working to be able to drilldown to AP from BIP report... :) it is cool.

      Alexey

       

    • Alexey Shtrakhov

      Ok, here it is :)
      Attached the document. Including word document and 2 BIP reports with their datamodels.

      BIP Invoice Drilldown.docx --> document explaining the solution
      AP Invoice Listing with Drilldown --> this is the main report where the drill down is implemented.
      APInvoiceDetails --> this is the sub report

      It probably looking a little bit more complicated in written form, but in fact the idea is quite straightforward.

      I tried to provide 2 examples of dynamic linking.

      1) Link to AP form : here we generate link fully in SQL
      2) Link to a sub report (BIP REPORT) : here we generate partially in Datamodel and then add dynamicaly invoice id value on the template itself.

      Both of these solutions are needed anyway, as in some cases there are some restrictions on how Report Template URL property is behaving.... So you can have here 2 different ways.. But in someway it is also good to see 2 slightly different ways to do it.

      Also provided my solution of the common problem we noticed related to linked report migration from TEST to PROD and same issue related to instance cloning... This mean that if you hardcode instance name in your links, then above mentioned operations will not work properly and you would have manually to update those links in the report definition... Not sure if somebody has better solution for this, but at least this is working.

      Anyway enjoy this material and if any questions please ask :)

      With respect
      Alexey

    • Alexey Shtrakhov

      Hi Sachin,

      So for dynamic PO drilldown from BIP report...

      Found some link on PO Approval and could modify it dynamicaly. So you can follow the same approach as described in the main document for AP invoice... The only difference is that in this link you have to dynamically provide at least 2 different parameters: marked in red below:

      https:/xx.prc.em2.oraclecloud.com/procurement/faces/FndOverview?fnd=%252FWEB-INF%252Foracle%252Fapps%252Fprc%252Fpo%252FviewDocument%252FpublicUi%252Fflow%252FViewOrderMain.xml%2523ViewOrderMain%3BpoHeaderId%253D[P_PO_HEADER_ID]%253BpoRole%253DBUYER%253BallowActions%253DY%3BtabId%253D[P_PO_HEADER_ID]%3BPurchase%2BOrder%253A%2B[P_PO_NUMBER]%3Bfalse%3B256%3B%3B%3B&fndGlobalItemNodeId=itemNode_procurement_purchasing

      xx - provide your cloud address
      [P_PO_HEADER_ID] - dynamically replace this with your order id
      [P_PO_NUMBER] - dynamically replace this with your Order Number


      With respect
      Alexey