oracle apex shuttle saving deselections

Question

I'm using Apex 4.2.4 and Oracle 11g. I have a mailing list application where I'm maintaining multiple mailing lists. A given recipient may belong to one or more lists. I'm using an Apex Shuttle to maintain the lists. The source of all mailing recipients is in the table: mail_recipient. There are four important fields in mail_recipient:

• prim_key
• first_name
• last_name
• email_address

There is another table that stores the selected recipients for a given mailing list: mail_recipient_category: The important fields in mail_recipient_category are:

• prim_key
• recipient_fkey (this stores the prim_key from the mail_recipient table)
• category
• merge_check

There are two displayed items on the page. The category drop down list (P31_email_list) and the shuttle (P31_email_list_assignments) The LOV for P31_email_list_assignments is:

Select last_name || ', ' || first_name || ' -- ' || email_address, prim_key from mail_recipient
order by 1;

The PL/SQL function body for the shuttle source is:

declare
  emp_list  apex_application_global.vc_arr2;
  i                   number := 1;
begin
  for r in (Select mr.last_name || ', ' || mr.first_name || ' -- ' || mr.email_address, mr.prim_key
            From mail_recipient mr left outer join 
              mail_recipient_category mrc
            On mr.prim_key = mrc.recipient_fkey
            Where  mrc.category = :P31_EMAIL_LIST)
  loop
    emp_list(i) := r.prim_key;
    i := i + 1;
  end loop;
  return APEX_UTIL.TABLE_TO_STRING(emp_list, ':');
end;

There is also a single page process to update the database table mail_recipient_category. The process is executed on submit after computations and validations. Begin

MERGE INTO MAIL_RECIPIENT_CATEGORY ss
USING (
    Select
        shuttle.column_value shuttle_st
      , db.recipient_fkey db_st
    from
        table(string_to_coll(:P31_email_list_assignments)) shuttle
          left outer join mail_recipient_category db
            on shuttle.column_value = db.recipient_fkey
           and db.category = :P31_email_list) t
on  (ss.recipient_fkey = t.db_st
and ss.category = :P31_email_list    
    )
when matched
then
    update
    set
        ss.merge_check = ss.merge_check
    delete
    where
        t.shuttle_st is null and ss.category = :P31_email_list
--        t.shuttle_st is null

  when not matched 
  then
    insert
      (recipient_fkey, category)
    values
      (t.shuttle_st, :P31_email_list);
end;

The shuttle works fine to load from left to right and save items. The problem I'm having is deselecting items from the right side to the left side of the shuttle. After moving a a mail recipient from the right to the left side, when I press the submit button, the items don't leave the right side of the shuttle and the process doesn't delete the row from the mail_recipient_category table.
Thanks for your help with this.

Answer 1

After working with a very experienced colleague, we determined that the page process was at fault. Specifically, the Delete clause of the Merge statement doesn't seem to work for this shuttle. We ended up adding a separate Delete statement after the the Merge statement in the page process.
I also dropped the mail_recipient_category table and am now using the more meaningful name mail_recipient_list. The shuttle field name is (p31_email_list_assignments).

The working shuttle LOV Definition code is:

Select last_name || ', ' || first_name || ' -- ' || email_address, prim_key 
From mail_recipient
order by 1;

The working shuttle Source code is:

declare
  mail_list  apex_application_global.vc_arr2;
  i                   number := 1;
begin
  for r in (Select mr.last_name || ', ' || mr.first_name || ' -- ' || mr.email_address, mr.prim_key
            From mail_recipient mr left join 
              mail_recipient_list mrl
            On mr.prim_key = mrl.recipient_fkey
            Where  mrl.list = :P31_EMAIL_LIST)
  loop
    mail_list(i) := r.prim_key;
    i := i + 1;
  end loop;
  return APEX_UTIL.TABLE_TO_STRING(mail_list, ':');
end;

The working After Submit page process code is:

Begin
Merge Into MAIL_RECIPIENT_LIST mrl
Using (
    Select
        shuttle.column_value shuttle_cv
      , db.recipient_fkey db_rfk
    From
        table(string_to_coll(:P31_email_list_assignments)) shuttle
          left outer join mail_recipient_list db
            on shuttle.column_value = db.recipient_fkey
           and db.list = :P31_email_list) t
On  (mrl.recipient_fkey = t.db_rfk
And  mrl.list = :p31_email_list    
    )
When Matched
Then
    Update
    Set
        mrl.merge_check = mrl.merge_check
--    Delete
--      Where
--      t.shuttle_cv is null

When Not Matched 
  Then
    Insert
      (recipient_fkey, list)
    Values
      (t.shuttle_cv, :P31_email_list);

/* The commented-out delete clause of the Merge statement never worked with this shuttle.
   The following delete statement will every time this page process is called
*/
Delete from MAIL_RECIPIENT_LIST 
Where instr(':' || :P31_email_list_assignments || ':',':' || recipient_fkey || ':') = 0  
And list = :P31_email_list;
end;

I hope someone finds this useful.