Problem
I want to be able to default the values based on what I have in the database, and after the user has finished making changes I want to save the values to the database.
The shuttle object allows us to select multiple values at a time.
In this example I have a list of subsystems for a request. I want to be able to default the values based on what I have in the database, and after the user has finished making changes I want to save the values to the database.
Solution
Creating the shuttle
In my example, I created a pop-up page (in line modal), but it doesn't matter whether the shuttle is on your main page are in a separate pop-up.In the designer, right-click on the region where you want to create the shuttle, "create page item".
Type: shuttle.
Name: P6_SUBSYSTEMS_CHANGED **Change this to whatever you want, but I refer to this in my code**
List of Values Type: SQL Query
SQL Query:
select subsytem_description, subsystem_id
from admin.xxx_ccs_subsystems;
Obviously you supply your own query, just note the order of the description and ID column. This query represent all the values that the user can select if they want to.Leave the rest at default.
Defaulting the values from the database
I want to default the values based on what I already have in the database.In the designer, right-click on the shuttle that you created, and "Create Computation".
Type: PL/SQL Function Body
SQL Query:
DECLARE
SS_CHANGED_LIST
apex_application_global.vc_arr2;
i
number := 1;
begin
for r in (select subsystem_id from
admin.xxx_ccs_request_subsystems where request_id = :P_REQUEST_ID) loop
SS_CHANGED_LIST(i) := r.subsystem_id;
i := i + 1;
end loop;
return
apex_util.table_to_string(SS_CHANGED_LIST,':');
end;
What does this code do? A table variable is created. We loop through our database rows and we add the ID column to the table variable. Finally we use an inbuilt function to convert the table to a colon-delimited string which we return to the shuttle object.
Saving the values from the shuttle into the database
I've seen lots of ways to do this, but the bit that I struggled with is where to put the code. I tried a few different ways, and it always failed to find any values in the shuttle object. This is what worked for me.
In the designer, right-click on the same region where you have created the shuttle, "create button".
Call it whatever you like, I used "Apply".
Where do you put the code? On your page, go to processing
Processes, right click > Create Process
Type: PL/SQL Code
PL/SQL Code:
declare
l_selected apex_application_global.vc_arr2;
begin
l_selected := apex_util.string_to_table(:P6_SUBSYSTEMS_CHANGED);
delete from admin.xxx_ccs_request_subsystems
where request_id = :P_REQUEST_ID
and changed_or_unchanged = 'C';
for i in 1..l_selected.count loop
insert into admin.xxx_ccs_request_subsystems
(request_id,
subsystem_id,
changed_or_unchanged)
values
(:P_REQUEST_ID,
l_selected(i),
'C');
end loop;
end;
What does this code do? A table variable is created. We use an inbuilt function to convert the string stored in the shuttle to a table. We delete the data that we had in the database, then we loop through a re-insert what the user has selected.
PL/SQL Code:
declare
l_selected apex_application_global.vc_arr2;
begin
l_selected := apex_util.string_to_table(:P6_SUBSYSTEMS_CHANGED);
delete from admin.xxx_ccs_request_subsystems
where request_id = :P_REQUEST_ID
and changed_or_unchanged = 'C';
for i in 1..l_selected.count loop
insert into admin.xxx_ccs_request_subsystems
(request_id,
subsystem_id,
changed_or_unchanged)
values
(:P_REQUEST_ID,
l_selected(i),
'C');
end loop;
end;
What does this code do? A table variable is created. We use an inbuilt function to convert the string stored in the shuttle to a table. We delete the data that we had in the database, then we loop through a re-insert what the user has selected.
Comments
Post a Comment