1 Reply Latest reply: Dec 14, 2012 3:08 AM by Sudhir_Meru RSS

    Apex Report Scheduler in a Apex Mail

    Sudhir_Meru
      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
          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