4 Replies Latest reply: Nov 19, 2012 5:05 PM by 603257 RSS

    Scheduling email in apex

    974898
      Hi all,

      i want to schedule an email in my application,that should deliver automatically when all the conditions are true.

      mail body is mentioned below :

      Dear Sir/Madam,<p>We have received the following compliance document on &nbsp <P_MONTH> <P_YEAR>:<p> 1.Declaration Letter for the month of <P_MONTH> <P_YEAR> <BR> 2.Resource list cum Contribution statement for the month of <P_MONTH> <P_YEAR> <BR> 3.PF remittance challan copy for the month of <P_MONTH> <P_YEAR> <BR> 4.ESI remittance challan copy for the month of <P_MONTH> <P_YEAR> <BR> <P>Thank you for the same and looking forward to your continued cooperation.<P> Thank you<BR> <p> Regards, <BR> Labour Law Compliance Team <p>

      Once all the 4 documents received from the vendor side then system should automatically send mail to vendor with above mentioned body.

      How can do this in apex?

      what are the steps involved for this scheduling?

      Thanks in advance,

      Anees
        • 1. Re: Scheduling email in apex
          evrm
          Hi Anees,

          you could use a combination of the packages APEX_PLSQL_JOB and APEX_MAIL to create a job to check if all the conditions are true before sending the mail.

          regards,
          Erik-jan
          • 2. Re: Scheduling email in apex
            Jitendra
            Hi anees,

            see the docs given below . it will helps you to understand or give you some idea about the sending mails

            Sending mails in apex.
            http://docs.oracle.com/cd/E14072_01/appdev.112/e12510/apex_mail.htm


            Scheduling a job
            http://www.apex-at-work.com/2009/06/dbmsscheduler-examples.html

            http://awads.net/wp/2011/02/02/25-unique-ways-to-schedule-a-job-using-the-oracle-scheduler/

            http://allthingsoracle.com/introduction-to-scheduled-jobs/



            Regards,
            Jitendra
            • 3. Re: Scheduling email in apex
              Mindmap
              Salam Anees,
              You might come across security issue. Please have a look at this example:
              create or replace PROCEDURE REMINDERS  IS
                      l_body    TABLE.MESSAGE%TYPE;
                      l_to        TABLE.email%type;
                      l_subj     varchar2 (100);
              BEGIN
              for c2 in (
                 select workspace_id
                   from apex_applications
                  where application_id = 100 )
              loop
                 apex_util.set_security_group_id(p_security_group_id =>
              c2.workspace_id);
              end loop;
              
              FOR C1 IN (SELECT * FROM TABLE
              WHERE TRUNC (REMINDER_DATE,'DD') <= TRUNC ( SYSDATE , 'DD') )
              
              loop    
                      l_body := 'Salam '||C1.MESSAGE ||','||utl_tcp.crlf;
                  l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;
                  l_body := l_body ||' Fateh'||utl_tcp.crlf;
                  l_to   := c1.email ;
                  l_subj := to_char( c1.REMINDER, 'HH24:MI "On" DD-Mon-YY');
                  l_subj := 'Client reminder @' || l_subj  ;
                  apex_mail.send(
                      p_to       => l_to,    
                      p_from     => 'Reminder@xxxxx.com',  
                      p_body     => l_body,
                      p_subj     => l_subj);
              end loop;
              END;
              Then you need to create a job:
              BEGIN
                  SYS.DBMS_SCHEDULER.CREATE_JOB (
                          job_name => '"FATEH",
                          job_type => 'STORED_PROCEDURE',
                          job_action => 'REMINDERS',
                          enabled => true,
                          auto_drop => false,
                          comments => NULL,
                          start_date    => trunc(sysdate)+8/24,    
                          repeat_interval => 'freq=DIALY' );
              END;
              • 4. Re: Scheduling email in apex
                603257
                Not being picky, just correcting this so as to avoid any confusion on the original poster's behalf if a simple copy/paste is done... :)
                repeat_interval => 'freq=DIALY' );
                should be
                repeat_interval => 'freq=DAILY' );