Multiple emails being sent out by Oracle Apex process in Tabular Form

Question

Thanks to Tom I was able to get my initial issue solved, I'm now able to have a tabular form send automatic emails out for checked rows, the only issue I have now is that it sends out a copy of the email how ever many time a check box is checked.

If you check off one row, it sends out one email, but if you check off 2 (or more) rows it sends out each email twice, three times for 3 rows.

I'm using the standard ApplyMRU process to update the rows and a separate process to send out the emails.

The process is below.

I know I'm missing something here, any ideas?

DECLARE  
l_checked_row  NUMBER;
l_id           NUMBER;
lc_message     VARCHAR2 (4000);
l_pkey         NUMBER;
l_r_reqs       reqs%ROWTYPE; 
BEGIN  
FOR i IN 1..apex_application.g_f01.count 
LOOP  
l_checked_row := apex_application.g_f01(i);
-- assuming that array F02 maps to column PKEY from table REQS
l_pkey        := apex_application.g_f02(l_checked_row);

-- get details required for creating the mail body
-- It's generally easier to just fetch the row instead of having to 
-- define variables to cover every field you need.
SELECT *  
  INTO l_r_reqs
  FROM reqs  
 WHERE pkey = l_pkey;  
-- Dont forget that select into may generate no_data_found or too_many_rows !

lc_message :=               'Date Written   :'         || l_r_reqs.date_wrote   || CHR (10);  
lc_message := lc_message || 'Sales          :'         || l_r_reqs.sales        || CHR (10);  
lc_message := lc_message || 'Client         :'         || l_r_reqs.client       || CHR (10);  
lc_message := lc_message || 'Position       :'         || l_r_reqs.job          || CHR (10);  
lc_message := lc_message || 'Who Covered       :'      || l_r_reqs.who          || CHR (10);  
lc_message := lc_message || 'Date Covered           :' || l_r_reqs.date_covered || CHR (10);  
l_id := APEX_MAIL.SEND(  
      p_to     => '[e-mail]',  
      p_from   => 'DO_NOT_REPLY@REQS',  
      p_subj   =>    ''  
                  || l_r_reqs.who  
                  || ' Has Covered '  
                  || l_r_reqs.job  
                  || ' at '  
                  || l_r_reqs.client  
                  || CHR (10),  
      p_body   => lc_message);        
-- avoid commits unless ab-so-lu-te-ly necessary. Apex implicit commits can make the flow hard enough to
-- understand as it is.
END LOOP;

apex_mail.push_queue ();
END;

Here's what I get from the APEX debug.

Session State: Save "P14_SALES" - saving same value: "Ian Kimmett" Processes - point: ON_SUBMIT_BEFORE_COMPUTATION Branch point: Before Computation Process point: AFTER_SUBMIT Tabs: Perform Branching for Tab Requests Branch point: Before Validation Validations: Perform basic and predefined validations: Perform custom validations: Branch point: Before Processing Processes - point: AFTER_SUBMIT ...Process "ApplyMRU" - Type: MULTI_ROW_UPDATE ...Process "ApplyMRD" - Type: MULTI_ROW_DELETE ......Skip because condition or authorization evaluates to FALSE ...Process "SEND_MAIL" - Type: PLSQL ......Process row 1 ...Execute Statement: begin DECLARE l_checked_row NUMBER; l_id NUMBER; lc_message VARCHAR2 (4000); l_pkey NUMBER; l_r_reqs reqs%ROWTYPE; BEGIN FOR i IN 1..apex_application.g_f01.count LOOP l_checked_row := apex_application.g_f01(i); l_pkey := apex_application.g_f02(l_checked_row); SELECT * INTO l_r_reqs FROM reqs WHERE pkey = l_pkey; lc_message := 'Date Written :' || l_r_reqs.date_wrote || CHR (10); lc_message := lc_message || 'Sales :' || l_r_reqs.sales || CHR (10); lc_message := lc_message || 'Client :' || l_r_reqs.client || CHR (10); lc_message := lc_message || 'Position :' || l_r_reqs.job || CHR (10); lc_message := lc_message || 'Who Covered :' || l_r_reqs.who || CHR (10); lc_message := lc_message || 'Date Covered :' || l_r_reqs.date_covered || CHR~ ......Process row 2 ...Execute Statement: begin DECLARE l_checked_row NUMBER; l_id NUMBER; lc_message VARCHAR2 (4000); l_pkey NUMBER; l_r_reqs reqs%ROWTYPE; BEGIN FOR i IN 1..apex_application.g_f01.count LOOP l_checked_row := apex_application.g_f01(i); l_pkey := apex_application.g_f02(l_checked_row); SELECT * INTO l_r_reqs FROM reqs WHERE pkey = l_pkey; lc_message := 'Date Written :' || l_r_reqs.date_wrote || CHR (10); lc_message := lc_message || 'Sales :' || l_r_reqs.sales || CHR (10); lc_message := lc_message || 'Client :' || l_r_reqs.client || CHR (10); lc_message := lc_message || 'Position :' || l_r_reqs.job || CHR (10); lc_message := lc_message || 'Who Covered :' || l_r_reqs.who || CHR (10); lc_message := lc_message || 'Date Covered :' || l_r_reqs.date_covered || CHR~ Branch point: After Processing ...Evaluating Branch: "AFTER_PROCESSING" Type: REDIRECT_URL Button: (No Button Pressed) Condition: (Unconditional) Redirecting to f?p=950:14:0::::: Stop APEX Engine detected Stop APEX Engine detected Final commit

Answer 1

As Greg pointed out and as can be seen from the debug, the process you are using has a scope set for a tabular form. I hadn't thought of that in your previous question, since I'm mostly stuck creating manual tabular forms with the apex_item API instead of wizard generated tabular forms.
The wizard generated tabular forms have some good support options like the ability to validate per row and fire a process per row, maintaining the bind variable syntax instead of having to use the global arrays.
It's a good find and I really forgot about it. You can however only and ever set this execution scope during the creation of a process.
Eg:

Selecting a tabular form will reduce the options available:

Once a process has been created you can verify the process point. A tabular form associated process will have the "Tabular Form" property set to the tabular form region:

A regular process however does NOT have this property at all:

So with the code above in a tabular form associated process you will fire this for each checked row, and thus get the result you described.
Having said that however, you can actually opt to either adapt your code so it fires within the tabular form row context or create a new process without associating it with the tabular form (and keeping that code).

For completeness' sake, this is how you can set up your code to fire for each of your rows:

DECLARE  
  l_checked_row  VARCHAR2(1);
  l_id           NUMBER;
  lc_message     VARCHAR2 (4000);
  l_pkey         NUMBER;
  l_r_reqs       reqs%ROWTYPE; 
BEGIN 
  l_checked_row := :APEX$ROW_SELECTOR; -- X if checked
  l_pkey        := :REQ_ID; -- the column name holding the PK. Not the label you assigned it, but the actual column name or alias from the report!

  IF l_checked_row = 'X' 
  THEN
    -- get details required for creating the mail body
    -- It's generally easier to just fetch the row instead of having to 
    -- define variables to cover every field you need.
    SELECT *  
      INTO l_r_reqs
      FROM reqs  
     WHERE pkey = l_pkey;  
    -- Dont forget that select into may generate no_data_found or too_many_rows !

    lc_message :=               'Date Written   :'         || l_r_reqs.date_wrote   || CHR (10);  
    lc_message := lc_message || 'Sales          :'         || l_r_reqs.sales        || CHR (10);  
    lc_message := lc_message || 'Client         :'         || l_r_reqs.client       || CHR (10);  
    lc_message := lc_message || 'Position       :'         || l_r_reqs.job          || CHR (10);  
    lc_message := lc_message || 'Who Covered       :'      || l_r_reqs.who          || CHR (10);  
    lc_message := lc_message || 'Date Covered           :' || l_r_reqs.date_covered || CHR (10);  

    l_id := APEX_MAIL.SEND(  
          p_to     => '[e-mail]',  
          p_from   => 'DO_NOT_REPLY@REQS',  
          p_subj   =>    ''  
                      || l_r_reqs.who  
                      || ' Has Covered '  
                      || l_r_reqs.job  
                      || ' at '  
                      || l_r_reqs.client  
                      || CHR (10),  
          p_body   => lc_message);        

    -- although pushing the queue for each mail sent is not advised you can do it for immediate effect 
    apex_mail.push_queue ();
  END IF;
END;

It's probably easier to read and maintain it this way too! Though I have to say, knowing about the arrays will come in handy sooner or later :)

Answer 2

The easiest way is to create a process create a process and not to select 'Tabluar Form'. That is just create the process as normal page process.