This discussion is archived
1 Reply Latest reply: Dec 14, 2012 1:08 AM by Sudhir_Meru RSS

Apex Report Scheduler in a Apex Mail

Sudhir_Meru Newbie
Currently Being Moderated
Hi,

I need to send report as a attachment everyday on a particular time with a scheduler. Need Experts Suggestion. Methods to do this with Apex.

>> I have Apex 4.1 and Oracle 11g XE


Thanks
Sudhir

Edited by: Sudhir_Meru on Dec 14, 2012 1:08 AM
  • 1. Re: Apex Report Scheduler in a Apex Mail
    jozef_SVK Journeyer
    Currently Being Moderated
    Hi,

    Try to look at: http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_util.htm#autoId20
    section: GET_PRINT_DOCUMENT Function Signature 4 / Example for Signature 4

    You can create some scheduled job (dbms_schedule.create_job) where you can call this procedure. I use it this way.

    Here is a part of my usage (code) - file is sent in pdf and xls frmat:
    -- set up cgi environment
      htp.init;
      l_cgivar_name(1) := 'REQUEST_PROTOCOL';
      l_cgivar_val(1)  := 'HTTP';
      owa.init_cgi_env(num_params => 1,
                       param_name => l_cgivar_name,
                       param_val => l_cgivar_val);
      --
      -- find workspace ID of application 
      for c1 in (select workspace_id
                   from apex_applications
                  where application_id = l_application_id)
      loop
        --
        l_workspace_id := c1.workspace_id;
        --
      end loop;
      --
      -- set security group ID, session ID, app ID globals 
      wwv_flow_api.set_security_group_id(l_workspace_id);
      apex_application.g_instance := wwv_flow_custom_auth.get_next_session_id;
      apex_application.g_flow_id := l_application_id;
      -- create authenticated session for l_user 
      wwv_flow_custom_auth_std.post_login( p_uname => l_user,
                                           p_session_id => null,
                                           p_flow_page => apex_application.g_flow_id||':'||1);
      -- set the application item value(s)
      apex_util.set_session_state('P1_X_COMPANY_R',p_company);
      -- set the email/report variables
      l_filename := 'Stav_na_sklade_'||apex_util.get_session_state('P1_X_COMPANY_R')
                    ||'_k_'||to_char(sysdate, 'DD_MM_YYYY');
      l_document := APEX_UTIL.GET_PRINT_DOCUMENT (
        p_application_id      => l_application_id,
        p_report_query_name   => 'monthly_stock_status',
        p_report_layout_name  => 'monthly_stock_status',
        p_report_layout_type  => 'rtf',
        p_document_format     => 'pdf');
      l_document_xls := APEX_UTIL.GET_PRINT_DOCUMENT (
        p_application_id      => l_application_id,
        p_report_query_name   => 'monthly_stock_status',
        p_report_layout_name  => 'monthly_stock_status',
        p_report_layout_type  => 'rtf',
        p_document_format     => 'xls');
      --
      for v in (SELECT e.send_to,
                       e.send_from,
                       g.body,
                       g.subject
                  FROM APEX_EMAIL e,
                       APEX_EMAIL_GROUP g
                 WHERE (INSTR 
                         (g.def_groups,
                         ',WH_FIN_STOCK_EMAIL_'||apex_util.get_session_state('P1_X_COMPANY_R')||','
                         ) > 0 and g.active = 'Y'
                       )
                   AND (INSTR (e.group_id, g.def_groups) > 0 and e.active = 'Y')
                 )
      loop
        --
        l_id := APEX_MAIL.SEND(
          p_to        => v.send_to,
          p_from      => v.send_from,
          p_subj      => v.subject||to_char(sysdate, 'DD.MM.YYYY'),
          p_body      => v.body,
          p_body_html => v.body);
        --
        APEX_MAIL.ADD_ATTACHMENT (
          p_mail_id    => l_id,
          p_attachment => l_document,
          p_filename   => l_filename||'.pdf',
          p_mime_type  => 'application/pdf');
        APEX_MAIL.ADD_ATTACHMENT (
          p_mail_id    => l_id,
          p_attachment => l_document_xls,
          p_filename   => l_filename||'.xls',
          p_mime_type  => 'application/vnd.ms-excel');
        --
      end loop;
      --
      apex_mail.push_queue;
      --
    exception ...
    In this case you could create some report query + report layout (instead of report layout you can use the generic column heading).

    Another solution is to create Subscription from Interactive Report menu. But i think that there is no option to set the input parameters dynamically.

    Regards
    J :D

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points