Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Comments

  • Mark daynes

    Nathan's reply is super comprehensive ... just to add something to that, it is possible to extract data from SaaS (using BICC or other methods such as OAC replicator, etc) and put it another DB and query that.

    Mark

  • Prateek Parasar

    Fusion SaaS comes with Rest services included. Whats your business scenario? Whats the requirement? 
    it depends what modules you have in your license, you can use Rest services for that module without any issues. 
     

    all listed Finance APIs here

    https://docs.oracle.com/en/cloud/saas/financials/19d/farfa/index.html

     

  • Beth Bleijie

    Thank you for your input, Nathan.

    Turns out it was an issue in the download process: when a Command Prompt opens there should be NO activity by the user while it runs.  It is easy to click for the next step, or multitask with something else while it is running, but apparently that stops it from running.

    So when a screen comes up with a command prompt (black screen, like the old DOS screen on early computers) the user need to wait until that it truly finished running.  At least that's the way it finally worked for us.

    Regards,

    Beth

  • Santosh Kumar Bhairi

    thank you  Leigh for taking time to provide it. 

  • Leigh Holling

    Note in the Lookup if the BI_URL is there so that you can also drill to other subreports should you need to however it is not needed to purely drill to financials,

  • 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 :)

  • Santosh Kumar Bhairi

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

  • 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,

  • nathan morgan (at Client)

    Is publisher report with data model using physical sql query also working after execute direct database request privilege is denied at 20A?

    Yes. The same readiness announcement also says

    "This change does not affect the SQL capabilities in BI Publisher."

    https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/r13/wn/common/releases/19D/19D-common-wn.htm#F10106

  • nathan morgan (at Client)

    Hi,

    To create a data model, so that you get the yellow "Data Model" icon on the new menu in analytic, you need role "BI Publisher Data Model Developer". See attached the same physical query, on in an analysis, one in a data model/report, the analysis does not work but the report does,

  • Mirza Adeel Baig

    Dear ,

    Is it also working on 20A when update?

    Thanks

  • nathan morgan (at Client)

    The privilege Edit/Execute Direct database request only impacts what an "Analysis" can do. Try it out. Build an analysis with a direct database request to select hello world from dual using one of the connection pools that connects tot he database. Click New/Data Model then click the plus to add a dataset of type "SQL query"  and use the same database connection to do the same thing (do not build the data model based on the analysis from the catalog). Update the manage privileges to denied authenticated user. The analysis will now throw an error but the data model will not? 

  • Mirza Adeel Baig

    Dear ,

    Thanks alot for information, Means in 20A we will not able to create direct database query. So how can i used sql query using the physical database connection to the database server in a data model for a publisher report. 

    Thanks

  • nathan morgan (at Client)

    Sridhar, To second what Ali said here is a bit from the manual regards you wanting to "Refresh" but be careful about "Rebuild"

    Oracle Financials Cloud Implementing Enterprise Structures and General Ledger 19D F22288-01
    https://docs.oracle.com/en/cloud/saas/financials/19d/faigl/ledgers.html#FAIGL820477

    When do I rebuild the GL balances cubes?
    Carefully consider requirements and work with Oracle Support before rebuilding a balances cube. Use the on-demand programs to rebuild dimension members for each dimension and to refresh balances to the cubes rather than rebuilding the cube. When you rebuild a cube, the process rebuilds both the standard and average balances cubes.To rebuild cubes, run the Create Cubes process. 

    How can I refresh balances in the GL balances cubes?
    Run the General Ledger Transfer Balances to Essbase process. Select your Ledger or Ledger Set and Starting Period as parameters

     

  • nathan morgan (at Client)

    Hi, I think you need to run using "Navigator/Tools/Scheduled Processes" the job "Generate Summary Metrics for Expenses" to populate data into the Subject Area ("Expenses - Employee Expense Overview Real Time"). Give it a go.