Can you specify which Report this is about (Report ID/View) ?

Most, if not all, of the REP views have security access set up based on, in particular, on ifsapp.archive_distribution, to ensure you can access the particular result key in that view.

This means you must be either the Report Creator (Ordered By yourself), or for your user account to be granted ADMINISTRATOR system privileges (in the permission sets) in order to be able to query any result key from those views.

Can you confirm that this is the case for the Oracle user you’re using to query for your select statement, either in PLSQL or the SQL Query Tool in the EE Client?

I assume you do have perms, because the same condition is applied on the Report Archive, and if you can see the report archive entry it should mean you have access to the archive distribution, but you never know, if for some reason you’re using a different user to do your Select Statement than you are in the App to check the report archive :)

The same applies when being done in Quick Reports, by the way. That code in Quick Report will only give results if the result key distribution is granted to whichever user executes the quick report.

For example if you create a Quick Report that does

select * from ifsapp.purchase_order_print_rep where result_key = ‘&result_key’

Users would only see results in that report if they are personally granted access to that Result Key, or if they have the ADMINISTRATOR system privilege.

This does mean the Quick report would have very little use to be honest, if you’re hoping to let regular end users (who typically don’t have ADMINISTRATOR System Privileges) use it to export data from result keys they have not generated themselves

Hi @SimonTestard, I really appreciate your detailed response. 

I’m trying to access data of FA_TRANSACTION_REASON_REP and logged in as IFSAPP user.
I tried the select query in PL/SQL Developer but doesn’t show the latest data.
If I just execute ‘select * from FA_TRANSACTION_REASON_REP’ it fetches old data.

I’m ordering report as IFSAPP but don’t see data for newly ordered reports.

(I’m using cloud application 23R1)

Thanks again 

Ok so I’ve investigated this, and I think this report has specific behavior that is going to make what you want to do fairly difficult if not impossible.

First of all, FA_TRANSACTION_REASON_REP simply reads from INFO_SERVICES_RPV

There’s not any specific filter on it so it’s actually going to return ANY records from that view.

That view is actually a generic view for report distribution, using a generic table to record report data, I think this is the old way of doing things where reports didn’t get their own report table, instead they were written into a common report table (INFO_SERVICES_RPT), with views defining the column names (somewhat like the External File feature, if you’re familiar with that).

You can see here the names s1/s2 etc, these are generic columns, with “s” being used to denote a string, “n” for a number, “d” for a date.

So “s1” is effectively the first string column in the table INFO_SERVICES_RPT.

Reports typically write into either INFO_SERVICES_RPT for “generic” reports, or their own dedicated Report Table (for example Customer Order Confirmations (CUSTOMER_ORDER_CONF_REP) writes through Customer_Order_Conf_RPI into the table ORDER_INFO_SERVICES_RPT)

The specific report you’re talking about though has very specific behavior though, because it simply doesn’t write to any “final” RPT table at all, it writes to a TEMPORARY table under some conditions to pregenerate the XML data in case of multi transactions:

It then it purely generates the XML data and records that through a loop (Typically reports do both, they generate the XML data but also record the result set in the report tables)

However, at no point does the procedure write data into INFO_SERVICES_RPT.

In short, this particular report does not create a permanent result key set that is available from any view, and is purely XML based. (This means it would be difficult/impossible to create a Layout using Crystal Report, for example).

I can confirm this behavior on my own instance as the same thing happens, the report prints fine, I get the XML dataset, but I don’t have any result key entry for that report in any view, simply because that data has never been written into any table to be used.

I hope that helps, I know that’s not really the answer you likely wanted to hear, but basically what you’re attempting to do is simply impossible as it stands in IFS, you won’t be able to return data from that report in any other fashion than XML, sorry :(

If you’re adept at manipulating SQL, you could theoretically grab the XML data from the Report Archive and retranspose/pivot that into a table format, but that’s quite involved, good luck if you want to that (https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql) !

Hi @SimonTestard,

Thank you so much for taking time to investigate this and explaining this why it’s impossible to do.

Do you have any idea to get the report data out as an excel sheet, please?

I really appreciate your help with this.

Theoretically, Office is straight compatible with XML. If you just go to the Report Archive, Save the XML Data, and Open it with Excel, you’ll get the following (Excel might make you answer a few questions about schema and so on, I just clicked left the defaults/clicked yes on all of the questions):

The columns you probably care will be on the right (I’m not showing that cause it’s confidential information)