Apex Collections, an example (part 2)

Here is the second part about collections, my problem could be described as:
we would like to let users add order lines to an order, update and delete them without saving anything in our database until the user confirms that the order is correct and wants to create it.

1.4 Add Member to Collection
We can now create a process that will add members to our collection and link this process to our ‘Add Row’ button.

1.5 Update Collection
Now that we can add rows to our collection, it’s time to update it. With ‘wwv_flow_collection.update_member_attribute’ we can update our collection. We must specify the collection name, seq_id, the column and its value. Create the process without any condition.

1.6 Delete Collection
Finally we can make sure that users can delete members from the collection. We will use the checkbox for this. Create a process that will be executed when the delete row button is pressed. Use ‘wwv_flow_collection.delete_members’ to delete a member from your collection. We must specify our collection name, the column where we are looking for and then the value for that column.
We will use our key value in p_c001 to delete members from our collection.

1.7 Save Results
Last but not least, we can store the members from our collection to the right table. I use a database procedure for this:
INSERT INTO order_lines (order_line_id,
order_id,
description,
amount,
line_nr)
SELECT c001,
p_invoice_id,
c002,
c003,
seq_id
FROM wwv_flow_collections
WHERE collection_name = ‘ORDERS’;

1.8 Summarize
Page 1:

(note: processes are conditional with a request so the user can browse back to page 1 without deleting his collection)

Page 2:

Apex Collections, an example (part 1)

Some weeks ago I needed to store records temporarily and let a user decide later on if he/she would save the data permanently. This was a perfect time for me to start using ApEx Collections. Collections let you temporarily store data as records so you can manipulate them and save them later on. I found a whole bunch of information on the oracle forum but I couldn’t find the right information I needed, so therefore this post

My specific problem can be described as: we would like to let users add order lines to an order, update and delete them without saving anything in our database until the user confirms that the order is correct and wants to create it.

1.1 Delete Collection

Our first step when working with collections is to make sure that no collection with the same name exists. So create a process ‘on Load – Before Header’ that deletes a collection with the collection name you are using, in this case that would be ‘ORDERS’. We can use ‘wwv_flow_collection.delete_collection’ to delete a collection.

1.2 Create Collection

Now we are ready to create our collection, use ‘wwv_flow_collection.create_collection’ to create your collection. You might notice that there is already another thing I do, I already put one record in my collection. You are not obliged to do so but I do it here because now the user can already start filling the collection without pressing on a add row button.

I use ‘wwv_flow_collection.add_member’ to put a record in my collection. You must specify your collection name and then you must use p_c001 – p_c050 to specify your columns. In my first column I put a unique key that I will use when deleting a member from my collection. The third column specifies the amount and will be default 1.

1.3 Display Collection

Now we are ready to display our collection and update/add/delete members from it. Create a new page with a report where you will display the collection. Make sure you can navigate to it from the page where you create the collection (you can do it on the same page if needed). Add 2 buttons to your report, one to add a member and one to delete a member of your collection.

Some things explained:

• Our first column is a checkbox that will allow users to delete selected lines from the collection. The function ‘pck$genlib.f_get_next_row_id’ will return your row_id (0001,0002, …) so we can refer to the specific row in a correct way.

• The second column is the seq_id, automatically assigned to your collection members, we need this value in order to update the right member from the collection.

• The other columns must be displayed as text fields so we can put data into them.

Part 2 will include adding, deleting and updating members from your collections and will be posted very soon.