Question

quick report scheduling

  • 15 October 2019
  • 19 replies
  • 2799 views

Userlevel 3
Badge +6

How I can schedule quick report on particular day of month and share same to the user in a mail? 


19 replies

Userlevel 4
Badge +7

We have set up the emailing of quick reports as Application Server tasks. One application server task per quick report to be scheduled. 


Job Type: Quick Report


Queue: Batch2


Select the Quick Report to be scheduled


List the email receivers in the field Email Address.


Note the Application Message ID received and schedule it via Scheduled Tasks:


Task Name: Run Application Server Task


Application Message ID: The ID of the application message created in Application Server Task. 

I tried this but the job never runs it is always in waiting 

Userlevel 5
Badge +8

 

Not every solution I have read here is quite complex and simple. The simple way to do this is to create a procedure with the sql of the quick report and run this procedure with DBMS_Job and send an e-mail to the person you want.

 

 

PROCEDURE PROCEDURE_NAME( paramater1_ IN VARCHAR,paramater2_IN VARCHAR)
IS

parameter_attr_    VARCHAR2(2000);
      result_key_        NUMBER;
      report_attr_       VARCHAR2(2000);
      msg_               VARCHAR2(32000); 
      print_job_id_      NUMBER;
      attr_              VARCHAR2(4000);
      date_              DATE;
      attach_            VARCHAR2(2000);
      text_              VARCHAR2(32766);
      subject_           VARCHAR2(2000);
     requisitioner_ VARCHAR2(2000);
     mail_adress_  VARCHAR2(2000);
      
   BEGIN
 
IFSAPP.Log_SYS.Init_Debug_Session_('tr');
    requisitioner_ := requisitioner_code_;
    mail_adress_ := mail_;
      subject_ := 'Bir gün önce iptal edilen sipariş satırlarınız mevcut';
    
       text_ := rpad('=', 90, '=') ||' BİR GÜN ÖNCE İPTAL EDİLEN SATINALMA TALEP SATIRLARINIZ ' || rpad ('=', 90, '=')||chr(13)|| chr(10)||chr(13)|| chr(10)||chr(13)|| chr(10);
       text_ := text_ ||  rpad('|  '||'Sipariş No',  15)||chr(9) || 
                                   rpad('|  '||'Satır No', 15)||chr(9) || 
                                   rpad('|  '||'Yayin No', 15)||chr(9) || 
                                   rpad('|  '||'Miktar', 15)||chr(9) || 
                                   rpad('|  '||'Env Ö/B', 15)||chr(9) ||
                                   rpad('|  '||'Sat Ö/B', 15)||chr(9) || 
                                   rpad('|  '||'Satınalmacı', 20)||chr(9) || 
                                   rpad('|  '||'Talep No', 15)||chr(9) ||
                                   rpad('|  '||'Islem_Saati', 25)||chr(9) ||chr(9) ||
                                   rpad('|  '||'Not', 40)||chr(9) ||chr(9) ||
                                   rpad('|  '||'Malzeme No', 13)||chr(9) ||
                                   rpad('|  '||'Malzeme Tanım', 43)||chr(13)|| chr(10);
       text_ := text_ || rpad('_', 230, '_')||chr(13)|| chr(10);
      FOR rec_ IN (
                            
             --YOU SQL BLOCK--
             
             
               ) LOOP  
            
        result_key_ := NULL;
         Client_SYS.Clear_Attr(report_attr_);
         Client_SYS.Clear_Attr(parameter_attr_);
            
         text_ := text_||                  rpad('|'||rec_.COLM1, 15)||chr(9) ||
                                                    rpad('|'||rec_.COLM2 , 15) || chr(9) ||                 
                                                    rpad('|'||rec_.COLM3,15) ||chr(9) ||
                                                    rpad('|'||rec_.COLM4,15) ||chr(9) ||
                                                    rpad('|'||rec_.COLM5,15) ||chr(9) ||
                                                    rpad('|'||rec_.COLM6,15) ||chr(9) ||
                                                    rpad('|'||rec_.COLM7,20) ||chr(9) ||
                                                    rpad('|'||rec_.COLM8, 15)||chr(9) ||
                                                    rpad('|'||rec_.COLM9, 25)||chr(9) ||
                                                    rpad('|'||nvl(rec_.note_text,'Not Bilgisi Girilmemiştir.'), 40)||chr(9) ||
                                                    rpad('|'||rec_.COLM1, 13)||chr(9) ||
                                                    rpad('|'||rec_.COLM1,43)||chr(9) || chr(13) ||chr(10);
         END LOOP;  
        text_ := text_ || rpad('_', 230, '_')||chr(13)|| chr(10)||chr(13)|| chr(10);
                                                    
    Command_SYS.Mail(
            from_user_name_ => 'MAIL USER NAME ',
            to_user_name_ =>mail_,
            cc_ =>   'CC USER MAİL ADRES ',
            text_ => text_ ,
            attach_ => attach_,
            subject_ => subject_ );                       
   General_SYS.Init_Method(Sfn_Bt_Tcr_To_Ask_Util_API.lu_name_, 'API_NAME', 'PROCEDURE_NAME');
      commit;
      null;
END PROCEDURE_NAME;

Userlevel 2
Badge +3

@brward you can use this link instead. http://ifsappsrvprd01.australiasoutheast.cloudapp.azure.com:58080/ifsdoc/f1doc/default.htm

 

I am also trying to save QR according to a schedule. I can do this easily using PLSQL, writing new API. But I am still trying to make this possible the standard way.

My question is, how can we use the QR after sending its response to BizAPI: RenderExcelQuickReport.

https://community.ifs.com/service-all-about-taking-care-of-your-customers-43/scheduling-quick-report-for-save-using-application-server-task-1609

 

Badge +1

How can we see this link? I tried and says contact your web administrator. I used my portal login but it will not work with this line.

Can be found in IFS Administration & Configuration Guide → Reporting → Ad-Hoc Reporting → Scheduled Quick Report

https://wit.ifsworld.com/f1docs/apps9/Foundation1/040_administration/250_reporting/020_adhoc_reporting/030_scheduled_quickreports/default.htm

You have to use your technical URL instead of wit.ifsworld.com for anyone that needs it.

Badge +1

How can we see this link? I tried and says contact your web administrator. I used my portal login but it will not work with this line.

Can be found in IFS Administration & Configuration Guide → Reporting → Ad-Hoc Reporting → Scheduled Quick Report

https://wit.ifsworld.com/f1docs/apps9/Foundation1/040_administration/250_reporting/020_adhoc_reporting/030_scheduled_quickreports/default.htm

Userlevel 4
Badge +7

You can use Oracle’s Utl_Smtp procedure

In Apps 8 and Apps 9, you can use COMMAND_SYS.MAIL.


HTML email can be configured under “Setup IFS Connect”. Under “Connector Senders”, you can change the “CONTENT_TYPE” from “text/plain” to “text/html”. This affects all email for that sender.

 

(I have no experience with using multiple email senders, but I’d imagine that would be the right work-around if you want to send plain text and HTML from the same system.)

I’m aware of the html sender, and also have used command_sys,mail extensively. The answer is more about using an alternative method that has the capacity to create a file on the fly and attach to the email, plus having an html body.

Userlevel 7
Badge +18

You can use Oracle’s Utl_Smtp procedure

In Apps 8 and Apps 9, you can use COMMAND_SYS.MAIL.


HTML email can be configured under “Setup IFS Connect”. Under “Connector Senders”, you can change the “CONTENT_TYPE” from “text/plain” to “text/html”. This affects all email for that sender.

 

(I have no experience with using multiple email senders, but I’d imagine that would be the right work-around if you want to send plain text and HTML from the same system.)

Userlevel 4
Badge +7

How I can schedule quick report on particular day of month and share same to the user in a mail? 

Following your query, as I have a similar requirement and looking for alternatives ways to do this.

I need a scheduled report to be exported as an Excel report and emailed to the user. 

You can use Oracle’s Utl_Smtp procedure to convert a cursor (SQL Statment) and create it as an attachment like .csv and email it out. Use a dbms scheduler job to run in required schedules. Positives of this method is you can embed html body and highly configurable including the schedule (ex. never email on a public holiday). 

Badge +1

Problem might be in machine to machine communication limits in SMTP server.

Your machine “IFS” try to call SMTP machine Z over the network area. Most of these kind of connections are blocked cause it’s basic spamming method.

You need to contact your SMTP administrator and ask how to find solution.

Badge +2

How I can schedule quick report on particular day of month and share same to the user in a mail? 

Following your query, as I have a similar requirement and looking for alternatives ways to do this.

I need a scheduled report to be exported as an Excel report and emailed to the user. 

Userlevel 3
Badge +6

Thanks Amy! 

I tried both options with external and internal emails. 

Userlevel 1
Badge +1

Are you trying to relay to an external email address?  Often times, mail servers restrict some external mail flow, so you may need to allow that for the message to process.

Userlevel 5
Badge +9

The error says Invalid Addresses. How have you entered the email addresses in the application server task?

Userlevel 3
Badge +6

I have scheduled Report but experiencing below error in Application message. Can you please advise:

Error while sending data... 
Caused by: javax.mail.SendFailedException: Invalid Addresses 
Caused by: com.sun.mail.smtp.SMTPAddressFailedException: 550 5.7.1 Unable to relay for rajendra.gupta@

Userlevel 5
Badge +9

We have set up the emailing of quick reports as Application Server tasks. One application server task per quick report to be scheduled. 


Job Type: Quick Report


Queue: Batch2


Select the Quick Report to be scheduled


List the email receivers in the field Email Address.


Note the Application Message ID received and schedule it via Scheduled Tasks:


Task Name: Run Application Server Task


Application Message ID: The ID of the application message created in Application Server Task. 

Userlevel 3
Badge +6

Yes, I have gone through with the mentioned document. Let me further investigate in the issue. 

 

Userlevel 6
Badge +15

Have you looked into the functionality mentioned in the Admin documentation to schedule the quick report? 

Can be found in IFS Administration & Configuration Guide → Reporting → Ad-Hoc Reporting → Scheduled Quick Report

https://wit.ifsworld.com/f1docs/apps9/Foundation1/040_administration/250_reporting/020_adhoc_reporting/030_scheduled_quickreports/default.htm

https://wit.ifsworld.com/f1docs/apps10/foundation1/040_administration/255_br_and_a/005_adhoc_reporting/030_scheduled_quickreports/

Userlevel 3
Badge +6

I created scheduled DB task and email also specified in thay task only. Even application message successfully provessed. But e mail is not triggered. Let see if any other option to fulfill the request. 

Userlevel 4
Badge +9

There might be an easier Way, but I would believe  a scheduled database task would be able to take care of the scheduling bit.

but i am not sure whether there is a feature for emailing a quick report which said scheduled database task could then run or If you would have to create an event/event action triggered by the scheduled database task. 

Reply