Scheduled Quick Report rule

Hi ​@Emerald,

Have you verified if the report generates values when ordering it through the Order Report window?

If the report generates values using that approach, it indicates a potential issue with the scheduling option that requires further investigation.

Maybe you are able to turn the reports (or parts of it) into events. An event can easily detect if an anomaly is there and a mail should be send.

Bear in mind that using SQL in an event will be depricated in future. The answer to that is Workflow. A tough cookie to crack for this, but it must be possible. It will become easier if you prepare a modification (API) that can handle the monitoring and send the mails.

Hi Eli,

I did a quick research on my MS SQL DWH environment.
If you have a MS SQL Server you can do the following.

Create a Log-Table.
Use the ORACLE SQL to import Data into this Log-Table (via LinkedServer - Settings)
Look for records and if they exist, use MS mail for the SQL Server.

We went this way with a OCC = OrphanedChildCheck and a
BiForUs_OrphanedChildCheck_Analyse_Tabelle 

So you can a) inform email recipients and b) have a log-file/table to see what happend in the past.

If that sound intersting, let me know.

Here a piece of code for LinkedServer.
select *
from openquery(IFS,
'SELECT *
from CUSTOMER_ORDER_TAB
'
)

select *
into your logtable
from openquery(IFS,
'SELECT *
from CUSTOMER_ORDER_TAB
'
)
WHERE CUSTOMER_NO not in
(select CUSTOMER_ID
from openquery(IFS,
'
(SELECT CUSTOMER_ID from CUSTOMER_INFO_TAB)
'
)
)

This will return no data so everything is ok.
If there is a record (CUSTOMER_NO in Orders but not in Customer)
send mail from MS SQL Server.

Hope that brings some new ideas.

All the best

Michael

​@Michael Kaiser Does this work in an IFS Cloud solution (so database at the IFS Cloud provider) and using BuildPlace? So not on premise, I mean.

Hi Steve,
yep, it will work in cloud environment as well. The ORACLE data structure is 99% the same like in the on prem version. (TEXT_ID$ is missing in some tables, that’s they only difference I found so far)

The only problem is to get a ORACLE connection up and running.
One CH customer needed 6 weeks for this issue. :-(
Other customers install an ORACLE client on the BI Server and - voilá - you can use LinkedServer settings within one or two days.

But when your connection is ready, you can read (take care of that!!!) all ORACLE data from your IFS DB.

hope that helps.

BR

Michael