We have seen similar issues when there are a lot of SQL quick reports in the environment and they are modified frequently. In this version of IFS Applications, Aurena use one projection for all SQL quick reports. This is required to view any SQL quick report. This is a dynamic projection created in runtime based on all the SQL quick reports in the system. First time it would require to build this projection and this happens when the first user tries to view a quick report. This building process involves parsing all the SQL queries in all the SQL quick reports - so this can take several minutes to complete. Until this is built no users will be able to view any quick reports in the system. Anyway once the projection is built this will be cached in the IFS middleware server and used by all the users for all the SQL quick reports - so it will be quicker then. 

The above cache will be cleared if the IFS middleware server is restarted. Or else if there is a modification/addition/deletion to at least one SQL quick report this cache gets invalid and then when the next user tries to view a SQL quick report it will be rebuilt. Therefore it will take some time for the user and others to view any SQL quick report in the environment. 

For any reason if this build process takes too long, all the users can see this problem. 

We understand this can be a problem and the underline design is being changed. As the very first step, we have changed the design to have a unique projection for each SQL quick report. This will then remove the need to rebuild the cache when a different SQL quick report is changed. This will be coming with our next core release IFS Cloud. In the APP10 version the available solutions would be to reduce the number of SQL quick reports as much as possible and avoid quick report modifications during peak production hours. New additions/deletions/modifications to quick reports can be done in a non production environment and then bring them to production during a scheduled downtime. As part of this process the administrator can attempt to view one of the SQL quick reports with the expectation that the quick reports related projection is built in the IFS middleware server and kept to be used by other users during peak production hours. 

if I initiate a

/main/ifsapplications/projection/ClearCache?METHOD=CLEAR_PROJECTION_CACHE&VALUE=*

how long does this take?

If I want to restrict this to just the sql quick report projection, what is its name so that I can replace the * ?

Hi,

The mentioned URL will clear the projection cache in middleware server. It will only take seconds to clear that. However it can take several minutes to build the cache again - depending on the size of the projection, database performance, and how much data it needs to access from the database. 

The name of the quick report related projection is QuickReports. Hence the URL can be changed as per below. 

/main/ifsapplications/projection/ClearCache?METHOD=CLEAR_PROJECTION_CACHE&VALUE=QuickReports

If you execute the above URL in your environments, the first user to view/order the SQL quick report must face the issue as the cache is missing in your middleware server. Then once the cache is rebuilt, issue will be resolved for some time. 

This issue is happening more often as we develop more sql quick reports and I have to refresh the cashes daily

What are the chances of the cloud fix being made available for APPS10?

Hi @Rusiru Dharmadasa,

This particular issue is happening in one of our reference environments also. 

Environment: http://ew/Environment/Detail?envid=11601

Product Version: IFS10 UPD19

Test steps:

1. From the Order Report page in Aurena, run OrderReport on a report with Report Type = SQL statement selected.
2. Then you are taken to the Quick Report page, but no data is displayed and got the following error.
3. In the IEE version, data is retrieved when the same report is specified and executed (Quick Reports → Order Report)

ERROR:

An internal server error occurred. Contact administrator.

Url: https://cmbgse1916.corpnet.ifsworld.com:48080/main/ifsapplications/projection/v1/QuickReports.svc/QuickReport_872098(='')?$skip=0&$top=25

Request Id = 018e792f-c4b1-4722-b480-636379e82153
Date = 2023-04-19T07:39:42.885Z
Client version = 10.19.139.0

SQL Query for the quick report:

select * 
FROM INVENTORY_PART IP_KASO 
WHERE IFSAPP.REPORT_SYS.PARSE_PARAMETER(IP_KASO.CONTRACT , '& --S--]ｻｲﾄ') = 'TRUE'

But for some other SQL type quick reports, I didn’t see this error. 

Questions:

1. Is it due to same issue that you have mentioned above?
2. Is it due to an error in quick report?

Hi @Rusiru Dharmadasa,

Following is the detailed information for above error message:

{
  "error": {
    "code": "ODATA_PROVIDER_ERROR",
    "message": "An internal server error occurred. Contact administrator.",
    "details":  
      {
        "code": "SYNTAX",
        "message": "The URI is malformed."
      }
    ]
  }
}

This is not the same error we discussed on this topic @ashen_malaka_ranasinghe 

Since the error says about a malformed URI, I suspect it is coming from (='') part in the given URI. 

Create a new SQL quick report with a simple query and see if it gets the same issue? 

@Rusiru Dharmadasa, I created a new SQL quick report with following simple query and didn’t get above mentioned error. Seems like above mentioned error is specific to that particular report.

SQL query that I have used:

SELECT * FROM ifsapp.company_person t where t.company_id='10'

RnD has informed us that, this issue is fixed in APPS10 UPD20 @Rusiru Dharmadasa.