This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: May 9, 2009 1:59 AM by Sunil Bhatia Go to original post RSS
  • 15. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    yes andy,

    actually want to send the email at this instant only. But as in other blog, just want to save the sent items and the data sent in the emails. Dont want this functionality to send email later or once in a day. I just want to send email as soon as the button send email hits, and if any of the checkbox is not selected, simple error message in alertbox or any other alternative comes which says "no employee selected"

    Thanks andy
    Sunil Bhatia
  • 16. Re: Incorporating Tree
    ATD Guru
    Currently Being Moderated
    OK

    I'll work on the basis that a table is just for an email history

    Andy
  • 17. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    thanks dear for this awesome help of urs

    Sunil Bhatia
  • 18. Re: Incorporating Tree
    ATD Guru
    Currently Being Moderated
    Hi,

    np - I will need an email address to send the emails to. Either post one here or send one to ................

    Andy
  • 19. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    hi andy,

    i will agree if u can send it to ur email id, because i will be unable to check my personal email here. Still if u cant, u can send it to "sun.bhatia21@gmail.com", bt if you can send email to ur personal id and test there, and here i will replace ur personal id to my official id. Will that work??? Sorry dear,

    Thanks
    SunilBhatia
  • 20. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    ok....

    i have talked to network department. If u r not testing on ur email id, u can test it on sun.bhatia21@gmail.com

    Sorry for late reply,

    Sunil Bhatia
  • 21. Re: Incorporating Tree
    ATD Guru
    Currently Being Moderated
    Hi,

    OK - I'll send emails to my own account to test things. I can't let you have access to that. Once tested, do you want me to switch to your gmail account?

    Andy
  • 22. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    hey andy,

    its fyn, if the testing of mails is successful at ur email id, then it will work fine for me too. I just want how u did it, i mean the actual coding of how u looped through on all checkboxes and the table structure based on which history saved thing was done. Thanks for so much help.

    Thanks
    Sunil Bhatia
  • 23. Re: Incorporating Tree
    ATD Guru
    Currently Being Moderated
    Hi,

    OK - the functionality works ok. I've just sent one email and have now forwarded this to you at your gmail account.

    Andy
  • 24. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    yupzzz

    i have recieved an email on my account. its working fine, you have sent 1 email, thats it?? or multiple emails?

    With Regards,
    Sunil Bhatia
  • 25. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    hi andy,

    Whenever possible at your end, do tell me how u implemented these things at your end, plus the table structure which u did for it.

    I will implement it at my end, and will reply after implementing it. Please have your lunch, as you are with me right from the morning, and i really appreciate your help

    Thanks a lot andy,
    Sunil Bhatia
  • 26. Re: Incorporating Tree
    ATD Guru
    Currently Being Moderated
    Hi,

    OK - I've updated the page to include a field for you to enter in an email address. Try the page, enter in your own email address and then check whatever employees you like.

    I have just done this and received several emails.

    Andy
  • 27. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    hi andy,

    yups i have tested it,

    it is working absolutely fine at my end. All the emails are perfect.

    Brilliant andy

    Regards,
    Sunil Bhatia
  • 28. Re: Incorporating Tree
    ATD Guru
    Currently Being Moderated
    Hi Sunil,

    Yeah! OK - I've hidden the email addresses in the report so users can not see these.

    This is what I've done on the page.

    Firstly, I have created the following in SQL (this was uploaded as a script and then run):
    CREATE TABLE "SENT_EMAILS"
    (
         "SENT_EMAIL_ID" NUMBER(10,0) NOT NULL ENABLE,
         "EMPNO" NUMBER(4,0),
         "EMAIL_TYPE" VARCHAR2(4),
         "EMAIL_TO" VARCHAR2(200),
         "EMAIL_FROM" VARCHAR2(200),
         "EMAIL_BODY" VARCHAR2(2500),
         "EMAIL_BODY_HTML" VARCHAR2(2500),
         "EMAIL_SUBJECT" VARCHAR2(200),
         "EMAIL_CC" VARCHAR2(200),
         "EMAIL_BCC" VARCHAR2(200),
         "EMAIL_SENT" DATE,
         CONSTRAINT "SENT_EMAILS_PK" PRIMARY KEY ("SENT_EMAIL_ID") ENABLE
    )
    /
    CREATE SEQUENCE "SENT_EMAIL_ID_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE
    /
    CREATE OR REPLACE TRIGGER "SENT_EMAILS_BI"
    BEFORE
    INSERT ON "SENT_EMAILS"
    FOR EACH ROW
    DECLARE
         v_id NUMBER;
    BEGIN
         BEGIN
              IF :NEW.SENT_EMAIL_ID IS NULL THEN
                   SELECT SENT_EMAIL_ID_SEQ.nextval INTO v_id FROM dual;
                   :NEW.SENT_EMAIL_ID := v_id;
              END IF;
         END;
    END;
    /
    ALTER TRIGGER  "SENT_EMAILS_BI" ENABLE
    /
    This just creates by SENT_EMAILS table and related objects.

    I've added a new item on my page called P76_EMAIL_ADDRESS - this is just so that I don't have to hardcode an email address into the process (otherwise, I'm likely to receive loads of test emails!)

    Then, on my page, I created a new PL/SQL process called P76_SEND_EMAILS. The process is set to run "On Submit - After Computations and Validations" and uses the following Process code:
    DECLARE
     vSELECTED VARCHAR2(1000);
     vEMPNO NUMBER;
     vENAME VARCHAR2(100);
     vEMAIL VARCHAR2(100);
    BEGIN
     vSELECTED := APEX_UTIL.TABLE_TO_STRING(APEX_APPLICATION.G_F01);
     :P76_SELECTED := vSELECTED;
     IF APEX_APPLICATION.G_F01.COUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'No employees selected!');
     END IF;
     vEMAIL := :P76_EMAIL_ADDRESS;
     IF vEMAIL IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, 'Email address required');
     END IF;
     FOR x IN 1..APEX_APPLICATION.G_F01.COUNT
     LOOP
      vEMPNO := APEX_APPLICATION.G_F01(x);
      SELECT ENAME INTO vENAME FROM EMP WHERE EMPNO = vEMPNO;
      INSERT INTO SENT_EMAILS (EMPNO, EMAIL_TYPE, EMAIL_TO, EMAIL_FROM, EMAIL_BODY, EMAIL_BODY_HTML, EMAIL_SUBJECT, EMAIL_CC, EMAIL_BCC, EMAIL_SENT)
      VALUES (vEMPNO, '76', vEMAIL, vEMAIL, 'Email for ' || vENAME || ' EMPNO ' || vEMPNO, 'Email for ' || vENAME || ' EMPNO ' || vEMPNO, 'Test email', null, null, SYSDATE);
      HTMLDB_MAIL.SEND
      (
      vEMAIL,
      vEMAIL,
      'Email for ' || vENAME || ' EMPNO ' || vEMPNO,
      'Email for ' || vENAME || ' EMPNO ' || vEMPNO,
      'Test email',
      '',
      ''
      );
     END LOOP;
     APEX_MAIL.PUSH_QUEUE();
    END;
    The vSELECTED lines are not required for normal use - this was just to get a string containing the selected EMPNO values for display purposes. You can remove these lines.

    The code first checks if there are any items checked by the user - if there are none, an error is raised. This displays on an error page - the user has to click OK on that page to return to the tree. This stops any further code from running.

    It also checks for a value in P76_EMAIL_ADDRESS - though this could be done using Validation if required.

    If there are items checked, the next part loops through these and gets the EMPNO (as the checkboxes have a "value" attribute that is the EMPNO for each item, this is the value submitted and stored in the APEX_APPLICATION.G_F01 collection.

    We then use the EMPNO value to retrieve the ENAME for the employee.

    Finally, a record is added into the SENT_EMAILS table and the email itself is constructed and put into the email queue. Finally, the queue is pushed to trigger the actual sending of the emails by the server.



    Andy
  • 29. Re: Incorporating Tree
    Sunil Bhatia Journeyer
    Currently Being Moderated
    oh wow andy,

    will reply in 5-10 min as i am trying to do the steps which you have taken.

    Thanks a lot

    Sunil Bhatia

Legend

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