This discussion is archived
4 Replies Latest reply: Nov 19, 2012 3:05 PM by 603257 RSS

Scheduling email in apex

974898 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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' );

Legend

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