APEX: Adding Select List to Tabular Form and then processing based on values

Question

I'm fairly new to APEX, but I'm getting to a point where it is getting complicated for me, so I need a bit help. I have a dialog form with a Tabular Form with a composite primary key (S_ID and DEPARTMENT_ID). I'm adding a Select List via APEX_ITEM.SELECT_LIST method as the last column with two values (Approve:0;Reject:1) and a Submit button. If the user "flags" at least one record as Reject, once the page is submitted I want to update two hidden fields in the selected record from the Tabular Form. If the user selects all records as Approve then I need to update a status and a date in another table. The default values of the select list are Approve for all records.

I'm pretty good with SQL and PL/SQL, but once I added the APEX_ITEM.SELECT_LIST, I realized that JavaScript might be needed and this is where I got stuck. I can probably figure it out just with PL/SQL if I add a field to the table and then tie the SELECT List to it, but then I have unnecessary table field which I'm trying to avoid. I wanted to resolve it with a "virtual" column.

Any ideas?

Thank you in advance.

Answer 1

When you are using the apex_item.select_list function (or any another function of apex_item package) APEX allows you to use apex_application.g_fXX collections, where XX - number, which you pass as a first parameter. These collections contain data from the tabular form and could be accessed in PL/SQL after the page submit.
Let's say we have a table:

create table tab_form (
s_id number,
department_id number,
field_to_update varchar2(100));

insert into tab_form (s_id, department_id, field_to_update) values (1, 1, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (1, 2, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (1, 3, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (2, 1, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (2, 2, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (2, 3, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (3, 1, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (3, 2, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (4, 3, 'field is not updated');

You need to do following. Create a report with a query like this:

select APEX_ITEM.TEXT(1, s_id) S_ID, 
       APEX_ITEM.TEXT(2, department_id) DEPARTMENT_ID,
       FIELD_TO_UPDATE,
       APEX_ITEM.SELECT_LIST(3, null, 'Approve;0,Reject;1', null, 'YES', null, '%') ar
  from TAB_FORM

Using apex_item package initiates using of collections g_f01, g_f02 and g_f03. Next, create a button to submit and a process, which will be executed, when the Submit button is pressed. The process could contain a code like this:

begin
  forall i in 1 .. apex_application.g_f03.count 
    update tab_form
       set field_to_update = case when apex_application.g_f03(i) = '0' then 'approved by the user' 
                                  when apex_application.g_f03(i) = '1' then 'rejected by the user' 
                                  else 'the user hasn''t decided yet' end
     where s_id = apex_application.g_f01(i) 
       and department_id = apex_application.g_f02(i);
end;

In this code, you can implement any logic you need to process user's input.

You can see this example on the page here: https://apex.oracle.com/pls/apex/f?p=34599:8
The Submit button executes the code above, the Reset button changes values to default.