1 2 Previous Next 15 Replies Latest reply on Jan 23, 2014 7:20 PM by jwellsnh

    Sending emails for each record from tabular form

    gjones77

      I currently have a requirements management tabular form that used to update or set job requirements inactive and/or covered.

       

      We're a staffing agency and have salesmen across the country that will use this tabular form to quickly manage their requirements to mark them as covered or inactive if the position has been filled.

       

      The multi-row update works fine since the form was built using the wizard, but I need to be able to send out an email for each record that's been marked as covered in the tabular form.

       

      How can this be accomplished?

       

      I'm running Oracle 12c and Apex 4.2.0 on a windows 2008 R2 server.

       

      Thanks again.

        • 1. Re: Sending emails for each record from tabular form
          jwellsnh

          Reviewing the documentation is a good place to start.  Afterwards you will need to decide how and where this process will be initiated and by whom.

           

          See - http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_mail.htm#CIHDIEJI

           

          Jeff

          • 2. Re: Sending emails for each record from tabular form
            gjones77

            Jeff,

             

            I already have a process on another form that does just that, sending out an email for a single record from a forms page isn't what I'm looking for.

             

            I need to be able to do it from a Tabular Form.

             

            I don't know how to reference the individual rows and columns and insert that info into the email.

             

            To give an example:

             

            A salesman goes to the tabular form page and checks off 5 requirements and marks those 5 as "Covered", meaning they received a resume for that position.

             

            Now, once the page is submitted I want it to send out 5 different emails referencing each individual row's information.

             

            Here's the current email process for the normal form page we have, it's set to fire off once they click a button and the page is processed.

             

            DECLARE

            l_id number;

            l_index number;

            l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;

            lc_Message VARCHAR2(4000);

            BEGIN

            lc_Message :=               'Date Written   :' || :P2_DATE_WROTE || chr(10);

            lc_Message := lc_Message || 'Sales          :' || :P2_SALES|| chr(10);

            lc_Message := lc_Message || 'Client         :' || :P2_CLIENT|| chr(10);

            lc_Message := lc_Message || 'Position       :' || :P2_JOB|| chr(10);

            lc_Message := lc_Message || '#1 or #2       :' || :P2_1OR2 || chr(10);

            lc_Message := lc_Message || 'Rate           :' || :P2_RATE || chr(10);

            lc_Message := lc_Message || 'Notes      :' || :P2_NOTES || chr(10);

             

            l_id := APEX_MAIL.SEND(

            p_to => 'reqs@someemail.com',

            p_from => 'DO_NOT_REPLY@REQS',

            p_subj => '' || :P2_WHO|| ' Has Covered ' || :P2_JOB|| ' at ' || :P2_CLIENT|| chr(10),

            p_body => lc_Message);

             

            COMMIT;

            APEX_MAIL.PUSH_QUEUE();

            END;

            • 3. Re: Sending emails for each record from tabular form
              jwellsnh

              APEX_APPLICATION.G_F01, F02 etc are global variables in the apex_application package that are defined as arrays.  Your process will need to use this package and a FOR LOOP to rip through the tabular form rows and set you local variables prior to sending the email.  Something like this:

               

                 FOR i IN 1 .. APEX_APPLICATION.g_f01.COUNT
                 LOOP
                    with column 1 = G_F01, column 2 = G_F02 etc etc.
                 END LOOP;
              

               

              Jeff

              1 person found this helpful
              • 4. Re: Sending emails for each record from tabular form
                gjones77

                Jeff,

                 

                I really appreciate the help, I'm just unsure how I would incorporate that into an email process.

                 

                I understand using the apex_application, just unsure on how that's structured within a process.

                 

                Keep in mind, I'm not a developer at all, just very good with google and computers, just enough to be dangerous.

                • 5. Re: Sending emails for each record from tabular form
                  jwellsnh

                  I am leaving work right now but maybe we can figure this out together.  I need more information about your tabular form.

                   

                  What are the columns of data on the form and what are the business rules regarding with rows of the form are to generate an email?

                   

                  I may be able to pick this back up late tonight from home; if not we can tackle this tomorrow.

                   

                  Jeff

                  • 6. Re: Sending emails for each record from tabular form
                    gjones77

                    Jeff,

                     

                    I really appreciate all the help, here's the query used to generate the the tabular form,

                     

                    select
                    "PKEY",
                    "DATE_WROTE",
                    "SALES",
                    "CLIENT",
                    "JOB",
                    "NOTES",
                    "COVERED",
                    "WHO",
                    "DATE_COVERED",
                    "PKEY" PKEY_DISPLAY,
                    "MIMETYPE",
                    "FILENAME",
                    "1OR2",
                    "HIT",
                    "RATE",
                    "STATE",
                    "FILENAME2",
                    "MIMETYPE2",
                    "CREATED",
                    "ACTIVE",
                    "CATEGORY"
                    from "REQS"
                    WHERE "ACTIVE" = 'Active'
                    AND "REQS"."SALES" = :P14_SALES
                    AND "COVERED" IS NULL
                    
                    

                     

                    The actual columns that are needed to be included in the email are

                    "SALES", (This is defined by a page item  P12_SALES, it's the name of the salesman)

                    "CLIENT", (Client name)

                    "JOB", (Position at client)

                    "WHO", (The name of the recruiter who covered the req)

                    "DATE_COVERED", (The date the req was covered)

                     

                    Right now if a req is covered the salesman or recruiter needs to go into the form from the reports page and manually check off "Covered" and then when they save the page it sends out an email.

                    The salesman wanted to be able to mark off multiple reqs at once instead of having to open each one one at a time, that the reason for the tabular form, but I still need the email to go out.

                     

                    Here's the current email process

                     

                    DECLARE
                    l_id number;
                    l_index number;
                    l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
                    lc_Message VARCHAR2(4000);
                    BEGIN
                    lc_Message :=               'Date Written   :' || :P2_DATE_WROTE || chr(10);
                    lc_Message := lc_Message || 'Sales          :' || :P2_SALES|| chr(10);
                    lc_Message := lc_Message || 'Client         :' || :P2_CLIENT|| chr(10);
                    lc_Message := lc_Message || 'Position       :' || :P2_JOB|| chr(10);
                    lc_Message := lc_Message || '#1 or #2       :' || :P2_1OR2 || chr(10);
                    lc_Message := lc_Message || 'Rate           :' || :P2_RATE || chr(10);
                    lc_Message := lc_Message || 'Notes      :' || :P2_NOTES || chr(10);
                    
                    l_id := APEX_MAIL.SEND(
                    p_to => 'reqs@someemail.com',
                    p_from => 'DO_NOT_REPLY@REQS',
                    p_subj => '' || :P2_WHO|| ' Has Covered ' || :P2_JOB|| ' at ' || :P2_CLIENT|| chr(10),
                    p_body => lc_Message);
                    
                    COMMIT;
                    APEX_MAIL.PUSH_QUEUE();
                    END;
                    
                    

                     

                    The new email has slightly different info since this one was created, but the idea is the same.

                     

                    My guess from reading your previous post is that I need to incorporate the LOOP process into the email process and change the references in the mail message from P_PAGE_ITEM to APEX_APPLICATION.g_f0X to reference each column, I've already looked at the page source to get the arrays, but if it's easy to just define them in a process I'm all for that.

                     

                    Thanks again for all your help.


                    Greg

                    • 7. Re: Sending emails for each record from tabular form
                      jwellsnh

                      Hi Greg,

                       

                      Salesman; rough crowd, this will not be easy!  Where the email address for the recipients come from?  Can I assume that the sender address will be the one above?

                       

                      Jeff

                      • 8. Re: Sending emails for each record from tabular form
                        gjones77

                        Jeff

                         

                        Yes, in this case it's a generic address that sends a message to the all the recruiting staff letting them know the req is covered.

                         

                        So in that sense it's a bit easier since we're not defining the address.

                         

                        And you have no idea how rough these guys are.

                         

                        Thanks again!

                         

                        Greg

                        • 9. Re: Sending emails for each record from tabular form
                          jwellsnh

                          Greg,

                           

                          What is the table that contains the recruiting staff email addresses?

                           

                          Jeff

                          • 10. Re: Sending emails for each record from tabular form
                            gjones77

                            Jeff,

                             

                            There isn't a table with that since the current email process just uses a single address that I've always manually added to the process.

                             

                            for now, so that account doesn't get spammed, just use reqs@email.com

                             

                            Greg

                            • 11. Re: Sending emails for each record from tabular form
                              jwellsnh

                              Greg,

                               

                              I took a different approach from what I originally suggested.  Since the tabular form is displaying only reqs that eligible to be covered, I chose to construct a process that would read the database after the reqs table was updated.  The code should find recent reqs covered by the salesman and then send out an email for each covered req.

                               

                              Since I cannot see the data structure of your reqs table, I guessed the data type and size for the local variables in the DECLARE section, you many need to adjust these.

                               

                              Give this code a shot and let's see where we get.  By the way, the naming conventions of your database are in need of naming standards.

                               

                              The process needs to occur After Submit and after the Automatic Row Processing (DML) process that is updating the reqs table.  Make sure that the process sequence number is greater than the Automatic Row Processing (DML) sequence number.

                               

                              DECLARE
                                 l_id           NUMBER;
                                 l_index        NUMBER;
                                 l_vc_arr2      apex_application_global.vc_arr2;
                                 lc_message     VARCHAR2 (4000);
                                 l_pkey         NUMBER;
                                 l_date_wrote   DATE;
                                 l_sales        VARCHAR2 (100);
                                 l_client       VARCHAR2 (100);
                                 l_job          VARCHAR2 (100);
                                 l_1or2         VARCHAR2 (100);
                                 l_rate         NUMBER;
                                 l_notes        VARCHAR2 (4000);
                              BEGIN
                                 FOR c1
                                    -- Retrieve reqs primary key that have been covered
                                    -- in the last 2 seconds by the salesman
                                 IN (SELECT pkey
                                       INTO l_pkey
                                       FROM reqs
                                      WHERE     SYSDATE < (date_wrote + 1 / 46200)
                                            AND active = 'Active'
                                            AND reqs.sales = :p12_sales
                                            AND covered IS NOT NULL)
                                 -- Send an email for each req that has been covered
                                 LOOP
                                    SELECT c1.date_wrote,
                                           c1.sales,
                                           c1.client,
                                           c1.job,
                                           c1.notes,
                                           c1.who,
                                           c1.1or2,
                                           c1.rate
                                      INTO l_date_wrote,
                                           l_sales,
                                           l_client,
                                           l_job,
                                           l_notes,
                                           l_who,
                                           l_1or2,
                                           l_rate
                                      FROM reqs
                                     WHERE pkey = l_pkey;
                              
                                    lc_message := 'Date Written   :' || l_date_wrote || CHR (10);
                                    lc_message := lc_message || 'Sales          :' || l_sales || CHR (10);
                                    lc_message := lc_message || 'Client         :' || l_client || CHR (10);
                                    lc_message := lc_message || 'Position       :' || l_job || CHR (10);
                                    lc_message := lc_message || '#1 or #2       :' || l_1or2 || CHR (10);
                                    lc_message := lc_message || 'Rate           :' || l_rate || CHR (10);
                                    lc_message := lc_message || 'Notes      :' || l_notes || CHR (10);
                                    l_id :=
                                       apex_mail.send (
                                          p_to     => 'reqs@someemail.com',
                                          p_from   => 'DO_NOT_REPLY@REQS',
                                          p_subj   =>    ''
                                                      || l_who
                                                      || ' Has Covered '
                                                      || l_job
                                                      || ' at '
                                                      || l_client
                                                      || CHR (10),
                                          p_body   => lc_message);
                                    COMMIT;
                                    apex_mail.push_queue ();
                                 END LOOP;
                              END;
                              

                               

                              Jeff

                              • 12. Re: Sending emails for each record from tabular form
                                gjones77

                                Jeff,

                                 

                                I changed the code a bit to call the fields that my manager requested, but it keep throwing this error:

                                 

                                ORA-06550: line 36, column 4: PLS-00302: component 'WHO' must be declared ORA-06550: line 35, column 21: PL/SQL: ORA-00904: "C1"."WHO": invalid identifier ORA-06550: line 30, column 1: PL/SQL: SQL Statement ignored

                                 

                                Here's the code I'm using

                                 

                                DECLARE  
                                l_id           NUMBER;
                                l_index        NUMBER;
                                l_vc_arr2      apex_application_global.vc_arr2;
                                lc_message     VARCHAR2 (4000);
                                l_pkey         NUMBER;
                                l_date_wrote   DATE;
                                l_sales        VARCHAR2 (4000);
                                l_client       VARCHAR2 (4000);
                                l_job          VARCHAR2 (4000);
                                l_who          VARCHAR2 (4000);
                                l_covered      VARCHAR2 (4000);
                                l_date_covered DATE;
                                
                                BEGIN  
                                FOR c1  
                                -- Retrieve reqs primary key that have been covered  
                                -- in the last 2 seconds by the salesman  
                                IN (SELECT pkey  
                                INTO l_pkey  
                                FROM reqs  
                                WHERE     SYSDATE < (date_wrote + 1 / 46200)  
                                AND active = 'Active'  
                                AND reqs.sales = :p12_sales  
                                AND covered IS NOT NULL)  
                                -- Send an email for each req that has been covered
                                  
                                LOOP  
                                SELECT 
                                c1.date_wrote,  
                                c1.sales,  
                                c1.client,  
                                c1.job,  
                                c1.date_covered,  
                                c1.who
                                INTO 
                                l_date_wrote,  
                                l_sales,  
                                l_client,  
                                l_job,  
                                l_date_covered,  
                                l_who
                                FROM reqs  
                                WHERE pkey = l_pkey;  
                                lc_message := 'Date Written   :' || l_date_wrote || CHR (10);  
                                lc_message := lc_message || 'Sales          :' || l_sales || CHR (10);  
                                lc_message := lc_message || 'Client         :' || l_client || CHR (10);  
                                lc_message := lc_message || 'Position       :' || l_job || CHR (10);  
                                lc_message := lc_message || 'Who Covered       :' || l_who || CHR (10);  
                                lc_message := lc_message || 'Date Covered           :' || l_date_covered || CHR (10);   
                                l_id :=  
                                apex_mail.send (  
                                p_to     => 'reqs@someemail.com',  
                                p_from   => 'DO_NOT_REPLY@REQS',  
                                p_subj   =>    ''  
                                || l_who  
                                || ' Has Covered '  
                                || l_job  
                                || ' at '  
                                || l_client  
                                || CHR (10),  
                                p_body   => lc_message);  
                                COMMIT;  
                                apex_mail.push_queue ();  
                                END LOOP;  
                                END;  
                                

                                 

                                 

                                Where am I screwing this up?

                                 

                                Greg

                                • 13. Re: Sending emails for each record from tabular form
                                  jwellsnh

                                  Greg, it is not you; it's my code.  Try this:

                                   

                                  DECLARE
                                     l_id           NUMBER;
                                     l_index        NUMBER;
                                     l_vc_arr2      apex_application_global.vc_arr2;
                                     lc_message     VARCHAR2 (4000);
                                     l_date_wrote   DATE;
                                     l_sales        VARCHAR2 (100);
                                     l_client       VARCHAR2 (100);
                                     l_job          VARCHAR2 (100);
                                     l_1or2         VARCHAR2 (100);
                                     l_rate         NUMBER;
                                     l_notes        VARCHAR2 (4000);
                                  BEGIN
                                     FOR c1
                                        -- Retrieve reqs primary key that have been covered
                                        -- in the last 2 seconds by the salesman
                                     IN (SELECT pkey
                                           FROM reqs
                                          WHERE     SYSDATE < (date_wrote + 1 / 46200)
                                                AND active = 'Active'
                                                AND reqs.sales = :p12_sales
                                                AND covered IS NOT NULL)
                                     -- Send an email for each req that has been covered
                                     LOOP
                                        SELECT date_wrote,
                                               sales,
                                               client,
                                               job,
                                               notes,
                                               who,
                                               1or2,
                                               rate
                                          INTO l_date_wrote,
                                               l_sales,
                                               l_client,
                                               l_job,
                                               l_notes,
                                               l_who,
                                               l_1or2,
                                               l_rate
                                          FROM reqs
                                         WHERE pkey = c1.pkey;
                                  
                                        lc_message := 'Date Written   :' || l_date_wrote || CHR (10);
                                        lc_message := lc_message || 'Sales          :' || l_sales || CHR (10);
                                        lc_message := lc_message || 'Client         :' || l_client || CHR (10);
                                        lc_message := lc_message || 'Position       :' || l_job || CHR (10);
                                        lc_message := lc_message || '#1 or #2       :' || l_1or2 || CHR (10);
                                        lc_message := lc_message || 'Rate           :' || l_rate || CHR (10);
                                        lc_message := lc_message || 'Notes      :' || l_notes || CHR (10);
                                        l_id :=
                                           apex_mail.send (
                                              p_to     => 'reqs@someemail.com',
                                              p_from   => 'DO_NOT_REPLY@REQS',
                                              p_subj   =>    ''
                                                          || l_who
                                                          || ' Has Covered '
                                                          || l_job
                                                          || ' at '
                                                          || l_client
                                                          || CHR (10),
                                              p_body   => lc_message);
                                        COMMIT;
                                        apex_mail.push_queue ();
                                     END LOOP;
                                  END;
                                  
                                  • 14. Re: Sending emails for each record from tabular form
                                    gjones77

                                    Jeff,

                                     

                                    I was able to create the process with no errors, but it's not sending out any email at all.

                                     

                                    I have it set as a page process that fires off when the submit button is pressed after computations and validations, but it's not sending anything out.

                                     

                                    Doesn't make sense though...

                                     

                                    Greg

                                    1 2 Previous Next